9 Replies Latest reply on Nov 18, 2019 3:57 PM by AQH

    query for related criteria.

    AQH

      greetings!

      i require below result from my 2-tables name demand and mainlog.

      mainlog table has log history data, while demand table get input from user with a portfolio number and other columns, below data input by user has 21927 portfoliono with 5 different (unique) toolno with location(required column) and sublocation(optional column) there is no pk-fk relation in these 2 tables but some same columns has values in both like toolno, requireforMainLoc & fitInMainLoc, requireforSubLoc & fitinSubLoc.

       

      Result in a way that;

      1. first check these three columns i.e toolno:fitInMainLoc:fitInSubLoc if found then get record which has last transaction in MainLogDate.
        • line 5 shows data of date 11/01/2019 with qty from mainlog of toolno 13024437 as toolno, fitInMainLoc and fitInSub log found.
      2. if above fails than check toolno:fitInMainLoc if found then get record which has last transaction in MainLogDate.
        • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInMainLoc.
      3. if above both fails than check toolno  if found then get record which has last transaction in MainLogDate.
        • line 4 shows data of date 11/02/2019 with qty from mainlog of toolno 11050566 as it found toolno.
      4. shows 'not avaiable if all three column not found in mainlog.
        • as shown in line 2 &3.

      *im using oracle db 11.2.0.2 64bit

       

      Header 1

      data

       

      create table demand (ddate date not null, uncode number(10) primary key, portfoliono varchar2(15) not null,

      toolno varchar2(8), requireformainloc varchar2(15) not null,requireforsubloc varchar2(15),

      CONSTRAINT demand_uk_toolno UNIQUE (toolno)) ;

       

      insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),1,'0000021927', '13027346', 'loc-cs', 'sl-1');

      insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),2,'0000021927', '13027347', 'loc-cs', 'sl-1');

      insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),3,'0000021927', '11050566', 'loc-cs', 'sl-1');

      insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),4,'0000021927', '13024437', 'loc-cs', 'sl-1');

      insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),5,'0000021927', '13023661', 'loc-cs', 'sl-1');

       

       

      create table mainlog (mdate date not null, uncode  number(10) primary key,

      toolno varchar2(8) not null, fintinMainLoc varchar2(15) not null,

      fitinSubLoc varchar2(15), qty number(5)

      );

      insert into mainlog values (to_date('10/01/2019', 'dd/mm/yyyy'), 20,'13024437','loc-cs','sl-2',2);

      insert into mainlog values (to_date('11/01/2019', 'dd/mm/yyyy'),21,'13024437','loc-cs','sl-1',1);

      insert into mainlog values (to_date('12/05/2018', 'dd/mm/yyyy'),22,'13024437','loc-aq','',    4);

      insert into mainlog values (to_date('11/02/2019', 'dd/mm/yyyy'),23,'13023661','loc-x2','sl-2',1);

      insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),24,'13023661','loc-aq',''    ,3);

      insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),25,'13023661','loc-x3','sl-1', 3);

      insert into mainlog values (to_date('11/02/2019', 'dd/mm/yyyy'),26,'11050566','loc-x2','sl-2', 1);

      insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),27,'11050566','loc-aq','', 3);

      insert into mainlog values (to_date('12/12/2018', 'dd/mm/yyyy'),28,'11050566','loc-x3','sl-1',3);

        • 1. Re: query for related criteria.
          AQH

          For constraint CONSTRAINT demand_uk_toolno UNIQUE (toolno) I'll use

          CONSTRAINT demand_uk_toolno UNIQUE (toolno,pirtfoliono).

          • 2. Re: query for related criteria.
            Frank Kulash

            Hi,

            AQH wrote:

             

            greetings!

            i require below result from my 2-tables name demand and mainlog.

            mainlog table has log history data, while demand table get input from user with a portfolio number and other columns, below data input by user has 21927 portfoliono with 5 different (unique) toolno with location(required column) and sublocation(optional column) there is no pk-fk relation in these 2 tables but some same columns has values in both like toolno, requireforMainLoc & fitInMainLoc, requireforSubLoc & fitinSubLoc.

             

            Result in a way that;

            1. first check these three columns i.e toolno:fitInMainLoc:fitInSubLoc if found then get record which has last transaction in MainLogDate.
              • line 5 shows data of date 11/01/2019 with qty from mainlog of toolno 13024437 as toolno, fitInMainLoc and fitInSub log found.
            2. if above fails than check toolno:fitInMainLoc if found then get record which has last transaction in MainLogDate.
              • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInMainLoc.
            3. if above both fails than check toolno if found then get record which has last transaction in MainLogDate.
              • line 4 shows data of date 11/02/2019 with qty from mainlog of toolno 11050566 as it found toolno.

            *im using oracle db 11.2.0.2 64bit

             

            Header 1

            data

             

            create table demand (ddate date not null, uncode number(10) primary key, portfoliono varchar2(15) not null,

            toolno varchar2(8), requireformainloc varchar2(15) not null,requireforsubloc varchar2(15),

            CONSTRAINT demand_uk_toolno UNIQUE (toolno)) ;

             

            insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),1,'0000021927', '13027346', 'loc-cs', 'sl-1');

            insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),2,'0000021927', '13027347', 'loc-cs', 'sl-1');

            insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),3,'0000021927', '11050566', 'loc-cs', 'sl-1');

            insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),4,'0000021927', '13024437', 'loc-cs', 'sl-1');

            insert into demand values (to_date('01/11/2019', 'dd/mm/yyyy'),5,'0000021927', '13023661', 'loc-cs', 'sl-1');

             

             

            create table mainlog (mdate date not null, uncode number(10) primary key,

            toolno varchar2(8) not null, fintinMainLoc varchar2(15) not null,

            fitinSubLoc varchar2(15), qty number(5)

            );

            insert into mainlog values (to_date('10/01/2019', 'dd/mm/yyyy'), 20,'13024437','loc-cs','sl-2',2);

            insert into mainlog values (to_date('11/01/2019', 'dd/mm/yyyy'),21,'13024437','loc-cs','sl-1',1);

            insert into mainlog values (to_date('12/05/2018', 'dd/mm/yyyy'),22,'13024437','loc-aq','', 4);

            insert into mainlog values (to_date('11/02/2019', 'dd/mm/yyyy'),23,'13023661','loc-x2','sl-2',1);

            insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),24,'13023661','loc-aq','' ,3);

            insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),25,'13023661','loc-x3','sl-1', 3);

            insert into mainlog values (to_date('11/02/2019', 'dd/mm/yyyy'),26,'11050566','loc-x2','sl-2', 1);

            insert into mainlog values (to_date('12/06/2018', 'dd/mm/yyyy'),27,'11050566','loc-aq','', 3);

            insert into mainlog values (to_date('12/12/2018', 'dd/mm/yyyy'),28,'11050566','loc-x3','sl-1',3);

            As I understand it, this is a Top-N Problem, where, for every row in demand, you want to find the best matching row in mainlog (that is, the best row with the same toolno), where the "best" match is determined by these rule (in order):

            Rows where BOTH requireformainloc = fintinMainLoc AND requireforSubLoc = fitinSubLoc are best.  Let's call these Class A rows.

            Rows where just requireformainloc = fintinMainLoc are next best.  Let's call these Class B rows.

            Rows where requireformainloc <> fintinMainLoc are the worst.  Let's call these Class C rows

            If two or more rows are in the best class (by the rules above), then the one with the latest mdate is best.

            Here's one way to do that:

             

            WITH    got_rno    AS
            (
                SELECT  d.*
                ,       m.mdate, m.fintinMainLoc, m.fitinSubLoc, m.qty
                ,       m.toolno  AS MainLogToolNo
                ,       ROW_NUMBER () OVER
                            ( PARTITION BY  d.toolno
                              ORDER BY      CASE
                                                WHEN  d.requireformainloc
                                                   <> m.fintinMainLoc
                                                               THEN  'C'
                                                WHEN  LNNVL ( d.requireforSubLoc
                                                            = m.fitinSubLoc
                                                            )  THEN  'B'
                                                               ELSE  'A'
                                            END
                              ,             m.mdate  DESC --, m.uncode  DESC
                            )  AS rno
                FROM             demand   d
                LEFT OUTER JOIN  mainlog  m  ON  m.toolno  = d.toolno
            )
            SELECT    ddate, uncode, portfoliono, toolno, requireformainloc,requireforsubloc
            ,         NVL ( TO_CHAR (mdate, 'DD/MM/YYYY')
                          , 'not available'
                          )  AS mdate
            ,         MainLogToolNo, fintinMainLoc, fitinSubLoc, qty
            FROM      got_rno
            WHERE     rno  = 1
            ORDER BY  uncode
            ;

             

             

            The results I get are:

            DDATE      UNCODE PORTFOLIONO     TOOLNO   REQUIREFORMAINL REQUIREFORSUBLO
            ---------- ------ --------------- -------- --------------- ---------------
            MDATE         MAINLOGT FINTINMAINLOC   FITINSUBLOC       QTY
            ------------- -------- --------------- --------------- -----
            01/11/2019      1 0000021927      13027346 loc-cs          sl-1
            not available

            01/11/2019      2 0000021927      13027347 loc-cs          sl-1
            not available

            01/11/2019      3 0000021927      11050566 loc-cs          sl-1
            11/02/2019    11050566 loc-x2          sl-2                1

            01/11/2019      4 0000021927      13024437 loc-cs          sl-1
            11/01/2019    13024437 loc-cs          sl-1                1

            01/11/2019      5 0000021927      13023661 loc-cs          sl-1
            11/02/2019    13023661 loc-x2          sl-2                1

             

            I don't see how you get the results you do for toolno='13023661'.  There are three rows in mainlog with that toolno, they are all Class C matches, and the latest mdate of those three rows is 11/02/2019, not 12/06/2018.  Was there was a mistake somewhere in your message?

            1 person found this helpful
            • 3. Re: query for related criteria.
              AQH

              for toolno 13023661, it has latest mdate of those three rows is 11/02/2019 but there is sl-2 in this row, and  if all three ( toolno:fitInMainLoc:fitInSubLoc) fails than check toolno:fitInSubLoc if found then get record which has last transaction in MainLogDate.

              • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInMainLoc OR fitInSubinLoc.
              • 4. Re: query for related criteria.
                Frank Kulash

                Hi,

                AQH wrote:

                 

                for toolno 13023661, it has latest mdate of those three rows is 11/02/2019 but there is sl-2 in this row, and if all three ( toolno:fitInMainLoc:fitInSubLoc) fails than check toolno:fitInSubLoc if found then get record which has last transaction in MainLogDate.

                • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInMainLoc OR fitInSubinLoc.

                That's not what you said before:

                 

                Result in a way that;

                1. first check these three columns i.e toolno:fitInMainLoc:fitInSubLoc if found then get record which has last transaction in MainLogDate.
                  • line 5 shows data of date 11/01/2019 with qty from mainlog of toolno 13024437 as toolno, fitInMainLoc and fitInSub log found.
                2. if above fails than check toolno:fitInMainLoc if found then get record which has last transaction in MainLogDate.
                  • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInMainLoc.
                3. if above both fails than check toolno  if found then get record which has last transaction in MainLogDate.
                  • line 4 shows data of date 11/02/2019 with qty from mainlog of toolno 11050566 as it found toolno.
                4. shows 'not avaiable if all three column not found in mainlog.
                  • as shown in line 2 &3.

                In requirement #2, you said you wanted to check if only toolno and fitInMainLoc (or fintInMainLoc) match, but there's no special requirement for when fitInSubLOc matches, but fitInMainLoc does not.  If the columns were reversed in your original message then reverse them in the CASE expression in reply #2.

                 

                • 5. Re: query for related criteria.
                  AQH

                  thanks for reply and i admit that i have not explain properly condition 2 above it should be

                  if above fails than check toolno:fitInMainLoc or fintInSubLoc if found then get record which has last transaction in MainLogDate.

                  • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInSubLoc.

                  i have attempt with multiple option in CASE as per advice but not success for this toolno. 

                  • 6. Re: query for related criteria.
                    Frank Kulash

                    Hi,

                    AQH wrote:

                     

                    thanks for reply and i admit that i have not explain properly condition 2 above it should be

                    if above fails than check toolno:fitInMainLoc or fintInSubLoc if found then get record which has last transaction in MainLogDate.

                    • line 6 shows data of date 12/06/2018 with qty from mainlog of toolno 13023661 as it found toolno and fitInSubLoc.

                    i have attempt with multiple option in CASE as per advice but not success for this toolno.

                    In that case, for toolno='11050566' why do you want the row with

                    fintInMainLoc = 'loc-x2' and finInSubLoc = 'sl-2' and not the row with

                    fintInMainLoc = 'loc-x3' and finInSubLoc = 'sl-1'?

                    • 7. Re: query for related criteria.
                      AQH

                      OMG! you are genius genius ! yes it has to be loc-x3 with sl-1 for  '11050566'

                      • 8. Re: query for related criteria.
                        Frank Kulash

                        Hi,

                        AQH wrote:

                         

                        OMG! you are genius genius ! yes it has to be loc-x3 with sl-1 for '11050566'

                        Okay, then you here's one way to do it:

                        WITH    got_rno    AS

                        (

                            SELECT  d.*

                            ,       m.mdate, m.fintinMainLoc, m.fitinSubLoc, m.qty

                            ,       m.toolno  AS MainLogToolNo

                            ,       ROW_NUMBER () OVER

                                        ( PARTITION BY  d.toolno

                                          ORDER BY      CASE

                                                            WHEN  d.requireformainloc

                                                               =  m.fintinMainLoc

                                                                           THEN  0

                                                                           ELSE  1

                                                        END

                                                      + CASE

                                                            WHEN  d.requireforSubLoc

                                                                = m.fitinSubLoc

                                                                           THEN  0

                                                                           ELSE  1

                                                        END

                                          ,             m.mdate  DESC --, m.uncode  DESC

                                        )  AS rno

                            FROM             demand   d

                            LEFT OUTER JOIN  mainlog  m  ON  m.toolno  = d.toolno

                        )

                        SELECT    ddate, uncode, portfoliono, toolno, requireformainloc,requireforsubloc

                        ,         NVL ( TO_CHAR (mdate, 'DD/MM/YYYY')

                                      , 'not available'

                                      )  AS mdate

                        ,         MainLogToolNo, fintinMainLoc, fitinSubLoc, qty

                        FROM      got_rno

                        WHERE     rno  = 1

                        ORDER BY  uncode

                        ;

                        This is the same as the query in reply #2 except for the part in red.  The query here is sorting first by the number of mis-matches in those two columns (0 mis-matches is better than 1, and 1 is better than 2), then by mdate.

                        • 9. Re: query for related criteria.
                          AQH

                          thankyou very much!