mahesh mahesh - 2 months ago 10
SQL Question

"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"

I am executing the query in DB2 like this

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


got an exception


"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