0

The below query should return only 4 rows (24-12-2017 to 27-12-2017). I'm using enterprise Postgres with Oracle compatibility.

But some how the last line of the query is not working. It should be because of the ROWNUM. It's ignoring the ROWNUM condition and displaying the number of records in all_objects of db. Could you please help.

  WITH a
    AS (
    Select
    date(TRUNC(date(sysdate), 'WW')) FROM_DATE,
    date(sysdate)-1 TO_DATE
    From dual)
    select
    date(date(a.FROM_DATE) + rownum -1) from
    all_objects,a
    where rownum  <= date(a.TO_date)+1-date(a.FROM_DATE)+1;

As per comment, adding more information. When I put a random date in the place of sysdate in the above query. the FROM_DATE is supposed to be the start of the week's date. When given 27-12-2017 --> the start date of the week is 24-12-2017(week starts from Monday). So the query should print from 24th - 27th dates. If I give 20-12-2017 --> it should print from 18 to 20th dates. I hope this helps.

user1720827
  • 137
  • 1
  • 3
  • 15
  • 1
    Why don't you use `generate_series()` instead? `select * from generate_series(date '2017-12-24', date '2017-12-27', interval '1' day)` –  Dec 27 '17 at 16:57
  • @a_horse_with_no_name `WITH a AS ( Select date(TRUNC(date(sysdate), 'WW')) FROM_DATE, date(sysdate)-1 TO_DATE From dual) select * from generate_series(date(a.FROM_DATE),date(a.TO_DATE),interval '1 day')` I'm getting `Missing from clause entry for table a`. (my table 'a' changes usually. So I need to append generate series at the end – user1720827 Dec 28 '17 at 02:10
  • That select I wrote is all you need: http://rextester.com/WILVRB34872 –  Dec 28 '17 at 07:06
  • @a_horse_with_no_name its not about printing only from 24th-27th. I just gave an example for yest., what it should do. There can be a date from another table in my "a". I need to use it at the end. `select date(x) from generate_series(date(TRUNC(date(sysdate), 'WW')), date(sysdate),interval '1 day') as x` this also works. But if its referring like my above comment, Im getting error – user1720827 Dec 28 '17 at 08:25
  • Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. –  Dec 28 '17 at 08:27
  • @a_horse_with_no_name I have added some more explanation to that. Hope that helps – user1720827 Jan 02 '18 at 05:08

0 Answers0