Hello,

I am trying to write a report that will tell me all items received today.  I can get a record of this using the JrnlRow.dat file and this query:

SELECT * FROM JrnlRow WHERE Journal = 4 AND RowDate = @SYSDATE@

However, when I try to tie this in with the LineItem.dat file my query times out.  This is the query that I run that causes the Connector Direct Query timeout:

SELECT * FROM
      LineItem LEFT JOIN JrnlRow ON LineItem.ItemRecordNumber = JrnlRow.ItemRecordNumber
WHERE
      Journal = 4 AND RowDate = @SYSDATE@

Can anyone tell me what I am doing wrong here?

Thank you,

Ben

Views: 21

Reply to This

Replies to This Discussion

Got it working with the help of some old forum posts I found.

SELECT
"LineItem"."ItemID",
"LineItem"."ItemDescription",
"LineItem"."Location",
"JrnlRow"."StockingQuantity" 'Qty Received'
FROM

("JrnlRow"
INNER JOIN "LineItem"
ON "JrnlRow"."ItemRecordNumber" = "LineItem"."ItemRecordNumber")
WHERE
"JrnlRow"."RowDate" = '2018-11-27' AND "JrnlRow"."Journal" = 4 AND "JrnlRow"."RowType" = 0

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service