Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT WITH NULL VALUES

Status
Not open for further replies.

cristi22

MIS
Aug 17, 2005
155
US
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?

Code:
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



 
Try this:

Code:
Select Member_Key, Zip_Code, Country_Code 
  From Member 
 Where (Country_Code = 'USA' 
    Or  Country_Code Is Null)
   And Zip_Code Is Not Null;
[3eyes]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
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 [ponder]

To include "blank" country codes:
Code:
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
 
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
 
thanks all for your help. Sorry for not being clear -
here is the code (figured it out...)
Code:
 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
 
Cristi,
Would the following do?
Code:
WHERE
   (    FUND IN (‘AW6H’, ‘AW6P’)
    AND LOCAL IN ('6', '6M', '43', '123', '31R'))
OR (FUND IN ('LOCL8P','819H','DOH','LOCL8P','363EP'))

Marc
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top