oracle sql - missing dates from range

Question

I've created the following script ...

SELECT
  gr.RESERVATION_NO,
  gr.TITLE,
  gr.CATNR,
  gl.DUEDATE,
  gr.CRE_USR,
  gl.QTY,
  gl.WORK_CENTER_NO,
  gl.TEC_CRITERIA,
  gr.RESERVE_QTY,
  gl.PLANT,
  studate.dt
FROM GPS_RESERVATION gr, 
    (Select first_date + Level-1 dt 
    From
    (
        Select trunc(sysdate) first_date,
        trunc(sysdate)+60 last_date
        from dual
    )
        Connect By Level <= (last_date - first_date) +1 ) studate
  INNER JOIN GPS_RESERVATION_LOAD gl
          ON gl.work_center_no = 'ALIN'
         AND gl.duedate = studate.dt
         AND gl.plant = 'W'
WHERE gr.RESERVATION_NO = gl.RESERVATION_NO
  AND gr.ACTIVE_FLAG  =  'Y'
  AND gr.reservation_no = '176601'
ORDER BY 
  gl.DUEDATE

I expected to see ALL DATES from sysdate to sysdate+60 but, I only get dates where duedate exists.

i.e.

I get...

enter image description here

I expected...

enter image description here

What am I doing wrong please ?

Thanks for your help.

You're using an inner join instead of a left outer join. You're also mixing join styles, which makes it harder to follow and debug; why not use a proper join between gr and studate? — Alex Poole
– Alex Poole, Commented Oct 28, 2014 at 17:40
Hi, I used a left outer join and got the same result. There is no join between 'gr' and 'studate', as 'studate' joins only to 'gr.duedate' ? Apologies ... I'm fairly new to SQL. — SMORF
– SMORF, Commented Oct 28, 2014 at 17:52
You're doing a cross join, but using the older syntax, not the explicit ANSI cross join syntax. The mix may be affecting the join order. But even if you make the inner join an outer join, you're implicitly making it inner again with WHERE gr.RESERVATION_NO = gl.RESERVATION_NO. That needs to be part of the outer join's on clause. — Alex Poole
– Alex Poole, Commented Oct 28, 2014 at 17:56

Alex Poole · Accepted Answer · 2014-10-28 18:05:01Z

You're mixing older Oracle join syntax with newer ANSI join syntax which is a bit confusing, and might trip up the optimiser; but the main problem is that you have an inner join between your generated date list and your gl table; and you then also have a join condition in the where clause which keeps it as an inner join even if you change the join type.

Without the table structures or any data, I think you want:

...
FROM (
    Select first_date + Level-1 dt 
    From
    (
        Select trunc(sysdate) first_date,
        trunc(sysdate)+60 last_date
        from dual
    )
    Connect By Level <= (last_date - first_date) +1
  ) studate
  CROSS JOIN GPS_RESERVATION gr
  LEFT OUTER JOIN GPS_RESERVATION_LOAD gl
          ON gl.work_center_no = 'ALIN'
         AND gl.duedate = studate.dt
         AND gl.plant = 'W'
         AND gl.RESERVATION_NO = gr.RESERVATION_NO
WHERE gr.ACTIVE_FLAG = 'Y'
  AND gr.reservation_no = '176601'
ORDER BY 
  gl.DUEDATE

The cross-join gives you the cartesian product of the generated dates and the matching records in gr; so if your gr filter finds 5 rows, you'll have 300 results from that join. The left outer join then looks for any matching rows in gl, with all the filter/join conditions related to gl within that on clause.

You should look at the execution plans for your query and this one, firstly to see the difference, but more importantly to check it is joining and filtering as you expect and in a sensible and cost-effective way. And check the results are correct, of course... You might also want to look at a version that uses a left outer join but keeps your original where clause, and see that that makes it go back to an inner join.

Hi Alex ... I have worked through your script. It makes perfect sense, thanks very much for the explanation.

Collectives™ on Stack Overflow

oracle sql - missing dates from range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related