| Define/declare dates and use in CTE [message #645729] |
Mon, 14 December 2015 04:55  |
 |
To-Dai
Messages: 4 Registered: December 2015 Location: Denmark
|
Junior Member |
|
|
Hello all,
I can't seem to find a working solution to my problem, so now I'm trying my luck here.
Basically I want to set/define a start_date and an end_date which I can then use with a CTE query like this:
WITH OPL AS (
Select *
FROM TBL_OPLAG
WHERE NR = '11'
AND OPL_DATE BETWEEN start_date AND end_date
,REK AS (
SELECT *
FROM REK
WHERE NR = '12'
AND REK_DATE BETWEEN start_date AND end_date
SELECT *
FROM OPL
JOIN REK ON OPL.OPL_DATE = REK.REK_DATE
So my question is, how can I make this work?
I'm a novice at (PL)SQL so "complete code answers" are very welcome
Thanks in advance!
[Updated on: Mon, 14 December 2015 04:57] Report message to a moderator
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645734 is a reply to message #645729] |
Mon, 14 December 2015 05:12   |
John Watson
Messages: 9003 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Do you mean something like this -
orclz>
orclz> var v1 number
orclz> var v2 number
orclz> exec :v1:=2000
PL/SQL procedure successfully completed.
orclz> exec :v2:=4000
PL/SQL procedure successfully completed.
orclz> select * from emp where sal between :v1 and :v2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02:00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01:00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19:00:00:00 3000 20
7902 FORD ANALYST 7566 1981-12-03:00:00:00 3000 20
orclz>
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645735 is a reply to message #645729] |
Mon, 14 December 2015 05:13   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It's not at all clear what the problem is.
Are the start/end dates user supplied / stored in a table / got from somewhere else?
What is preventing you from using the start/end date you have?
All you've posted is what appears to be a syntactically correct query.
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645736 is a reply to message #645729] |
Mon, 14 December 2015 05:35   |
 |
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Dear,
I have done following Activities for Resolving issue at your end:
CREATE TABLE DEV_SAURABH.TEST_T
(
NR VARCHAR2(20 CHAR),
OPL_DATE DATE,
END_DATE DATE
);
CREATE TABLE DEV_SAURABH.TEST_K
(
NR VARCHAR2(20 CHAR),
OPL_DATE DATE,
REK_DATE DATE
);
INSERT INTO test_t
VALUES(
'11'
,trunc(sysdate)
,trunc(sysdate)+2
);
INSERT INTO test_t
VALUES( '12'
,trunc(sysdate)
,trunc(sysdate)+4
);
INSERT INTO test_k
VALUES( '11'
,trunc(sysdate)+1
,trunc(sysdate)
);
INSERT INTO test_t
VALUES( '12'
,trunc(sysdate)+3
,trunc(sysdate)
);
Final Query:
-----------
WITH OPL AS (
Select *
FROM test_t
WHERE NR = '11'
AND OPL_DATE BETWEEN trunc(sysdate) AND trunc(sysdate) + 1
)
,REK AS (
SELECT *
FROM test_k
WHERE NR = '12'
AND REK_DATE BETWEEN trunc(sysdate) AND trunc(sysdate) + 4
)
SELECT *
FROM OPL
JOIN REK ON OPL.OPL_DATE = REK.REK_DATE
Please provide always Create table and Insert table scripts.You have to resolve your query using similar logic.
[Updated on: Mon, 14 December 2015 05:36] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645816 is a reply to message #645808] |
Tue, 15 December 2015 07:38   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Actually his works and yours doesn't. Define variables in sqlplus are always strings. And the normal rules about quotes don't apply, it actually just takes everything after the equals:
SQL> define start_date=to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS');
SQL> select &start_date from dual;
old 1: select &start_date from dual
new 1: select to_date('20150501 from dual
ERROR:
ORA-01756: quoted string not properly terminated
SQL> define start_date=23
SQL> select &start_date from dual;
old 1: select &start_date from dual
new 1: select 23 from dual
23
----------
23
SQL>
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645820 is a reply to message #645816] |
Tue, 15 December 2015 07:56   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
There are also sqlplus variables (distinct from define variables) but you can't set them to date either:
SQL> var x date
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645827 is a reply to message #645816] |
Tue, 15 December 2015 08:28   |
Solomon Yakobson
Messages: 3312 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Tue, 15 December 2015 08:38Actually his works and yours doesn't.
You are piling up different issues. Your example has nothing to do with dates. It is about defining SQL*PLus substitution variable rules. If value has whitespaces it must be enclosed in double quotes:
SQL> define start_date=to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS');
SQL> define start_date
DEFINE START_DATE = "to_date('20150501" (CHAR)
SQL> define start_date="to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS')";
SQL> define start_date
DEFINE START_DATE = "to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS')" (CHAR)
SQL>
SY.
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645831 is a reply to message #645827] |
Tue, 15 December 2015 08:36   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 15 December 2015 14:28Your example has nothing to do with dates. It is about defining SQL*PLus substitution variable rules. If value has whitespaces it must be enclosed in double quotes:
Well I was demonstrating that Ed's code as written would just error out. And his idea that the original example was feeding numbers to to_date was in fact wrong.
Your variation does sort the problem out but I'd suggest it isn't actually any better than the original approach - both actually do the correct things with regards to data type conversion.
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645836 is a reply to message #645831] |
Tue, 15 December 2015 08:49   |
Solomon Yakobson
Messages: 3312 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, I agree Ed's example isn't written in generic way but this is up to a person utilizing the code to know how to define SQL*Plus substitution variables correctly. And Ed's approach is "safer", if you will. It makes sure substitution variable is properly used as date everywhere in the code while declaring substitution variable without TO_DATE leaves a chance for that substitution variable to be used as string resulting in implicit conversions and possibly wrong results.
SY.
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645838 is a reply to message #645836] |
Tue, 15 December 2015 08:54   |
Solomon Yakobson
Messages: 3312 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, I take my words about "safer" back. If someone has no understanding of dates he/she will put something like:
define start_date="to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS')";
select to_date(start_date)...
So it is catch 22.
SY.
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645859 is a reply to message #645843] |
Tue, 15 December 2015 09:47   |
Solomon Yakobson
Messages: 3312 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
From SQL*Plus doc: Enclose text in single quotes if it contains punctuation or blanks. So you can follow that, just keep in mind same rule of doubling single quotes applies if substitution variable value contains single quote:
SQL> define start_date='to_date(''20150501 23:23:59'',''yyyymmdd hh24:MI:SS'')'
SQL> define start_date
DEFINE START_DATE = "to_date('20150501 23:23:59','yyyymmdd hh24:MI:SS')" (CHAR)
SQL>
It just simpler to use double quotes when value contains single quotes. And what Michel showed is also documented in DEFINE command:
reference the variable in the NEW_VALUE or OLD_VALUE clause of a COLUMN command and then reference the column in a SELECT command
And there is an example of NEW_VALUE in COLUMN command.
SY.
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645887 is a reply to message #645873] |
Tue, 15 December 2015 15:22   |
Solomon Yakobson
Messages: 3312 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Tue, 15 December 2015 11:23Where exactly are you looking in the docs SY?
SQL*PlusĀ® User's Guide and Reference. DEFINE:
DEFINE
Syntax
DEF[INE] [variable] | [variable = text]
Specifies a user or predefined variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables.
Terms
variable
Represents the user or predefined variable whose value you wish to assign or list.
text
Represents the CHAR value you wish to assign to variable. Enclose text in single quotes if it contains punctuation or blanks.
variable = text
Defines (names) a substitution variable and assigns it a CHAR value.
Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all substitution variables.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Define/declare dates and use in CTE [message #645919 is a reply to message #645813] |
Wed, 16 December 2015 06:22  |
 |
EdStevens
Messages: 1377 Registered: September 2013
|
Senior Member |
|
|
JNagtzaam wrote on Tue, 15 December 2015 07:35@EdStevens: I'm pretty sure substitution variables are always CHAR:
> define start_date=to_date('20150501','yyyymmdd');
> define start_date
DEFINE START_DATE = "to_date('20150501','yyyymmdd')" (CHAR)
So what you are suggesting could give you incorrect results.
You are correct. I wasn't looking at your code as sqlplus-specific (and use of specifically sqlplus substitution variables) but rather as a bit of pseudo-code. And from that view, still not using DATE data types.
|
|
|
|