Solved

Need help on sql statement.

Posted on 2009-07-13
7
155 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:westdh
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24844772
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

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24844773

SELECT * 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
        )A WHERE result1 > 0 OR result2 > 0
ORDER BY ShippingStateProvince

0
 

Author Comment

by:westdh
ID: 24846379
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

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 14

Expert Comment

by:shru_0409
ID: 24846773
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
0
 

Author Comment

by:westdh
ID: 24847595
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
0
 

Author Closing Comment

by:westdh
ID: 31603055
Thanks this set me in the right direction
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now