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 Go to next message
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Umm, probably because this
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
isn't valid CASE syntax.

The syntax for a searched CASE is
CASE
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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')
          )
Re: Case in a where clause [message #195624 is a reply to message #195573] Fri, 29 September 2006 13:41 Go to previous message
foxgolfer
Messages: 6
Registered: September 2006
Junior Member
Thanks to all for your help.
Previous Topic: update one table from another table
Next Topic: Can I improve create index
Goto Forum:
  


Current Time: Fri Dec 02 23:23:11 CST 2016

Total time taken to generate the page: 0.28762 seconds