Solved

Need help on sql statement.

Posted on 2009-07-13
7
158 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 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server group with two values 4 40
How to structure query with count aggregate 4 44
SQL Select Query help 1 38
sql select total by week ending 3 24
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 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