Query question/error

[Former Member]

I have the following query that functions correctly:

SELECT T0.[CardCode] as 'Cust. No.', T0.[CardName] as 'Cust. Name', T0.[DocNum] as Document, T0.[DocCur] as Currency, T1.Rate, T0.[DocDate],T1.[ActDelDate] as 'Delivery date', datepart(MM,T0.[DocDate]) as Month,datepart(yyyy,T0.[DocDate]) as Year, T1.TotalFrgn, T1.[SubCatNum] as 'BP Catalog',T1.[ItemCode], T1.[Dscription] as 'Item Descr.', T1.[Quantity], T1.[Price] as 'Sale price', T1.[LineTotal] as Revenues, T1.VatSumSy as 'Revenues VAT', T0.TotalExpns as Freight, T8.[Segment_0], T8.[AcctName], T3.U_ProductGroups, T3.U_ProductCategories, T5.[GroupName],T2.U_Dtmdiv,T6.Name as Country,

T7.[ItmsGrpNam],T4.SlpNAme, T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS], T0.Comments, T0.NumAtCard

INTO #T_TEMP

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode

LEFT OUTER JOIN OCRY T6 ON T2.Country = T6.Code INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod INNER JOIN OACT T8 ON T1.AcctCode = T8.AcctCode

where T0.[DocDate] >=  '[%FROM]' and T0.[DocDate] <=  '[%TO]'

UNION ALL

SELECT T0.[CardCode] as 'Cust. No.', T0.[CardName] as 'Cust. Name', T0.[DocNum] as Document, T0.[DocCur] as Currency, T1.Rate, T0.[DocDate],T1.[ActDelDate] as 'Delivery date', datepart(MM,T0.[DocDate]) as Month,datepart(yyyy,T0.[DocDate]) as Year, T1.TotalFrgn, T1.[SubCatNum] as 'BP Catalog',T1.[ItemCode], T1.[Dscription] as 'Item Descr.', -T1.[Quantity], T1.[Price] as 'Sale price', -T1.[LineTotal] as Revenues, -T1.VatSumSy as 'Revenues VAT' , -T0.TotalExpns as Freight, T8.[Segment_0], T8.[AcctName],  T3.U_ProductGroups, T3.U_ProductCategories, T5.[GroupName],T2.U_Dtmdiv,T6.Name as Country,

T7.[ItmsGrpNam],T4.SlpNAme, T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS], T0.Comments, T0.NumAtCard

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode

LEFT OUTER JOIN OCRY T6 ON T2.Country = T6.Code INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod INNER JOIN OACT T8 ON T1.AcctCode = T8.AcctCode

where T0.CANCELED = 'N' and T0.ObjType = 14 and T0.[DocDate] >=  '[%FROM]' and T0.[DocDate] <=  '[%TO]'

SELECT [Cust. No.], [Document], T0.[Currency], (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN Null  ELSE T0.Rate END) as 'Rate', [DocDate], [Delivery date], [Month], [Year], (CASE WHEN T0.TotalFrgn = 0 THEN Null ELSE T0.TotalFrgn END) as 'Total FC', [BP Catalog], T0.[ItemCode], [Item Descr.], [Quantity], (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN T1.Price  ELSE T1.Price * T0.Rate END) * T0.Quantity as 'Purchase Price', (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN T1.Price  ELSE T1.Price * T0.Rate END) as 'Costs', [Revenues], [Revenues VAT], [Freight], (CASE WHEN ISNULL(T0.[Freight],0)=0 OR T0.[Freight] = 0 THEN Null  ELSE T0.[Freight]/10 END) as 'Freight GST', [Sale price], [Segment_0], [AcctName], T0.U_ProductGroups, T0.U_ProductCategories, [Country], [U_Dtmdiv] as 'Business Line', [Cust. Name], [SlpNAme] as 'KAM', [GroupName], [ItmsGrpNam], T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS],  T0.Comments,  T0.NumAtCard as 'Customer Ref.No.'

FROM #T_TEMP T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OPLN T2 ON T1.PriceList = T2.ListNum LEFT JOIN ORTT T3 ON T0.DocDate = T3.RateDate AND T1.Currency = T3.Currency

WHERE T2.ListName = 'PURCHASE'

ORDER BY [DocDate],[Document]

----This query was written by someone else. I am trying to add a data field for the Ship To State for a sales tax reporting analysis. I know I need field StateS in INV12 table, but I keep getting errors. OINV Addresss2 is the entire shipping address, but I need to specifically add just the state. do I have to add an INNER JOIN INV12 T9 ON T0.Address2=T9.StateS????

Many thanks in advance

[kothandaraman_nagarajan]

Hi,

Try this join between tables:

OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry

same way for ORIN table:

ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN RIN12 T2 ON T0.DocEntry = T2.DocEntry

Thanks & Regards,

Nagarajan

[Johan_H]

Hi Courtney,

Indeed, you need to first join in the INV12 table, however the linking fields are DocEntry, and remember to use a unique table alias (so far you have used T0 to T8):

INNER JOIN INV12 T9 ON T0.DocEntry = T9.DocEntry

Next, because the query contains a UNION ALL clause, you need to make sure that you add a dummy / place holder field in the ORIN part for the StateS field. The StateS field is a nchar or nvarchar type field, so your dummy /  place holder field should be something like ' ' .

Regards,

Johan