Skip to Content
0

Problem with query using ODLN and DLN1

Aug 07, 2017 at 10:33 AM

79

avatar image
Former Member

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])
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Nagarajan K Aug 07, 2017 at 12:24 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Danilo Kasparian Aug 07, 2017 at 12:44 PM
0

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])
Show 1 Share
10 |10000 characters needed characters left 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])

0
Balaji Selvaraj Aug 07, 2017 at 10:50 AM
0

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])
Share
10 |10000 characters needed characters left characters exceeded