correlation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

correlation problem

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2008-04-18 : 05:42:32

hi,
I have 3 tables
A (accID integer, accName varchar2(10))
B (accID integer, status varchar2(2) dateChanged date)
C (accID integer, type varchar2(5), tran_sub_type varchar2(5), dateInserted date)

I need to "
select A.accID, A.accName, B.status B.dateChanged, (max(C.dateInserted) where A.accID = C.accID and type ='T' and sub_type = 'CI') MAX_DATE
from A, B, C
where A.accID = B.accID and A.accID = C.accID and B.status='D'
and MAX_DATE > B.dateChanged"

In plain english (tables are presented only with necessary columns):
A keeps (bank) accounts , B keeps the status of the account and the date that the status changed and C keeps all the transactions that are made for the account

want to get the accounts that are assigned a specific status ('D') on a certain date, that the max(date) of the transactions (of a specific type and sub_type, 'T' and 'CI' respectively) referring to that account is more recent than the date on which the account got the specific status

My problem is that MAX_DATE is not recognized in the where clause ....

(of course i am not sure that this is the right query ...)

Thx
theodore

ps: i kwow that this is an SQL forum and currently i am using Oracle 9i, but i have tried the forum for a few times when i was working SQL Server and i got answers all the time !!! (after all sql..is sql!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 06:00:44

Change like this and see if it gives you desired result (I've given query in SQL Server syntax so you might have to modify it to suit oracle)

SELECT a.accID, a.accName, b.status b.dateChanged,c.MaxDate
FROM A a
INNER JOIN B b
ON b.accID=a.accID
INNER JOIN (SELECT MAX(dateInserted) as MaxDate,accID 
           FROM C
           WHERE type='T' AND sub_type='CI'
           GROUP BY accID)c
ON c.accID=a.accID
WHERE b.Status='D'
AND c.MaxDate > b.dateChanged

EDIT:missed a braces

dev45
Yak Posting Veteran

54 Posts

Posted - 2008-04-18 : 06:32:41

Thx for the answer..
which also seems to be correct :)
(i am telling seems, cos the underlying data is huge and i have only checked a couple of accounts)

Thanks a lot again

ps: the sql is also accepted by oracle the way you provided it

dev45
Yak Posting Veteran

54 Posts

Posted - 2008-04-18 : 06:34:15

The only problem is that it is too slow.....
but we'll figure something out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources