Basic query question 1

[eyetry]

As I've said before, I'm not really an oracle developer so bear with this simple question. At least it seems like it should be easy. Apparently I'm having brain freeze.

I get 2 files one external the other internal. Both get loaded to a table with. I need to identify those people that appear on file A that don't appear on file B. This doesn't work.

select count(a.PARENT_ID)
from EDI_AUDIT_DEP_IN_TMP a, EDI_AUDIT_DEP_IN_TMP b
where a.PARENT_ID || substr(a.FNAME, 1,3) || a.ACCOUNT_NO || a.RELATIONSHIP || a.GENDER || a.DOB <> b.PARENT_ID || substr(b.FNAME, 1,3) || b.ACCOUNT_ID || b.RELATIONSHIP || b.GENDER || b.DOB)
and a.file_id = 'A'
and b.file_id = 'B'

Any suggestions?

 

[HarleyQuinn]

How about something like (untested):

select count(a.PARENT_ID)
from EDI_AUDIT_DEP_IN_TMP a, EDI_AUDIT_DEP_IN_TMP b
where a.PARENT_ID || substr(a.FNAME, 1,3) || a.ACCOUNT_NO || a.RELATIONSHIP || a.GENDER || a.DOB = b.PARENT_ID || substr(b.FNAME, 1,3) || b.ACCOUNT_ID || b.RELATIONSHIP || b.GENDER || b.DOB(+)
and b.PARENT_ID || substr(b.FNAME, 1,3) || b.ACCOUNT_ID || b.RELATIONSHIP || b.GENDER || b.DOB IS NULL
and a.file_id = 'A'
and b.file_id = 'B'

There was also an extra bracket in there after b.DOB in the join.

It's been a while since I've done Oracle so fingers crossed eh?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Welshbird]

I would look at using something like this. The minus function is one of the most useful things I have learnt from this forum!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[HarleyQuinn]

Very nice Fee, I didn't know that was around

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.