Use select clause in case expression to set condition

Question

Query 1

    select * from worklog where worklog.recordkey IN
       case 
       when worklog.class ='ACTIVITY'  THEN (SELECT wonum from woactivity where parent ='M2176' )
       when worklog.class ='WORKORDER' THEN  (SELECT WONUM FROM WORKORDER WHERE WONUM = 'M2176')
       end ;

Query 2:

    SELECT * from worklog
       WHERE CASE WHEN worklog.class='ACTIVITY' THEN 
                       worklog.recordkey in (select wonum from woactivity where parent='M2176' ) 
             end
                  WHEN worklog.class='WORKORDER' THEN 
                       worklog.recordkey = (select wonum from workorder where wonum='M2176')  
             end  ;

in the above queries I need to set a value in worklog.recordkey. I have two conditions when worklog.class is 'ACTIVITY', I have to set vat value from WOACTIVITY wonum and if worklog.class is 'WORKORDER' then I have to set value from WORKORDER table. When I use the above queries I am getting Errors like:

'Incorrect syntax near the keyword 'in''

and

'Incorrect syntax near the keyword 'case''.

Please help me with this.

In case you aren't aware, and in case it makes your life easier, WOACTIVITY is just a view on WORKORDER where WOCLASS = 'ACTIVITY'. So, unless you have one you haven't shared, which would be fine, there is no technical reason why you need to choose whether to query WORKORDER or WOACTIVITY. Instead, just query WORKORER. If you want, join on WORKLOG.CLASS = WORKORDER.WOCLASS. — Preacher, May 22 '20 at 14:27

Preacher · Answer 1 · 2020-05-25T14:14:43.767

3

Because workorder is a table and woactivity is basically a view on that table where woclass = 'ACTIVITY', you can simplify your query this way:

select * from worklog where
(class, recordkey, siteid) in (select woclass, wonum, siteid from workorder)
and wonum='M2176'

And if the above doesn't work for you on SQL Server, per @Dex's comment, just flip it around:

select * From worklog where
exists (select 1 from workorder wo where 
    wo.woclass = worklog.class
    and wo.siteid = worklog.siteid
    and wo.wonum = worklog.wonum)
and wonum='M2176'

Note that I have added siteid into the comparison mix, since workorder is at the site level of information.

edited May 25 '20 at 14:14

answered May 22 '20 at 14:34

Preacher

2,127
1
11
25

FYI: I know this works in Oracle, but last I checked (2014-ish), this did not work in Microsoft SQL Server, which is a tag on the question. Back then, you could not do multi-field "in" statements like this, and a quick search says that is still the case. – Dex May 23 '20 at 04:23

score 2 · Answer 2 · answered May 22 '20 at 06:57

Skip the case expression, use regular AND/OR instead:

SELECT * from worklog
WHERE (worklog.class = 'ACTIVITY' and
       worklog.recordkey in (select wonum from woactivity where parent='M2176')) 
   OR (worklog.class='WORKORDER' AND 
       worklog.recordkey = (select wonum from workorder where wonum='M2176'))

John Morgan · Answer 3 · 2020-05-22T21:17:53.010

As mentioned above:

1) WORKORDER is a base table, and WOACTIVITY to a view of this table with a restriction based on the WORKORDER.WOCLASS attribute

2) The logical key for the WORKORDER object is SITEID + WONUM. i.e. a WONUM value may not be unique across the entire environment.

3) All records relating to a specific WORKORDER will have the same SITED + WOGROUP value.

4) The primary WORKORDER record will have ISTASK = 0 and WONUM = WOGROUP, while the associated activities will have ISTASK=1.

score 1 · Answer 4 · answered May 22 '20 at 06:59

You don't need to use CASE. I think you can accomplish what you're trying to do with boolean operators

Query 1

select * from worklog where 
  (worklog.class ='ACTIVITY' 
    and worklog.recordkey in 
      (select wonum from woactivity where parent ='M2176'))
  OR
  (worklog.class ='WORKORDER'
    and worklog.recordkey in
      (select wonum from workorder where wonum = 'M2176'));

Query 2

Here, I also changed the second condition on worklog.recordkey, from = to in

    select * from worklog
       where (worklog.class='ACTIVITY'
               and worklog.recordkey in
                 (select wonum from woactivity where parent='M2176'))
          or (worklog.class='WORKORDER' 
               and worklog.recordkey in
                 (select wonum from workorder where wonum='M2176'))

score 1 · Answer 5 · answered May 22 '20 at 13:09

You can do it with this too.

SELECT * 
FROM worklog w 
WHERE 1=1
    AND w.class = 'ACTIVITY' 
    AND w.recordkey IN (SELECT wonum FROM woactivity WHERE parent = 'M2176')
UNION ALL
SELECT * 
FROM worklog w 
WHERE 1=1
    AND w.class = 'WORKORDER' 
    AND w.recordkey IN (SELECT wonum FROM workorder WHERE parent = 'M2176')

Use select clause in case expression to set condition

5 Answers5

Query 1

Query 2