How to return both null values and that of a parameter

[Former Member]

Good afternoon,

I've been trying to workout how I can return values that are stated as part of a parameter and the nulls. I have a report that I have used SQL to input a list of ID's from one table and then used UNION to join onto another table. Those in table A have a period field but those in table B don't.

Table A as stated has a period column which has values 1 to 12 in, I have a parameter so that a user can input one value and it will come back with that data, but what I need to do is bring back everything that has for example a value of "5" (from the parameter) and that does not exist in table A but does in table B.

Can someone please suggest a way to do this?

Thanks

Kris

[Johan_H]

Hi Kris,

EXCEPT may be what you are looking for. It works somewhat similarly to UNION, but it shows you the results from the top query that cannot be found in the bottom query.

Something like this:

SELECT ItemCode FROM RDR1

EXCEPT

SELECT ItemCode FROM DLN1

for example would give you all items that were ever ordered, but never delivered.

Regards,

Johan