How to use defined variable in Where clause-CodePudding

Defining and selecting variable works just fine in Oracle SQL Developer.

ALTER SESSION SET NLS_LANGUAGE=english; -- First day of week
--DEFINE SUMMER_START_DT = TO_CHAR(TO_DATE('03-24-2022', 'MM-DD-YYYY'),'yyyymmdd')
DEFINE SUMMER_START_DT = TO_CHAR(NEXT_DAY(LAST_DAY(TO_DATE(TO_CHAR('01/03/' || (EXTRACT(YEAR FROM SYSDATE)-1   level) || '02:00:00'),'DD/MM/YYYY HH24:MI:SS')) - INTERVAL '7' DAY, 'SUNDAY'),'yyyymmdd') FROM DUAL CONNECT BY level <=1 
SELECT &SUMMER_START_DT;

But I get an error when trying to use the variable in Select statement using it as filter in the Where clause.

SELECT a.* FROM TRADE a WHERE TO_CHAR(a.TRADE_DATE_TIME,'yyyymmdd') = &SUMMER_START_DT;

I get the error "SQL command not properly ended"

Hope someone can help me. Thanks

Kind regards

Soren Sig Mikkelsen

CodePudding user response：

You substitution variable includes from dual, which is OK when you just prepend select in your first example; but in the second you end up with two from clauses:

SELECT a.*
FROM TRADE a
WHERE TO_CHAR(a.TRADE_DATE_TIME,'yyyymmdd') =
  TO_CHAR(NEXT_DAY(LAST_DAY(TO_DATE(TO_CHAR('01/03/' || (EXTRACT(YEAR FROM SYSDATE)-1   level) || '02:00:00'),'DD/MM/YYYY HH24:MI:SS')) - INTERVAL '7' DAY, 'SUNDAY'),'yyyymmdd')
FROM DUAL CONNECT BY level <=1

(You can see that in the generated column name/alias in the output grid; or set verify on and run as a script.)

If you really wanted to use that as the right-hand side of the filter then you could enclose it in parentheses:

SELECT a.* FROM TRADE a WHERE TO_CHAR(a.TRADE_DATE_TIME,'yyyymmdd') = (SELECT &SUMMER_START_DT);

which would become:

SELECT a.*
FROM TRADE a
WHERE TO_CHAR(a.TRADE_DATE_TIME,'yyyymmdd') =
  (
    SELECT TO_CHAR(NEXT_DAY(LAST_DAY(TO_DATE(TO_CHAR('01/03/' || (EXTRACT(YEAR FROM SYSDATE)-1   level) || '02:00:00'),'DD/MM/YYYY HH24:MI:SS')) - INTERVAL '7' DAY, 'SUNDAY'),'yyyymmdd')
    FROM DUAL CONNECT BY level <=1
  )

But the connect by isn't doing anything here, so you can remove that; and if you remove from dual as well then you can run your first statement as:

SELECT &SUMMER_START_DT FROM DUAL;

and the second as it is.

You could simplify the calculation though. For a start you aren't using the time element, so you don't need to make it 2am; and you can truncate to the start of the year and add two months to get March 1st; as a string if that's really what you want:

to_char(next_day(last_day(add_months(trunc(sysdate, 'YYYY'), 2)) - 7, 'SUNDAY'), 'YYYYMMDD')

db<>fiddle

But you can keep it as a date; if you:

DEFINE SUMMER_START_DT = next_day(last_day(add_months(trunc(sysdate, 'YYYY'), 2)) - 7, 'SUNDAY')

then again you can do:

SELECT &SUMMER_START_DT FROM DUAL;

and your second query can be:

SELECT a.*
FROM TRADE a
WHERE a.TRADE_DATE_TIME >= &SUMMER_START_DT
AND a.TRADE_DATE_TIME < &SUMMER_START_DT   1

which avoids converting every TRADE_DATE_TIME date value to a string to compare it, and allows an index on that date column to be used.