Using OR to show all records if NULL criteria selected
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Using OR to show all records if NULL criteria selected

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Angry Using OR to show all records if NULL criteria selected

    Hi I am really stuck on this one:

    This piece of SQL is used to return project data according to a phase.

    Ecah phase is displayed as an individual check box in an Oracle Based application. The user then has the opportunity to select the check boxes according to the Phases he wants displayed. However if the user leaves ALL Check boxes blank then it is assumed that all phases are required to be displayed. The SQL Generated is shown below.

    SELECT CELLS.Zone
    , CELLS.SiteCode
    , CELLS.SARFVersion
    , CELLS.Phase
    FROM Cells CELLS
    WHERE
    (1=1
    AND CELLS.Zone = 'North East'
    AND ( CELLS.Phase = DECODE('', '1', '1','')
    OR CELLS.Phase = DECODE('', '1', '2', '' )
    OR CELLS.Phase = DECODE('', '1', '3', '' )
    )
    )

    If the checkbox is selected then the FIRST quote after the DECODE is replaced with 1. So in this example the match will return the value 2.

    SELECT CELLS.Zone
    , CELLS.SiteCode
    , CELLS.SARFVersion
    , CELLS.Phase
    FROM Cells CELLS
    WHERE
    (1=1
    AND CELLS.Zone = 'North East'
    AND ( CELLS.Phase = DECODE('', '1', '1','')
    OR CELLS.Phase = DECODE('1', '1', '2', '' )
    OR CELLS.Phase = DECODE('', '1', '3', '' )
    )
    )

    Region Phase
    --------------- ----------
    North East 2
    North East 2
    North East 2



    The result is Only Phase 2 Data is displayed. However if Users don’t select a Phase in the combo the where Clause returned is

    CELLS.Phase = ‘’. Since there are NO records with a Phase of BLANK. We get No rows returned. What I really want is ALL Rows.

    How can I construct the SQL to show this?

    Any help will be great !

  2. #2
    Join Date
    May 2002
    Posts
    108
    I wonder if I have understood your requirement ! :(

    I reckon you have complicated things a little.

    Any way replace

    AND CELLS.Zone = 'North East'

    with

    AND CELLS.Zone = nvl(combolist.value,CELLS.Zone)


    HTH

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    What you *really* want to do is dynamically construct the SQL for the exact data you want. Writing generic statements that handle multiple choices are almost always *slower*. However, I don't use Oracle's applications, so maybe the proper solution is not available to you.

    If not, then here's a WHERE clause with some binds:
    Code:
       C.Zone   =   :Zone       AND
          (
          :Phase1   =   1   AND
          C.Phase   =   1
          )                     OR
          (
          :Phase2   =   1   AND
          C.Phase   =   2
          )                     OR
          (
          :Phase3   =   1   AND
          C.Phase   =   3
          )                     OR
          (
          :Phase1   IS NULL   AND
          :Phase2   IS NULL   AND
          :Phase3   IS NULL
          )
    Then again, if you can't do binds (again, I don't know exactly how bad Oracle apps are), then someone's going to have to tell me what you 'can' do.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width