Skip to Content
avatar image
Former Member

Problem with query using ODLN and DLN1

Hello

I have the following Query which is functioning OK.

But to get correct result I need to change last line (I need to use ODLN date instead of DLN1 date):

So I want to use WHERE (T0.DocDate >= [%0] AND T0.DocDate <= [%1])

But for some reason Query no longer functions when I make the change.

Please advise where I am in error and how I can use ODLN.DocDate.

Thanks for any assistance

K

SELECTT0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode as [Supplier Code],T0.CardName AS [Supplier Name],T2.Territory,T1.ItemCode,T1.Dscription AS [Item Description],T3.ItmsGrpCod,T4.ItmsGrpNam,T1.Quantity,T1.Currency,T1.Price ,T1.LineTotal AS 'Line t GB',T0.DocTotal AS 'Doc t GB'FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry  INNER JOIN OCRD T2 on T0.CardCode= T2.CardCodeINNER JOIN OITM T3 on T3.ItemCode = T1.ItemCodeINNER JOIN OITB T4 on T4.ItmsGrpCod = T3.ItmsGrpCodWHERE (T1.ActDelDate >= [%0] AND T1.ActDelDate <= [%1])
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 07, 2017 at 12:24 PM

    Hi,

    Try this,

    SELECT T0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode as [Supplier Code],T0.CardName AS [Supplier Name],T2.Territory,T1.ItemCode,T1.Dscription AS [Item Description],T3.ItmsGrpCod,T4.ItmsGrpNam,SUm(T1.Quantity),T1.Currency,T1.Price ,T1.LineTotal AS 'Line t GB',T0.DocTotal AS 'Doc t GB' FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode INNER JOIN OITM T3 on T3.ItemCode = T1.ItemCode INNER JOIN OITB T4 on T4.ItmsGrpCod = T3.ItmsGrpCod WHERE (T0.Docdate >= [%0] AND T0.DocDate <= [%1]) group by T0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode, T0.CardName ,T2.Territory,T1.ItemCode,T1.Dscription ,T3.ItmsGrpCod,T4.ItmsGrpNam,T1.Currency,T1.Price ,T1.LineTotal,T0.DocTotal

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 07, 2017 at 12:44 PM

    No problem to run here, just some blank spaces that was missing, not sure if is the real query or just the web site problem when you pasted the query

    SELECT T0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode as [Supplier Code],T0.CardName AS [Supplier Name],T2.Territory,T1.ItemCode,T1.Dscription AS [Item Description],T3.ItmsGrpCod,T4.ItmsGrpNam,T1.Quantity,T1.Currency,T1.Price ,T1.LineTotal AS 'Line t GB',T0.DocTotal AS 'Doc t GB' FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry  INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode INNER JOIN OITM T3 on T3.ItemCode = T1.ItemCode INNER JOIN OITB T4 on T4.ItmsGrpCod = T3.ItmsGrpCod WHERE (T1.ActDelDate >= [%0] AND T1.ActDelDate <= [%1])
    Add comment
    10|10000 characters needed characters exceeded

    • Hi Andrew,

      I agree with Danilo, no problem in the code, you need just to add some spaces in your code as below, and it will be working fine:

      SELECT T0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode as [Supplier Code],T0.CardName AS [Supplier Name],T2.Territory,T1.ItemCode,T1.Dscription AS [Item Description],T3.ItmsGrpCod,T4.ItmsGrpNam,T1.Quantity,T1.Currency,T1.Price ,T1.LineTotal AS 'Line t GB',T0.DocTotal AS 'Doc t GB' FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry  INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode INNER JOIN OITM T3 on T3.ItemCode = T1.ItemCode INNER JOIN OITB T4 on T4.ItmsGrpCod = T3.ItmsGrpCod
      WHERE (T1.ActDelDate >= [%0] AND T1.ActDelDate <= [%1])

  • Aug 07, 2017 at 10:50 AM

    Hi Andrew

    Query works fine the Docentry & DocNumber(ODLN table) will repeat as per the number of row level items [DLN1 table]

    SELECT T0.DocEntry,T0.DocNum,T0.DocDate,T0.CardCode as [Supplier Code],T0.CardName AS [Supplier Name],T2.Territory,T1.ItemCode,T1.Dscription AS [ItemDescription],T3.ItmsGrpCod,T4.ItmsGrpNam,T1.Quantity,T1.Currency,T1.Price ,T1.LineTotal AS 'LineGB',T0.DocTotal AS 'DocGB' 
    FROM ODLN T0 
     INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode
     INNER JOIN OITM T3 on T3.ItemCode = T1.ItemCode 
    INNER JOIN OITB T4 on T4.ItmsGrpCod = T3.ItmsGrpCod 
    WHERE (T0.DocDate >= [%0] AND T0.DocDate <= [%1])
    
    Add comment
    10|10000 characters needed characters exceeded