Jump to content
Henry Olive

SQL expression evaluation not supported

Recommended Posts

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 by Henry Olive

Share this post


Link to post

Thank You Frickler,

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

Edited by Henry Olive

Share this post


Link to post

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

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

 

Share this post


Link to post

Thank You again Frickler,

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

Share this post


Link to post

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

 

Share this post


Link to post

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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×