SELECT WITH NULL VALUES

[cristi22]

Hello all -

Here is the data sample:

ssn: zip country_code
---------------------------------
123456789, " ", TU
999999999, 10003, USA
555555555, " ", "" should not be on the outfile
666666666,"07036", ""

I need to extract the data for the mailing purposes & select records with not null zip codes only for records with blank or USA country_codes.

If the country_code is USA or blank zip_code should not be null. How do I code it?

select member_key ,zip_code,country_code from member 
where  ? zip_code is not null ... in case when country code is USA or NULL

Please help!
cristi

 

[LKBrwnDBA]

Try this:

Select Member_Key, Zip_Code, Country_Code 
  From Member 
 Where (Country_Code = 'USA' 
    Or  Country_Code Is Null)
   And Zip_Code Is Not Null;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cristi22]

this won't work i need non-usa as well

 

[LKBrwnDBA]

this won't work i need non-usa as well

This is not the original requirement you stated:

...select records with not null zip codes only for records with blank or USA country_codes.

Besides, wouldn't: USA + Blank + Non-USA = ALL

To include "blank" country codes:

Select Member_Key, Zip_Code, Country_Code 
  From Member 
 Where (Country_Code In ('USA', '   '))
   And Zip_Code Is Not Null;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MarcLodge]

Hi Cristi,
We are having a problem here, as I suspect you are, with your actual requirements.

Please think about what you want, and write the requirements down on paper. This will lead you to either post here very clearly, and to have answered your question yourself.

So.............

Requirements?

Marc

 

[cristi22]

thanks all for your help. Sorry for not being clear -
here is the code (figured it out...)

 and (
	((case value(country_code,'') when '' then 'USA' else country_code end)='USA' and value(zip_code,'')<>'')
	or
	((case value(country_code,'') when '' then 'USA' else country_code end)<>'USA' and value(zip_code,'')='')
	)

Now, i am struggling with another one...
need to select all the funds & only specific locals for AW% FUND

WHERE .....
and FUND
in('LOCL8P','AW6H','819H','DOH','AW6P','LOCL8P','363EP')
and
if fund in ('AW6H' ,'AW6P') THen local in ('6', '6M', '43', '123', '31R')
else if fund <> AW.. THEN select all locals


thanks a lot
cristi

 

[MarcLodge]

Cristi,
Would the following do?

WHERE
   (    FUND IN (‘AW6H’, ‘AW6P’)
    AND LOCAL IN ('6', '6M', '43', '123', '31R'))
OR (FUND IN ('LOCL8P','819H','DOH','LOCL8P','363EP'))

Marc