2 Replies Latest reply: Jun 10, 2014 5:52 AM by Banu Alexandru RSS

    Item details with Last Transaction Date ?

    RamaKrishnan S

      Hi,

       

      Please help someone to fine tune this query :-

       

      Select ILLITM,ILDOC, ILDCT, ILTRDJ

      FROM CRPDTA.F4111

      where ILTRDJ in (Select max(ILTRDJ) FROM CRPDTA.F4111);

       

      The above query is returning the latest transaction date with Item details.

      But expected output is: Every Item with last transactional date.

       

      Hope I am clear on the above requirement please.

       

      Thanks in advance.

      RAM

        • 1. Re: Item details with Last Transaction Date ?
          user13481010

          you can use table alias for this

          Select ILLITM,ILDOC, ILDCT, ILTRDJ

          FROM CRPDTA.F4111 tab1

          where tab1.ILTRDJ in (Select max(tab2.ILTRDJ) FROM CRPDTA.F4111 tab2);


          or if you are using this query inside any PL-SQL block , you can use a variable to hold max(ILTRDJ) and then use this variable in query.

          • 2. Re: Item details with Last Transaction Date ?
            Banu Alexandru

            Hi Rama,

             

            You need to link the item in the primary query with the max in the subquery.

             

            Assuming that ILLITM is the item than the query would be like this:

             

            select X.ILLITM, X.ILDOC, X.ILDCT, X.ILTRDJ
                 from CRPDTA.F4111 X
                 where X.ILTRDJ in (select max(ILTRDJ) 
                                                         from CRPDTA.F4111 Y 
                                                         where Y.ILLITM = X.ILLITM);
            
            

             

            Thank you,

            Alex.