1 2 Previous Next 17 Replies Latest reply on Aug 13, 2019 1:41 PM by Frank Kulash

    Need to alter existing query to fetch correct data.

    Vemula Muni

      Hi All,

       

      Below is my query

       

      WITH    parameters    AS

      (

          SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS startdt  -- Always use 4 digits for years

          ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS enddt

          ,       'CWR'                                  AS per_org

          FROM    dual

      )

      ,    got_business_unit   AS

      (

          SELECT  p.startdt, p.enddt

          ,       d1.emplid, d1.per_org, d1.effdt

          ,       CASE

                      WHEN  b.business_unit  IS NULL

                      THEN  'I'

                      ELSE  d1.hr_status

                  END         AS hr_status

          ,       b.business_unit

          FROM    parameters       p

          JOIN    ps_cts_job_dtl1  d1   ON  d1.per_org      = p.per_org

                                        AND d1.effdt       <= p.enddt

          OUTER APPLY (

                          SELECT  MAX (b.business_unit)  AS business_unit

                          FROM    ps_cts_job_dtl  d

                          JOIN    ps_cts_iqn_bu   b   ON  b.business_unit = d.business_unit

                          WHERE   d.emplid        = d1.emplid

                          AND     d.effdt         = d1.effdt

                      )            b

      )

      SELECT    g.emplid

      ,         GREATEST ( g.effdt

                         , g.startdt

                         )  AS startdt

      ,         LEAST ( e.enddt

                      , g.enddt

                      )     AS enddt

      ,         g.per_org

      ,         g.hr_status

      ,         'Employee is active as CWR and BU is in IQN_BU setup table'

                         AS reason

      FROM      got_business_unit  g

      OUTER APPLY (

                      SELECT  NVL ( MIN (gs.effdt) - 1

                                  , DATE '9999-12-31'

                                  )  AS enddt

                      FROM    got_business_unit  gs

                      WHERE   gs.emplid    = g.emplid

                      AND     gs.effdt     > g.effdt

                      AND     gs.hr_status = 'I'

                  )                e

      WHERE     hr_status  = 'A'

      AND       e.enddt    > g.startdt

      ORDER BY  emplid

      ,         startdt

      ;

       

      Out Put from the query is

      Need only one row for employee 733930 from 01-Jun-2019 to 03-Jun-2019.

      need only one row for employee 733931 from 26-Jun-2019 to 30-Jun-2019.

      I want out put like below based on below tables and data.

      Required output:

       

      Note : Reason column for understanding, no need to display reason column

      While fetching below out put Please map EMPLID,EMPL_RCD,EFFDT,EFFSEQ Key fields in both JOB_DTL and JOB_DTL1  ( Map  Key fields is very important )

       

      • Employee 733930 is active CWR in between '01-Jun-2019' to '03-Jun-2019' and employee have only one row with HR_STATUS ='A'  ( employee have more than one row with HR_STATUS = 'A' ) and Business_unit value present in PS_CTS_IQN_BU table.
      • Employee 733930 is active CWR in between '15-Jun-2019' to '18-Jun-2019' and employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
      • Employee 733930 is active as CWR  in between '19-JUN-2019' to '25-JUN-2019' in different BU which not in IQN_BU setup table, so i want to exclude that information.
      • Employee 733930 is active CWR from '26-Jun-2019' with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
      • Employee 733931 is active CWR from '06-Jun-2019' to '09-Jun-2019' and, employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
      • Employee 733931 is active CWR from '26-Jun-2019' to '30-Jun-2019' and he is having more than one row with HR_STATUS = 'A' and Business_unit value present in PS_CTS_IQN_BU table.

       

       

      EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
      73393001-Jun-201903-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
      73393015-Jun-201918-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
      73393026-Jun-201930-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
      73393106-Jun-201909-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
      73393126-Jun-1930-JUN-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table

       

      Below is the requirement details.

       

      1. I want to fetch the contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
      2. And active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table should be present in PS_CTS_IQN_BU table.
      3. there is a chance to have an employee multiple consecutive rows with hr_status='A'

       

      DBA Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

      Below is my "PS_CTS_JOB_DTL"  table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

      EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

      EFFDT : this filed use to track when change happens.

      EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

      ACTION : Will determine which action they taken on employee ( please don't consider action field to achieve output as below. because action codes may different in each time)

       

      Note : Data Volume of this table is more than 50 million rows

       

      PS_CTS_JOB_DTL Table
      EMPLIDBUSINESS_UNITEMPL_RCDEFFDTEFFSEQACTION
      733930US410027-May-20190ADD
      733930US410002-Jun-20190DTA
      733930US410004-Jun-20190TER
      733930US410106-Jun-20190HIR
      733930US410115-Jun-20190TER
      733930US410215-Jun-20191ADD
      733930US608219-Jun-20190ASC
      733930CN512226-Jun-20190ASG
      733931US410026-Apr-20190HIR
      733931US410005-Jun-20190TER
      733931IN208106-Jun-20190ADD
      733931IN208110-Jun-20190TER
      733931IN208212-Jun-20190HIR
      733931IN408218-Jun-20190ASC
      733931IN408219-Jun-20190TER
      733931IN208326-Jun-20190ADD
      733931IN208328-Jun-20190DTA

       

       

      PS_CTS_JOB_DTL1 Table :

       

      Note : Data Volume of this table is more than 50 million rows

      Below is my "PS_CTS_JOB_DTL1"  table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

      EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

      EFFDT : this filed use to track when change happens.

      EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

      HR_STATUS : To identify employee status ( A = Active, I = Inactive ).

      PER_ORG : to identify employee type ( CWR = Contract, EMP = Employee )

      Note : employee may contains more then one row with HR_STATUS = 'A' before he is going to inactive ( HR_STSTUS = 'I' )

       

      EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
      733930027-May-20190ACWR
      733930002-Jun-20190ACWR
      733930004-Jun-20190ICWR
      733930106-Jun-20190AEMP
      733930115-Jun-20190IEMP
      733930215-Jun-20191ACWR
      733930219-Jun-20190ACWR
      733930226-Jun-20190ACWR
      733931026-Apr-20190AEMP
      733931005-Jun-20190IEMP
      733931106-Jun-20190ACWR
      733931110-Jun-20190ICWR
      733931212-Jun-20190AEMP
      733931218-Jun-20190AEMP
      733931219-Jun-20190IEMP
      733931326-Jun-20190ACWR
      733931328-Jun-20190ADTA

       

      Below is my "PS_CTS_IQN_BU"  table structure and data.

       

      IQN BU Setp
      CountryBusiness_Unit
      USAUS410
      USAUS412
      CANCN512
      INDIN208

       

      1. CREATE TABLE PS_CTS_JOB_DTL (EMPLID VARCHAR2(11) NOT NULL,
      2.    BUSINESS_UNIT VARCHAR2(5) NOT NULL,
      3.    EMPL_RCD INT NOT NULL,
      4.    EFFDT DATE,
      5.    EFFSEQ INT NOT NULL,
      6.    ACTION VARCHAR2(3) NOT NULL) ;
      7. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('27-MAY-2019','DD-MON-YYYY'),0,'ADD');
      8. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-2019','DD-MON-YYYY'),0,'DTA');
      9. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-2019','DD-MON-YYYY'),0,'TER');
      10. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'HIR');
      11. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-2019','DD-MON-YYYY'),0,'TER');
      12. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-2019','DD-MON-YYYY'),1,'ADD');
      13. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'ASC');
      14. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ASG');
      15. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-2019','DD-MON-YYYY'),0,'HIR');
      16. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-2019','DD-MON-YYYY'),0,'TER');
      17. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-2019','DD-MON-YYYY'),1,'ADD');
      18. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-2019','DD-MON-YYYY'),0,'TER');
      19. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-2019','DD-MON-YYYY'),0,'HIR');
      20. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-2019','DD-MON-YYYY'),0,'ASC');
      21. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'TER');
      22. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ADD');
      23. Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-2019','DD-MON-YYYY'),0,'DTA'); 

      PS_CTS_JOB_DTL1 Table Build and Insert Scripts:

      1. CREATE TABLE PS_CTS_JOB_DTL1 (EMPLID VARCHAR2(11) NOT NULL,
      2.    EMPL_RCD INT NOT NULL,
      3.    EFFDT DATE,
      4.    EFFSEQ INT NOT NULL,
      5.    HR_STATUS VARCHAR2(1) NOT NULL,
      6.    PER_ORG VARCHAR2(3) NOT NULL) ;
      7. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('27-MAY-2019','DD-MON-YYYY'),0,'A','CWR');
      8. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
      9. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('04-JUN-2019','DD-MON-YYYY'),0,'I','CWR');
      10. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
      11. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('15-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
      12. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('15-JUN-2019','DD-MON-YYYY'),1,'A','CWR');
      13. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
      14. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_Date('26-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
      15. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('26-APR-2019','DD-MON-YYYY'),0,'A','EMP');
      16. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('05-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
      17. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
      18. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('10-JUN-2019','DD-MON-YYYY'),0,'I','CWR');
      19. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('12-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
      20. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('18-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
      21. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
      22. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('26-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
      23. Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-2019','DD-MON-YYYY'),0,'A','CWR');

      PS_CTS_IQN_BU Table Build and Insert Scripts: ( IQN business unit setup )

      1. CREATE TABLE PS_CTS_IQN_BU (COUNTRY VARCHAR2(3) NOT NULL,
      2.    BUSINESS_UNIT VARCHAR2(5) NOT NULL) ;
      3. Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US410');
      4. Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US412');
      5. Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('CAN','CN512');
      6. Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('IND','IN208');
        • 1. Re: Need to alter existing query to fetch correct data.
          Frank Kulash

          Hi,

           

          This looks almost identical to reply #12 in your earlier thread: Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

          Please don't post the same question in multiple threads.  It's confusing for people trying to answer questions, and it's confusing for you, too, since you have to look in multiple places for answers.  Mark one of these threads as "Answered" before we go any farther.

           

          The query you posted meets the requirements you posted.

          1. It fetches contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
          2. It only returns rows where active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table is present in PS_CTS_IQN_BU table.
          3. It handles the case were an employee has multiple consecutive rows with hr_status='A'.   (It may not handle them the way you want, but you haven't said what that way is.)

          You apparently have other requirements you haven't posted yet.

          • 2. Re: Need to alter existing query to fetch correct data.
            Vemula Muni

            Hi Frank Kulash,

             

            EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
            733930027-May-20190ACWR
            733930002-Jun-20190ACWR
            733930004-Jun-20190ICWR

             

            Employee 733930 is active as CWR from 27-May-2019 to 03-Jun-2019.  and he is having one extra row with HR_STATUS = 'A' and effdt 02-Jun-2019. (there is a chances employee may have more than one row with HR_STATUS = 'A' between active and inactive.). And for each effdt row employee Business_unit also available in IQN BU setup table.

             

            When i run the query with start date 01-JUN-2019 and end date 30-JUN-2019 Query is fetching two rows. 01-JUN-2019 to 03-JUN-2019 one row and 02-JUN-2019 to 03-JUN-2019 one row.

             

            instead of fetching two rows i want to fetch only one row from 01-JUN-2019 to 03-JUN-2019.  Because he is active CWR from 01-JUN-2019 to 03-JUN-2019 .

            Note :

            1. when ever there is more than one row with HR_STATUS = 'A' between start and end dates, we need to check for each row business unit is available or not in the IQN BU table. If business unit is available then we need to skip that row.

            in this case 27-MAY-2019 effdt row business unit valu is present in IQN BU table so we are fetching greatest value 01-JUN-2019 as start date.

            For 02-JUN-2019 effdt row business unit also available in IQN BU table so we need to skip this row. ( but query is fetching this row )

            2. If Business unit value is not available in IQN BU table then we need to treat row as HR_STATUS is inactive. ( This one is working fine in the Query)

             

            similarly for Employee 733931 also it is fetching two rows for below data.

             

            EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
            733931326-Jun-20190ACWR
            733931328-Jun-20190ADTA

             

            Query is fetching 26-JUN-2019 to 30-JUN-2019 one row and 28-JUN-2019 to 30-JUN-2019 second row.    For 28-JUN-2019 effdt row business unit value is available in IQN BU table so  i want to fetch only one from 26-JUN-2019 to 30-JUN-2019.

            • 3. Re: Need to alter existing query to fetch correct data.
              Vemula Muni

              Hi Frank Kulash , Could you please help me out on this.

              • 4. Re: Need to alter existing query to fetch correct data.
                Frank Kulash

                Hi,

                 

                Sorry, I still don't understand why you want the results you posted.  The specific examples are good, but I need a general rule, such as "When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A', then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."

                • 5. Re: Need to alter existing query to fetch correct data.
                  Vemula Muni

                  Hi Frank Kulash, Yes correct When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A',  and business unit also should be present in IQN BU table for all consecutive rows then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."   { If business unit is not available in IQN BU table for consecutive rows then we need to treat that row as inactive. Currently we are checking this thing we are validating in query }

                  • 6. Re: Need to alter existing query to fetch correct data.
                    Frank Kulash

                    Hi,

                     

                    Here's one way to do it:

                    WITH    parameters    AS

                    (

                        SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt  -- Always use 4 digits for years

                        ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS penddt

                        ,       'CWR'                                  AS per_org

                        FROM    dual

                    )

                    ,    got_business_unit   AS

                    (

                        SELECT  p.pstartdt, p.penddt

                        ,       d1.emplid, d1.per_org, d1.effdt

                        ,       CASE

                                    WHEN  b.business_unit  IS NULL

                                    THEN  'I'

                                    ELSE  d1.hr_status

                                END         AS hr_status

                        ,       b.business_unit

                        FROM    parameters       p

                        JOIN    ps_cts_job_dtl1  d1   ON  d1.per_org      = p.per_org

                                                      AND d1.effdt       <= p.penddt

                        OUTER APPLY (

                                        SELECT  MAX (b.business_unit)  AS business_unit

                                        FROM    ps_cts_job_dtl  d

                          JOIN    ps_cts_iqn_bu   b   ON  b.business_unit = d.business_unit

                                        WHERE   d.emplid        = d1.emplid

                                        AND     d.effdt         = d1.effdt

                                    )            b

                        WHERE   d1.hr_status  IN ('A', 'I')  -- if necessary

                    )

                    SELECT    emplid

                    ,         GREATEST (sdt, pstartdt)  AS startdt

                    ,         LEAST  ( NVL (edt - 1, penddt)

                                     , penddt

                                     )                 AS enddt

                    ,         per_org

                    ,         'A'                      AS hr_status

                    ,         'Employee is active as CWR and BU is in IQN_BU setup table'

                                                       AS reason

                    FROM      got_business_unit

                    MATCH_RECOGNIZE

                              (

                                  PARTITION BY  emplid, pstartdt, penddt, per_org

                                  ORDER BY      effdt

                                  MEASURES      MIN (effdt)    AS sdt

                                  ,             i.effdt        AS edt

                                  ONE ROW PER MATCH

                                  PATTERN       (a+ i?)

                                  DEFINE        a  AS   hr_status  = 'A'

                                  ,             i  AS   hr_status  = 'I'

                       )

                    WHERE     NVL (edt - 1, penddt)  > pstartdt

                    ORDER BY  emplid

                    ,         sdt

                    ;

                    The sub-queries are almost the same as the corresponding sub-queries you posted.

                    In parameters, I changed the column names to pstartdt and penddt, to avoid confusing them with startdt and enddt elsewhere.

                    In got_business_unit, I added a condition "WHERE   d1.hr_status  IN ('A', 'I')".  If hr_status is always 'A' or 'I', then you don't need it.

                    • 7. Re: Need to alter existing query to fetch correct data.
                      Vemula Muni

                      Hi Frank Kulash, Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.

                       

                      • 8. Re: Need to alter existing query to fetch correct data.
                        Frank Kulash

                        HI,

                        Vemula Muni wrote:

                         

                        Hi Frank Kulash, Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.

                         

                        That doesn't look like an Oracle error message.

                         

                        IN and OUT parameters occur in PL/SQL procedures.  Nothing in this thread before this has even mentioned PL/SQL.  Are you doing something other than what is posted here?

                         

                        I don't get any error when I run the query above in SQL*Plus (version 12.2.0.1.0).  Do you?

                         

                        What front end are you using when you get the error you posted?  If it's detecting an error on line 1, then maybe that front end doesn't recognize WITH clauses (which have been part of Oracle since version 9.1, released in 2001).

                        The query you posted in your original message also started with WITH.  Did it raise an error?

                        • 9. Re: Need to alter existing query to fetch correct data.
                          Vemula Muni

                          Hi Frank Kulash, I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).

                          • 10. Re: Need to alter existing query to fetch correct data.
                            Frank Kulash

                            Hi,

                            Vemula Muni wrote:

                             

                            Hi Frank Kulash, I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).

                            So you're using SQL Developer version 1.5.5?  That sounds kind of old.

                             

                            The query in reply #6 runs (and returns the results you requested) when I try it in SQL Developer 4.1.4.21.  It looks like you have a separate problem that involves SQL Developer, not SQL or PL/SQL.  Start a new thread in the SQL Developer forum.  When posting there, simplify the query as much as possible, so that it still causes the same error.  That will also help isolate exactly what couases the problem, which will help if you can't solve the SQL Developer issue and need to find a different way to run the query

                            • 11. Re: Need to alter existing query to fetch correct data.
                              Ranagal

                              Or how about downloading a latest version from the internet and installing the same that is compatible with 12c ?

                               

                              Regards,

                              Ranagal

                              • 12. Re: Need to alter existing query to fetch correct data.
                                Glen Conway

                                Your screen shot of SQL Developer 1.5.5 running on Java JDK 1.8 is pretty stunning.  However you got it to work, you should at least have had to click through some dialog saying 1.5.5 does not support that version of Java.

                                 

                                And even getting that far would be pretty surprising.  Ditto for achieving a connection to Oracle 12c.  For me, this is a definite +1 on upgrading.

                                • 13. Re: Need to alter existing query to fetch correct data.
                                  Vemula Muni

                                  Hi Frank Kulash, Thanks for your kind support. Now this is working fine in SQL Developer 4.1.4.21. Once again Thanks a lot.

                                  • 14. Re: Need to alter existing query to fetch correct data.
                                    L. Fernigrini

                                    Just an additional comment, when using hard-coded dates, try to avoid using month names, use numbers instead.

                                     

                                    This piece of code from your query would work in my environment:

                                     

                                     

                                    TO_DATE ('01-JUN-2019', 'DD-MON-YYYY')

                                     

                                     

                                    But if the month was August, you would have used AUG, and that's not a valid abbreviation for months in Spanish :-).

                                     

                                    Since the conversions depends on the NLS settings of the sessions, you may find issues if any user uses another language... That may not be possible on some systems, but better prevent the issue than complain later!

                                     

                                    You should use:

                                     

                                    TO_DATE ('01-06-2019', 'DD-MM-YYYY')
                                    1 2 Previous Next