CODEWITHSUNDEEP
sqloracle-databasecase
SSAT
SSAT

Reputation: 107

oracle case statement return multiple character

Hello please correct my query 

select * from table S1
WHERE  S1.stu ='12345'
AND S1.sem = (Select Case When col2 > col4 Then col2 Else col4 End From table2)
And S1.met1 in  (case when (S1.sem = 1) then ('1', 'D', 'F' )
                      when (S1.sem = 2) then ('1', '5', '7',  )
                      when (S1.sem = 3) then ('1',  'D', 'F' )
                      else s1.sem end);

case should be used because of union with tables. am getting

Error: missing keyword

for this query

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

That is not how case works. It is a scalar expression that returns only one value.

Replace it with basic logic:

where ( (S1.sem = 1 and s1.met1 in ('1', 'D', 'F' ) ) or
        (S1.sem = 2 and S1.met1 in ('1', '5', '7') ) or
        (S1.sem = 3 and s1.met1 in ('1', 'D', 'F' ) ) or
        (s1.sem not in (1, 2, 3) and s1.met1 = s1.sem)
      )

I would comment that based on the values you are using, it would seem that s1.sem is a number and s1.met1 is a character. For me, it is quite suspicious when a number is being compared to a string.

Upvotes: 6

Related Questions