Selecting All Items When Variable is Null

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

June 24th, 2004, 12:15 PM

thordiddy

Contributing User

Join Date: Jun 2004

Posts: 40

Time spent in forums: 8 h 17 m 12 sec
Reputation Power: 5

Selecting All Items When Variable is Null

I am writing a script which recieves inquiry values from a form and then executes based on the information. In one category, I am providing a query for state, where the user can select a specific state to search or can select "ALL STATES" where the query checks all accounts with any state. Being new to the whole Oracle thing, I was wondering how I can get this done. I have heard that using wildcards could help me out, but I am still lost.

Here is my inquiry code where all variables named search_+something are the user defined parameters.

OPEN output_cur FOR
SELECT
e_mail,
pat_firstname,
pat_lastname
FROM
ops_arw.member_pin M,
ops_arw.subscriber S,
ops_arw.address A
WHERE
A.CITY = search_CITY AND
A.ZIP_CODE = search_ZIP_CODE AND
A.STATE = search_STATE AND
M.EMAIL_PREFERENCE = search_EMAIL_PREF AND
M.SRV_EMAIL_PREFERENCE = search_SRV_PREF AND
S.EFF_DATE = search_EFF_DATE AND
S.CERT_NO = M.CERT_NO AND
S.CERT_NO = A.CERT_NO AND
trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) < MAX_AGE AND
trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) > MIN_AGE;

Thanks for the help.

June 24th, 2004, 01:25 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

It looks like you are using PL/SQL. Note that that comment block "/* .. */" can be in the middle of a SQL statement and not hurt anything.

try something like this:

Code:

IF search_state = 'ALL_STATES'
THEN 
        OPEN output_cur FOR
        SELECT
           e_mail,
           pat_firstname,
           pat_lastname
        FROM 
           ops_arw.member_pin M,
           ops_arw.subscriber S,
           ops_arw.address A
        WHERE
           A.CITY = search_CITY AND
           A.ZIP_CODE = search_ZIP_CODE AND
           /* I removed this --  A.STATE = search_STATE AND */
           M.EMAIL_PREFERENCE = search_EMAIL_PREF AND
           M.SRV_EMAIL_PREFERENCE = search_SRV_PREF AND
           S.EFF_DATE = search_EFF_DATE AND
           S.CERT_NO = M.CERT_NO AND
           S.CERT_NO = A.CERT_NO AND
           trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) < MAX_AGE AND
           trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) > MIN_AGE;
ELSE
        OPEN output_cur FOR
        SELECT
           e_mail,
           pat_firstname,
           pat_lastname
        FROM 
           ops_arw.member_pin M,
           ops_arw.subscriber S,
           ops_arw.address A
        WHERE
           A.CITY = search_CITY AND
           A.ZIP_CODE = search_ZIP_CODE AND
           A.STATE = search_STATE AND
           M.EMAIL_PREFERENCE = search_EMAIL_PREF AND
           M.SRV_EMAIL_PREFERENCE = search_SRV_PREF AND
           S.EFF_DATE = search_EFF_DATE AND
           S.CERT_NO = M.CERT_NO AND
           S.CERT_NO = A.CERT_NO AND
           trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) < MAX_AGE AND
           trunc(months_between(SYSDATE,M.MEM_DOB)/12,0) > MIN_AGE;

END IF;

June 24th, 2004, 01:31 PM

thordiddy

Contributing User

Join Date: Jun 2004

Posts: 40

Time spent in forums: 8 h 17 m 12 sec
Reputation Power: 5

I can't do that because I need to do the same thing with each criteria, not just STATE. It would make the code have to be incredibly long.

Thanks for the idea though

June 24th, 2004, 04:11 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

You didn't say that to start with. Most of the guys answering on the forums are professionals and can answer better with a whole picture.

Since I imagine there are still things you've left out I'll just go with this:

decode()

It's like an if statement in Oracle SQL.

June 25th, 2004, 10:40 AM

thordiddy

Contributing User

Join Date: Jun 2004

Posts: 40

Time spent in forums: 8 h 17 m 12 sec
Reputation Power: 5

Sorry about that. If I was to use decode(), how would I implement it into my cursor? For example, I want it to yield everything if the variable CITY = NULL, but I want it to only yield people living in a certain city when CITY does not equal NULL.

Thanks for the help.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: