Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need help on sql statement.

Posted on 2009-07-13
7
Medium Priority
?
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Technology Partners: 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!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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