Skip to Content

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

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]

date-last-used.png (14.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 19, 2017 at 08:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 19, 2017 at 08:20 AM

    Hi,

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

    What is the question?

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • 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