Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: Oracle
Anybody can help? I am joining four tables to get the required result. Referring to the first script below, I want to get the summary of Charges which is one of the fields of table1.SUM value should be grouped by month according to the adddate field from table4. How can i change of below script to get the expected result.
Hope anyone can help me. Smile | :)
SELECT * FROM Table1 WHERE Table1.evidencenumber IN 
(SELECT DISTINCT table2.evidencenumber FROM table3 rs, table2
WHERE table3.rep_id IN (SELECT DISTINCT rep_id FROM table4 WHERE adddate>=TO_DATE('010109','ddmmyy') 
AND adddate<=TO_DATE('311209','ddmmyy') AND dept='Engineering')
AND table3.evidencenumber=table2.evidencenumber
AND table2.pareantid IS NULL)
Posted 23-May-11 0:38am
Edited 23-May-11 0:51am
Keith Barrow152.4K
udayakumarcgvak at 12-Oct-11 3:54am
Helo Friend,
Your Sql Code is Not clear to me,So U give Table & Field details and Your Requirement.I think that is good for some one understand..

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

If I correctly understand the purpose you need only summary by the values of Table1, then you can filter distinct results by simply joining all the subqueries in outer "where" clause and adding the "rowid" marker of the row from corresponding table (this is possible in Oracle), and thus calculating the result in the outer query:
select T.adddate, sum(T.Charges) from
SELECT distinct Table1.rowid, Table1.Charges, table4.adddate FROM
 Table1.evidencenumber = table2.evidencenumber 
 AND table3.rep_id = table4.rep_id
 and table4.adddate>=TO_DATE('010109','ddmmyy') 
 AND table4.adddate<=TO_DATE('311209','ddmmyy')
 AND table4.dept='Engineering'
 AND table3.evidencenumber=table2.evidencenumber
 AND table2.pareantid IS NULL
) T 
group by T.adddate
Still if your SQL engine doesnt have the built-in row marker (like rowid in Oracle) you can add a unique field to the Table1 and use it as a rowid-marker.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 512
1 Maciej Los 445
2 Sergey Alexandrovich Kryukov 421
3 CPallini 200
4 _Asif_ 135
0 Sergey Alexandrovich Kryukov 6,813
1 OriginalGriff 5,342
2 Peter Leow 3,837
3 Mika Wendelius 2,850
4 CHill60 2,748

Advertise | Privacy | Mobile
Web03 | 2.8.150224.1 | Last Updated 12 Jul 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100