RE: How to create such query ?

Something like this -

SELECT Case when file2.cdcode is null then 'code value in file3 to
identify unmatched' else file2.cdcode end,
File2.cdcode, file1.cdcode, file3.description FROM file1 left outer join

File2 on file1.CdCODE = file2.cdcode left outer join file3
on Case when file1.cdcode is null then 'code value in file3 to identify
unmatched ' else file2.cdcode end = file3.cdcode

the value in the quotes needs to be a code value that exists in file3
that would pull a description saying the code did not exist in file2


John Arnold
(301) 354-2939

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tomasz Skorza
Sent: Wednesday, September 24, 2008 10:03 AM
To: midrange-l@xxxxxxxxxxxx
Subject: How to create such query ?

Hi

I have three files:
File1 - with all user profiles (outfile from DSPUSRPRF)
File2 - with the same user profile (but not every profile from File1 has
record in File2), this
file2 has also additional field - "code of divison" i.e. AC
File3 - where code are explain i.e. AC - Accounting (all codes from
File2 has explanation in File3)

And now I would like to get report where I receive ALL profile from
File1 with name of Division (if
profile doesn't exist in File2 should have BLANK as division)
But I received only this which exist in File2 or nothing. How to connect
these three files? Which
subquery will be correct?