?
Solved

Need help on sql statement.

Posted on 2009-07-13
7
Medium Priority
?
160 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
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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