Skip to Content
0
May 10, 2010 at 02:29 PM

Help on query

87 Views

could you please help me I want to see the prices with special price and when there isn't any special price I have used union all and left outer join but it does not work

please see below

SELECT T2.[ItemCode],T3.[ItemName],T2.[Price],T4.[Price] FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[OPLN] T1 ON T0.ListNum = T1.ListNum INNER JOIN ITM1 T2 ON T1.ListNum = T2.PriceList INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode INNER JOIN OSPP T4 ON T3.ItemCode = T4.ItemCode WHERE T0.[CardCode] =[%0]

UNION ALL

SELECT T2.[ItemCode],T3.[ItemName],T2.[Price],T4.[Price] FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[OPLN] T1 ON T0.ListNum = T1.ListNum INNER JOIN ITM1 T2 ON T1.ListNum = T2.PriceList INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode LEFT OUTER JOIN OSPP T4 ON T3.ItemCode = T4.ItemCode WHERE T0.[CardCode] =[%0]