Home » SQL & PL/SQL » SQL & PL/SQL » Case in a where clause
| Case in a where clause [message #195573] |
Fri, 29 September 2006 08:58  |
foxgolfer
Messages: 6
Registered: September 2006
|
Junior Member |
|
|
I have the following Select statement that I am trying to use in Crystal Reports:
SELECT FIRST_NAME, MIDDLE_INIT, LAST_NAME,
PROCESS_LEVEL, FTE_TOTAL, SCHEDULE,
MIDPOINT, ANNUALIZED, JOB_CLASS, PLEVELNAME,
GROUP_NAME, UNION_CODE, EMPLOYEE, JOB_CODE
FROM LR.V_LISTING
WHERE
NOT (UNION_CODE = 'CARP' OR UNION_CODE = 'ELECT' OR
UNION_CODE = 'LOCAL123' OR
UNION_CODE = 'LOCAL234' OR UNION_CODE = 'PLUMB') AND
(GROUP_NAME = 'G:ACTIVE' OR GROUP_NAME = 'G:INACTIVE') AND
(substr (SCHEDULE,3,3) <> 'EXC' or
substr(SCHEDULE,3,3) <> 'EXX') and
JOB_CLASS = 40 and
JOB_CODE >= '0000' AND JOB_CODE <= '9999' and
CASE WHEN &POS = '*' THEN substr(SCHEDULE,3,1) Like '*'
WHEN &POS = 'E' THEN substr(SCHEDULE,3,1) = 'E'
WHEN &POS = 'N' THEN substr(SCHEDULE,3,1) <> 'E'
END
And I receive the following error message when trying to test it in Toad:
ORA-00905: missing keyword and the cursor is flashing on the 1 in the first line of the Case.
Any help? Thanks.
|
|
|
|
| Re: Case in a where clause [message #195575 is a reply to message #195573] |
Fri, 29 September 2006 09:08  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Umm, probably because thisCASE
WHEN &POS = '*' THEN substr(SCHEDULE,3,1) Like '*'
WHEN &POS = 'E' THEN substr(SCHEDULE,3,1) = 'E'
WHEN &POS = 'N' THEN substr(SCHEDULE,3,1) <> 'E'
END
The syntax for a searched CASE isCASE
WHEN (boolean statement) THEN (value)
WHEN (boolean statement) THEN (value)
< ELSE (value) >
END
You've got: CASE
WHEN (boolean statement) THEN (boolean statement)
WHEN (boolean statement) THEN (boolean statement)
END
|
|
|
|
| Re: Case in a where clause [message #195577 is a reply to message #195573] |
Fri, 29 September 2006 09:18 |
foxgolfer
Messages: 6
Registered: September 2006
|
Junior Member |
|
|
|
So, can I do what I'm "attempting" to do? I.E., use the value of a parameter to alter the where clause. I had it written with an IIF until I discovered Oracle does not support an IIF.
|
|
|
|
| Re: Case in a where clause [message #195581 is a reply to message #195577] |
Fri, 29 September 2006 09:30 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, assuming that &pos is a proper substitution variable, then you should be able to do it without useing CASE at all:
AND ((&POS='*' AND substr(schedule,3,1) = '*')
OR(&POS='E' AND substr(schedule,3,1) = 'E')
OR(&POS='N' AND substr(schedule,3,1) !='E')
)
WHEN &POS = '*' THEN substr(SCHEDULE,3,1) Like '*'
WHEN &POS = 'E' THEN substr(SCHEDULE,3,1) = 'E'
WHEN &POS = 'N' THEN substr(SCHEDULE,3,1) <> 'E'
|
|
|
|
| Re: Case in a where clause [message #195585 is a reply to message #195573] |
Fri, 29 September 2006 09:46 |
foxgolfer
Messages: 6
Registered: September 2006
|
Junior Member |
|
|
I added the following per your instructions:
and
((&POS='*' AND substr(schedule,3,1) = '*') or
(&POS='E' AND substr(schedule,3,1) = 'E') or
(&POS='N' AND substr(schedule,3,1) !='E')
)
and it "started" searching and then I received the following error message:
ORA-00904: "E": invalid identifier
and the &POS prior to the ='N' is highlighted.
|
|
|
|
| Re: Case in a where clause [message #195588 is a reply to message #195585] |
Fri, 29 September 2006 09:56 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm guessing here (not using Crystal), but I suspect that it's being a bit simpleminded about with it's substitution parameters, and not including the '' wrappers for strings.
Try this:
AND (('&POS'='*' AND substr(schedule,3,1) = '*')
OR('&POS'='E' AND substr(schedule,3,1) = 'E')
OR('&POS'='N' AND substr(schedule,3,1) !='E')
) |
|
|
|
| Re: Case in a where clause [message #195590 is a reply to message #195573] |
Fri, 29 September 2006 09:59 |
foxgolfer
Messages: 6
Registered: September 2006
|
Junior Member |
|
|
|
I will try your latest suggestion. Also, I'm testing this using TOAD, not Crystal but the Select will be placed in Crystal after I get it to work. Also, is the * valid in Oracle for a wildcard? Shouldn't it be a %?
|
|
|
|
| Re: Case in a where clause [message #195591 is a reply to message #195590] |
Fri, 29 September 2006 10:09 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hey - I'm not psychic.
Your query said "LIKE '*'" which is syntactically equivalent to "='*'" as there's no oracle wild cards in it. If you meant "LIKE '%'" then you can just use:
AND (('&POS'='*')
OR('&POS'='E' AND substr(schedule,3,1) = 'E')
OR('&POS'='N' AND substr(schedule,3,1) !='E')
)
as the LIKE '%' will match anything.
It's probably worth checking that Crystal uses substitiution variables, and not bind variables.
|
|
|
|
| Re: Case in a where clause [message #195602 is a reply to message #195573] |
Fri, 29 September 2006 11:14 |
foxgolfer
Messages: 6
Registered: September 2006
|
Junior Member |
|
|
I am honestly not trying to confuse you, but I did qualify as being an Oracle newbie.
What I am trying to accomplish is if the user enters an * as a parameter, then I want to return all records. If I need to change
AND (('&POS'='*' AND substr(schedule,3,1) = '*')
to
AND (('&POS'='*' AND substr(schedule,3,1) = '%')
to accomplish that, then I will, IF it's correct. All I was asking was if the * is recognized by Oracle as a wildcard; I thought I read somewhere that the % is the equivalent to *.
|
|
|
|
| Re: Case in a where clause [message #195621 is a reply to message #195602] |
Fri, 29 September 2006 13:29 |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
if "*" is entered to return all records, then remove the comparison that goes with it:
AND (('&POS'='*')
OR('&POS'='E' AND substr(schedule,3,1) = 'E')
OR('&POS'='N' AND substr(schedule,3,1) !='E')
)
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 07:45:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
