SQL expression evaluation not supported

[Henry Olive 5]

I wish everyone a healthy day

Interbase SQL

 

I'm getting  expression evaluation not supported err.msg. with below SQL

If I remove (Case.........end)  which is 2.Line in SQL command, The Sql works

What is wrong ?

 

SELECT O.DELVTYPE, SUM(OD.REMAINQTY) QTY,
       (Case When O.DELVTYPE ='Sea' then O.DELVDATE + 45 else O.DELVDATE + 15 end) as DELVDATE
FROM ORDETAIL OD
JOIN ORDERS O ON O.RNO=OD.RNO
WHERE OD.ITEMNO = 'ABX22'
GROUP BY O.DELVTYPE, O.DELVDATE  

HAVING SUM(OD.REMAINQTY) > 0

 

Thank You

 

Edited July 7, 2021 by Henry Olive

[Frickler 11]

What if you remove the parentheses around the case statement?

[Henry Olive 5]

Thank You Frickler,

If i remove parentheses nothing changes , still get the same err.msg.

Edited July 7, 2021 by Henry Olive

[Frickler 11]

It's possible that Interbase supports only "simple case", that is 

case foo
  when 1 then 'one'
  when 2 then 'two'
  else 'many'
end

 

[Henry Olive 5]

Thank You again Frickler,

I tried your last suggestion ( simple case)  but still getting the same err. msg.

[david_navigator 12]

Do you need to cast the results to be the same type of the field ?

[corneliusdavid 214]

Yes, IB uses "simple" case.  Try this:

SELECT O.DELVTYPE, SUM(OD.REMAINQTY) QTY,
       (Case O.DELVTYPE 
          When 'Sea' then O.DELVDATE + 45 
          else O.DELVDATE + 15 
        end) as DELVDATE
FROM ORDETAIL OD
JOIN ORDERS O ON O.RNO=OD.RNO
WHERE OD.ITEMNO = 'ABX22'
GROUP BY O.DELVTYPE, O.DELVDATE  
HAVING SUM(OD.REMAINQTY) > 0

 

[Lajos Juhász 293]

Most probably the problem is the group by part. You've there O.Delvdate that is not in the select list. Interbase 2017 Update 1 (http://docwiki.embarcadero.com/InterBase/2020/en/Enhancements_to_GROUP_BY_and_ORDER_BY) introduced the extended syntax. If you're using a version that support you can write:
 

SELECT O.DELVTYPE, SUM(OD.REMAINQTY) QTY,
       (Case O.DELVTYPE 
          When 'Sea' then O.DELVDATE + 45 
          else O.DELVDATE + 15 
        end) as DELVDATE
FROM ORDETAIL OD
JOIN ORDERS O ON O.RNO=OD.RNO
WHERE OD.ITEMNO = 'ABX22'
GROUP BY O.DELVTYPE, 3
HAVING SUM(OD.REMAINQTY) > 0

[Henry Olive 5]

Thank you so much  David,  CorneliusDavid,  Lajos