mahesh mahesh - 1 month ago 8
SQL Question

"AMOUNT1" is not valid in the context where it is used

I'm executing the the following query in DB2:

select
SUM (orders.totalproduct
+orders.TOTALTAX
+orders.totalshipping
-orders.totaladjustment) as amount1
from
orders
where
amount1>10000


The query is not executed, I get this exception instead:


"AMOUNT1" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.64.96 SQL Code: -206, SQL State: 42703


What am I doing wrong?

Answer

You can't create and use amount1 in same time in DB2.

Try this:

 select * from (
 select SUM (orders.totalproduct+orders.TOTALTAX+orders.totalshipping- orders.totaladjustment) as amount1 from orders 
 ) tmp where amount1>10000

or this:

select SUM (orders.totalproduct+orders.TOTALTAX+orders.totalshipping-  orders.totaladjustment) as amount1 from orders 
having  SUM (orders.totalproduct+orders.TOTALTAX+orders.totalshipping-orders.totaladjustment)>10000
Comments