-
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 !
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|