asked on

Need help on sql statement.

I need some help on the following how can I include in my WHERE Statement the results of result1 and result2, whem I find I cannot add to the where statement the following .
and result1 > '0' or result2 > '0'. when I add the term result1 it give me an error code saying invalid column name. Is thier a way to resolve this?

SELECT e.Name, o.OrderID, o.CreatedOn, oi.Quantity, o.ShippingStateProvince,
result1 = CASE WHEN (pv.SKU <= '14' and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity * .670 * .26417 ELSE '0' END,
result2 = CASE WHEN (pv.SKU  > '14' and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity* .750 * .26417 ELSE '0' END,
 er.[Excise Tax Rates], er.OverPercentRate, pv.SKU, 'Name: ' + o.ShippingFirstName + ' ' + o.ShippingLastName + ' City: ' + o.ShippingCity AS Results 
FROM WC_StateCompliance AS e, Nop_OrderProductVariant AS oi, Nop_Order AS o, Nop_Customer As c, WC_Excise_Tax_Rates AS er, Nop_ProductVariant AS pv 
Where e.Name = o.ShippingStateProvince and o.OrderID = oi.orderID and oi.Quantity > 0 
ORDER BY o.ShippingStateProvince

Open in new window

Guy Hengel [angelIII / a3]

you cannot reuse column aliases in the where clause directly.
either you have to put the entire sql into a subquery, and put the where outside (see code attached), or you will have to repeat the expression.

also: for dumerical values, do NOT use quotes. sql server will eventually convert the numerical values to char, and give you unexpected results.

note: I fear the query you posted in incomplete or incorrect, as you have 6 tables, but only 2 join conditions ...

select sq.*
 from ( 
SELECT e.Name, o.OrderID, o.CreatedOn, oi.Quantity, o.ShippingStateProvince,
result1 = CASE WHEN (pv.SKU <= 14 and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity * .670 * .26417 ELSE 0 END,
result2 = CASE WHEN (pv.SKU  > 14 and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity* .750 * .26417 ELSE 0 END,
 er.[Excise Tax Rates], er.OverPercentRate, pv.SKU, 'Name: ' + o.ShippingFirstName + ' ' + o.ShippingLastName + ' City: ' + o.ShippingCity AS Results 
FROM WC_StateCompliance AS e, Nop_OrderProductVariant AS oi, Nop_Order AS o, Nop_Customer As c, WC_Excise_Tax_Rates AS er, Nop_ProductVariant AS pv 
Where e.Name = o.ShippingStateProvince and o.OrderID = oi.orderID and oi.Quantity > 0 
) sq
WHERE sq.result1 > 0 
   OR sq.result2 > 0
ORDER BY sq.ShippingStateProvince

Open in new window

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

westdh

ASKER

Using aneeshattingal:code with a litle modification I have gotten the query down to this.
Here is the code
--------------------------------------------------------------------------------------------------------------------

and in the results (attached to comment below by a3) ii am still getting to many rows returned for er.State = o.ShippingStateProvince
is not working correctly

SELECT * FROM
(SELECT e.License, e.Name, o.OrderID, o.CreatedOn, oi.Quantity, o.ShippingStateProvince,
result1 = CASE WHEN ( pv.SKU <= '14'and pv.SKU <> ' '
and er.OverPercentRate <> 'na'
and oi.Quantity > 0 )
THEN oi.Quantity * .670 * .26417
ELSE '0'
END,
result2 = CASE WHEN ( pv.SKU > '14' and pv.SKU <> ' '
and er.OverPercentRate <> 'na'
and oi.Quantity > 0 )
THEN oi.Quantity * .750 * .26417
ELSE '0'
END, er.[Excise Tax Rates], er.OverPercentRate, pv.SKU,
'Name: ' + o.ShippingFirstName + ' ' + o.ShippingLastName + ' City: '
+ o.ShippingCity AS Results
 
FROM WC_StateCompliance AS e
,Nop_OrderProductVariant AS oi
,Nop_Customer As c
,Nop_ProductVariant AS pv
,Nop_Order AS o
,WC_Excise_Tax_Rates AS er
 
Where
er.State = o.ShippingStateProvince
and o.OrderID = oi.orderID
and o.CustomerID = c.CustomerID
and oi.Quantity > 0
)A WHERE result1 > 0 OR result2 > 0

Open in new window

shru_0409

select * from r (SELECT e.Name, o.OrderID, o.CreatedOn, oi.Quantity, o.ShippingStateProvince,
CASE WHEN (pv.SKU <= '14' and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity * .670 * .26417 ELSE '0' END as result1,
CASE WHEN (pv.SKU > '14' and er.State = o.ShippingStateProvince and oi.Quantity > 0) THEN oi.Quantity* .750 * .26417 ELSE '0' END as result2,
--er.[Excise Tax Rates],
er.OverPercentRate, pv.SKU, 'Name: ' + o.ShippingFirstName + ' ' + o.ShippingLastName + ' City: ' + o.ShippingCity AS Results
FROM WC_StateCompliance AS e,
Nop_OrderProductVariant AS oi,
Nop_Order AS o, Nop_Customer As c,
WC_Excise_Tax_Rates AS er,
Nop_ProductVariant AS pv
Where e.Name = o.ShippingStateProvince
and o.OrderID = oi.orderID
-------- require three more inner join
and oi.Quantity > 0)
where (r.result1 + result2) > 0
ORDER BY r.ShippingStateProvince

westdh

ASKER

When I add c.CustomerID, to the select statement i get the following error...
Msg 8156, Level 16, State 1, Line 31
The column 'CustomerID' was specified multiple times for 'A'.
---------------------------------------------------------------------------------------------------------
SELECT * FROM
(SELECT c.CustomerID, o.CustomerID, e.License, e.Name, o.OrderID, o.CreatedOn, oi.Quantity, o.ShippingStateProvince,
result1 = CASE WHEN ( pv.SKU <= '14'and pv.SKU <> ' '
and er.OverPercentRate <> 'na'
and oi.Quantity > 0 )
THEN oi.Quantity * .670 * .26417
ELSE '0'
END,
result2 = CASE WHEN ( pv.SKU > '14' and pv.SKU <> ' '
and er.OverPercentRate <> 'na'
and oi.Quantity > 0 )
THEN oi.Quantity * .750 * .26417
ELSE '0'
END, er.[Excise Tax Rates], er.OverPercentRate, pv.SKU,
'Name: ' + o.ShippingFirstName + ' ' + o.ShippingLastName + ' City: '
+ o.ShippingCity AS Results

FROM Nop_Order AS o

INNER JOIN Nop_OrderProductVariant AS oi ON o.OrderID = oi.OrderID
INNER JOIN WC_StateCompliance As e ON e.Name = o.ShippingStateProvince

,Nop_Customer As c
,WC_Excise_Tax_Rates as er
,Nop_ProductVariant AS pv

Where
oi.Quantity > 0
)A WHERE result1 > 0 OR result2 > 0

westdh

ASKER

Thanks this set me in the right direction