Selection Criteria or a Parameter ?

[dhamspec]

Hi, I am using Crystal 9 linked to an Oracle Database. I have two fields set up like below. Vendor number is a unique identifier. I would like to direct my query not to select any vendor number if it appears anywhere in the transvendor field. With the example below, I would like my query to show all vendors except 8008 because that number occurs in the Transvendor field. I have tried various parameter variations, have pulled in a second copy of the table and tried to eliminate through linking but have not had any success yet, query always shows me all vendors even if they occur in the transvendor field.
Thanks in advance
Denise

Vendor# Transvendor#
8008 0
8009 0
8010 0
8011 0
8012 0
8013 0
8014 0
8015 8008
8016 8008

 

[Turkbear]

Hi,
The better way wil be to build a view in the database that handles that exclusion:

Create or Replace MyView as Select * from table_name where vendor not in ( select transvendor from table_name);

Otherwise you may have to modify the Sql the CR creates or use some form of supression..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dhamspec]

Thanks for the suggestions, I did not quite get the results I wanted though. I am starting with
8008 0
8009 0
8010 0
8011 0
8012 0
8013 0
8014 0
8015 8008
8016 8008

I got
8009 0
8010 0
8011 0
8012 0
8013 0
8014 0

I want
8009 0
8010 0
8011 0
8012 0
8013 0
8014 0
8015 8008
8016 8008

Your suggestion has eliminated those records with all with a transvendor number below is the actual code I used

Select * from VTD
WHERE VTD_VND_NBR not in (select VTD_TRANS_VND from VW_DCBVTD)

Thanks D

 

[dhamspec]

Ah fixed the issue, reversed the fields and have now
WHERE VTD_trans_vnd not in (select VTD_vnd_nbr from VW_DCBVTD). Gave me expected results. Thanks so much for your help this issue was really holding me up