Using OR to show all records if NULL criteria selected

[mjjrr1]

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 !

[nandu]

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

[chrisrlong]

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:

   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