ORA-00936: missing expression using prompt

Question

Cannot get a CASE WHEN to work in my query using a variable.

select field1, field2
from table
where 1=1
AND GLCC.SEGMENT2 
    (CASE
    WHEN :P_AccountType =  'B' THEN  < 40000
    WHEN :P_AccountType =  'P' THEN => 40000
    ELSE
    BETWEEN '00000' AND '99999'
    END)

any idea where/what I do wrong

Many thanks.

(CASE WHEN :P_AccountType = 'B' THEN SEGMENT2 < '40000' WHEN :P_AccountType = 'P' THEN SEGMENT2 >= '40000' ELSE SEGMENT2 BETWEEN '00000' AND '99999' END) but this results in: ORA-00905: missing keyword — Fernando, Feb 24 '19 at 21:00

score 0 · Answer 1 · answered Feb 24 '19 at 12:25

0

I don't think the variable is the problem, you can't build a WHERE clause with syntax like that, as far as I can see.

Also, what is the 1=1 for?

And the => should surely be the other way round.

And why are you using a numeric representation of the number in two places and a character one in the other?

Try

select field1, field2 from table
where (GLCC.SEGMENT2 < 40000 and :P_AccountType = 'B')
or  (GLCC.SEGMENT2 >= 40000 and :P_AccountType = 'P' )
or (GLCC.SEGMENT2 BETWEEN 0 AND 99999 and :P_AccountType not in ('B','P'))

or something like that (not in a position to test!)

answered Feb 24 '19 at 12:25

MandyShaw

1,088
3
14
22

Many many thanks. your remarks about character vs numeric is correct. I mistake from me. Your suggestion works for B, for rest I do get a time out. Why do you combine variable with segment value? My intention was depending on variable input to select range of segment2 values. – Fernando Feb 24 '19 at 17:18
I wasn't really thinking about how the variable was used, sorry, just about the where clause. Maybe try using a subselect so the variable is only specified once. Sorry, no more time. – MandyShaw Feb 24 '19 at 18:46

Barbaros Özhan · Answer 2 · 2019-02-24T20:41:30.820

0

I think you need such a collation as below :

SELECT field1, field2
  FROM "table"
 WHERE  
     (CASE
      WHEN :P_AccountType =  'B' AND SEGMENT2 <  '40000' THEN  1
      WHEN :P_AccountType =  'P' AND SEGMENT2 >= '40000' THEN  1
      ELSE  
          CASE WHEN SEGMENT2 BETWEEN '00000' AND '99999' THEN  1 END 
          --> the above case line is needed when :P_AccountType is neither "B", nor "P"
      END) = 1

edited Feb 24 '19 at 20:41

answered Feb 24 '19 at 14:53

Barbaros Özhan

59,113
10
31
55

Barbaros, many thanks. query runs very fast, but outcome is always same. Tried also by making values text by adding ' around the values. As segment2 values are text and not numeric. But did not make a difference. Don't quite understand purpose of all make 1? – Fernando Feb 24 '19 at 17:22
@Fernando you're welcome. Please share the sample data to help you better. the value `1` does not have a special meaning, you can replace all `1` with `0` or any other value like `xYZ`, the main point is to satisfy the equality. – Barbaros Özhan Feb 24 '19 at 18:07
Hi Barboros, User can select Accountype A = Trial Balance Accounts 00000 - 99999 B = BS Accts 00000 -39999 P = All IS Accts 40000- 99999 This variable result in a range. So :P_AccountType =B Segment2 should contain all Balance sheet accounts 00000 - 39999 etc. Because segment2 values are strings I used apostrophes unless you advise me different. – Fernando Feb 24 '19 at 20:32
@Fernando OK, then we need to keep quotes merely. By the way, rather, you can edit the question with these details. – Barbaros Özhan Feb 24 '19 at 20:43

ORA-00936: missing expression using prompt

2 Answers2