query working fine in sap b1 but how to use top 1 for below sub-query

[datta1990]

Hi Team,

I want output for below image

my Query is

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue], (SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]

[Johan_H · Accepted solution]

Hi Datta p,

Okay, I see, please try this:

SELECT TOP 10 T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue]
, (SELECT MAX(x.DocDate)
   FROM ((SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc)
          UNION ALL (SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          UNION ALL (SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)
          ) x) 'Date last used'
FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode 
WHERE T1.[OnHand] > 0 
ORDER BY T1.[ItemCode]

Regards,

Johan

[Johan_H]

Hi,

Your query appears to work just fine, and gives you 8 different Date Last Used columns.

What is the question?

Regards,

Johan

[datta1990]

Hi Sir,

I want top1 report for 8 different Date Last Used columns.

(last transaction date for that particular part code on below query? )

Regards,

Datta p

[datta1990]

Hi sir,

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]

Is my question Items in stock with the last transaction date for that particular part code and add new the column on above report.

ANS: I have created the report using below forms

1.Delivery note

2.Return

3.A/R Invoice

4.A/R credit note

5.Good recipient purchase order

6.Good Returns

7.A/P Invoice

8.A/P Credit Note

9.stock Transfer,

10Goods receipt

11.Goods Issue

Query:

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] ,

(SELECT MAX(x.DocDate) FROM

(

(SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OIGE z INNER JOIN IGE1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OIGN z INNER JOIN IGN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

UNION ALL

(SELECT top 1 z.[DocDate] FROM OWTR z INNER JOIN WTR1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)

) x) 'Date last used'

FROM OITM T0

INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]

Thanks, Sir

Datta Phulse