This discussion is archived
7 Replies Latest reply: Sep 19, 2013 5:56 AM by AlbertoFaenza RSS

This looks like an Oracle bug to me

kendenny Expert
Currently Being Moderated

My Oracle version is 11.2.0.2.0

My emp_ref contains the following for emp_id 244

Emp idName
244Nick B

emp_det contains the following

Emp idFrom dateTo dateJob ind
24401-Jan-201131-Dec-2099P

emp_asgn contains the following

No data

The following query returns no data:

select er.name
      ,er.emp_id
      ,ed.from_date
      ,ed.to_date
      ,ed.job_ind
  FROM emp_ref er
INNER JOIN emp_det ed
    ON (ed.emp_id = er.emp_id)
  LEFT OUTER JOIN (SELECT emp_id
                         ,asgn_type
                     FROM emp_asgn
                    WHERE emp_id = 244
                      AND asgn_type IN ('IAR','IAB')
                      AND TRUNC(SYSDATE,'YEAR') BETWEEN start_date AND end_date) ea
    ON (ea.emplid = ed.emplid)
WHERE er.emplid = 244
   AND ed.job_ind = CASE
         WHEN ea.emp_id IS NULL THEN 'P'
         ELSE 'S'
       END
   AND TRUNC(SYSDATE,'YEAR') BETWEEN ed.from_date AND ed.to_date

 

Basically if there is row in emp_asgn there should also be a row in emp_det for that employee with job_ind = 'S'. If there is no row in emp_asgn then I want to use the row in emp_det with job_ind = 'P'. If I remove the case in the WHERE clause and just say "And job_ind = 'P'" it will return a row.

  • 1. Re: This looks like an Oracle bug to me
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    i don't understand you exactly but  i think you need this:

     

    select er.name
          ,er.emp_id
          ,ed.from_date
          ,ed.to_date
          ,ed.job_ind
      FROM emp_ref er
    INNER JOIN emp_det ed
        ON (ed.emp_id = er.emp_id)
      LEFT OUTER JOIN (SELECT emp_id
                             ,asgn_type
                         FROM emp_asgn
                        WHERE emp_id = 244
                          AND asgn_type IN ('IAR','IAB')
                          AND TRUNC(SYSDATE,'YEAR') BETWEEN start_date AND end_date) ea
        ON (ea.emplid = ed.emplid)

        and ed.job_ind = 'S'

    WHERE er.emplid = 244
    /*   AND ed.job_ind = CASE
             WHEN ea.emp_id IS NULL THEN 'P'
             ELSE 'S'
           END*/
       and (ea.emp_id is not null or ed.job_ind = 'P')  
       AND TRUNC(SYSDATE,'YEAR') BETWEEN ed.from_date AND ed.to_date

     

    ----

    Ramin Hashimzade

  • 2. Re: This looks like an Oracle bug to me
    kendenny Expert
    Currently Being Moderated

    I apologize. I see what my problem is. I have bad data. This employee does have an emp_asgn row but does not have an 'S' row in emp_det.

  • 3. Re: This looks like an Oracle bug to me
    kendenny Expert
    Currently Being Moderated

    Thanks Ramin. But the way you changed it could get me two rows. There's always a row with job_ind = 'P'. If there's a row in emp_asgn with asgn_type 'IAR' or 'IAB' then there should also be a row in emp_det with job_ind = 'S'. In this case there's not and that's what is causing the problem.

  • 4. Re: This looks like an Oracle bug to me
    Paul Horth Expert
    Currently Being Moderated

    That query won't even work - what's emplid?

     

    In 11.2.0.2.0  it returns expected results if corrected

     

    SQL> l

      1  with emp_ref as (

      2  select 244 emp_id, 'Nick B'  name from dual

      3  ),

      4  emp_det as (

      5  select 244 emp_id, to_date('01-jan-2011', 'dd-mon-yyyy') from_date,

      6  to_date('31-dec-2099','dd-mon-yyyy') to_date,'P' job_ind from dual

      7  ),

      8  emp_asgn as (

      9  select null emp_id, null asgn_type, null start_date, null end_date from dual

    10  )

    11  select er.name

    12        ,er.emp_id

    13        ,ed.from_date

    14        ,ed.to_date

    15        ,ed.job_ind

    16    FROM emp_ref er

    17  INNER JOIN emp_det ed

    18      ON (ed.emp_id = er.emp_id)

    19    LEFT OUTER JOIN (SELECT emp_id

    20                           ,asgn_type

    21                       FROM emp_asgn

    22                      WHERE emp_id = 244

    23                        AND asgn_type IN ('IAR','IAB')

    24                        AND TRUNC(SYSDATE,'YEAR') BETWEEN start_date AND end_date) ea

    25      ON (ea.emp_id = ed.emp_id)

    26  WHERE er.emp_id = 244

    27     AND ed.job_ind = CASE

    28           WHEN ea.emp_id IS NULL THEN 'P'

    29           ELSE 'S'

    30         END

    31*    AND TRUNC(SYSDATE,'YEAR') BETWEEN ed.from_date AND ed.to_date

    SQL> /

     

    NAME       EMP_ID FROM_DATE TO_DATE   J

    ------ ---------- --------- --------- -

    Nick B        244 01-JAN-11 31-DEC-99 P

  • 5. Re: This looks like an Oracle bug to me
    kendenny Expert
    Currently Being Moderated

    emplid is emp_id. I was changing all the table and column names but missed one.

  • 6. Re: This looks like an Oracle bug to me
    kendenny Expert
    Currently Being Moderated

    Like we like to say where I work. PEBKAC (Problem exists between keyboard and chair).


  • 7. Re: This looks like an Oracle bug to me
    AlbertoFaenza Expert
    Currently Being Moderated

    Hi Kendenny,

     

    everything would be easier if you could post sample data (including emp_asgn) as CREATE TABLE and INSERT statements as described in the FAQ:
    Re: 2. How do I ask a question on the forums?

     

    Without that and your expected output in one case or the other we can only make a guess.

     

    Here is some example:

    -- using with to create sample data

    with emp_ref as

    (

       select 244 emp_id, 'Nick B' name from dual union all

       select 245 emp_id, 'John S' name from dual

    )

    -- both employees 244 and 245 has 'P' and 'S' job_ind

    , emp_det as

    (

       select 244 emp_id

            , to_date('01-Jan-2011', 'DD-Mon-YYYY') from_date

            , to_date('31-Dec-2099', 'DD-Mon-YYYY') to_date

            , 'P' job_ind

        from dual union all

       select 244 emp_id

            , to_date('01-Jan-2011', 'DD-Mon-YYYY') from_date

            , to_date('31-Dec-2099', 'DD-Mon-YYYY') to_date

            , 'S' job_ind

        from dual union all

       select 245 emp_id

            , to_date('01-Jan-2011', 'DD-Mon-YYYY') from_date

            , to_date('31-Dec-2099', 'DD-Mon-YYYY') to_date

            , 'S' job_ind

         from dual union all

       select 245 emp_id

            , to_date('01-Jan-2011', 'DD-Mon-YYYY') fromdate

            , to_date('31-Dec-2099', 'DD-Mon-YYYY') todate

            , 'P' job_ind

        from dual

    )

    , emp_asgn as

    -- only emp_id 245 exists in emp_asgn

    (

       select 245 emp_id

            , to_date('01-Jan-2012', 'DD-Mon-YYYY') start_date

            , to_date('31-Dec-2015', 'DD-Mon-YYYY') end_date

            , 'IAR' asgn_type

        from dual

    )

    -- end sample data

    select er.name

          ,er.emp_id

          ,ed.from_date

          ,ed.to_date

          ,ed.job_ind

      from emp_ref er inner join emp_det ed

              on (ed.emp_id = er.emp_id)

           left outer join emp_asgn ea

              on (    ea.emp_id = er.emp_id

                  and ea.asgn_type in ('IAR','IAB')

                  and trunc(sysdate,'YY') between start_date and end_date

                  )

    where (  ed.job_ind = 'P' and ea.emp_id is null

           or ed.job_ind = 'S' and ea.emp_id is not null

           )

       and trunc(sysdate,'YEAR') between ed.from_date and ed.to_date

    order by er.emp_id;

     

     

    NAME       EMP_ID FROM_DATE TO_DATE   JOB_IND

    ------ ---------- --------- --------- -------

    Nick B        244 01-JAN-11 31-DEC-99 P     

    John S        245 01-JAN-11 31-DEC-99 S     


    Regards.

    Alberto

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points