CODEWITHSUNDEEP
sqloracle-databaseplsql
jamesk5
jamesk5

Reputation: 136

PLSQL 'IN' Operator

I'm trying to select rows based on a range of 'dates' determined by the following PLSQL query, which currently delivers the results I need - being the 'date' object of the last 10 weeks of the day of the week when the script is run. Eg. running it on the 22th of May would yield, 15th May, 8th May and so on.

SELECT SYSDATE-(level*7) as DateRange
FROM DUAL
CONNECT BY LEVEL <=10

This generates a list of dates. Then I try and combine this with a parent select statement to get rows with the dates outputted by the above that are in the 'DAY' (of Oracle type DATE) column.

SELECT * FROM NEM_RM16
WHERE NEM_RM16.DAY IN (
  SELECT SYSDATE-(level*7) as DateRange
  FROM DUAL
  CONNECT BY LEVEL <=10);

Which gives no results, despite knowing that there are rows that have the dates generated by the above.

I've read that when using the 'IN' operator, values must be enclosed in single quotes, but I'm not sure about how to do this with the query above.

Am I going about this the right way by using the IN operator, or should I be doing a different type of nested query?

Upvotes: 2

Views: 2913

Answers (2)

turbanoff
turbanoff

Reputation: 2479

use trunc for truncate time component from sysdate

SELECT * FROM NEM_RM16
WHERE NEM_RM16.DAY IN (
  SELECT trunc(SYSDATE)-(level*7) as DateRange
  FROM DUAL
  CONNECT BY LEVEL <=10);

Upvotes: 3

Rohan
Rohan

Reputation: 2030

Maybe the format of the date returned by nested query does not match with the date format of the column NEM_RM16.DAY Probably, if the dates are compared after making them of the same format, they will match properly

Like this 


SELECT *
  FROM NEM_RM16
 WHERE TO_DATE(TO_CHAR(NEM_RM16.DAY, 'DD/MM/YYYY'), 'DD/MM/YYYY') IN
       (SELECT TO_DATE(TO_CHAR(SYSDATE - (level * 7), 'DD/MM/YYYY'),
                       'DD/MM/YYYY') as DateRange
          FROM DUAL
        CONNECT BY LEVEL <= 10);

Hope it helps

Upvotes: -1

Related Questions