Skip to Content
author's profile photo Former Member
Former Member

Query question/error

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jul 23, 2014 at 06:30 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 24, 2014 at 03:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.