Home » SQL & PL/SQL » SQL & PL/SQL » Define/declare dates and use in CTE (SQL Developer 4.1.2)
Define/declare dates and use in CTE [message #645729] Mon, 14 December 2015 04:55 Go to next message
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 Laughing

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #645738 is a reply to message #645729] Mon, 14 December 2015 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Welcome to the forum.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
With these we can 1) better understand what you want 2) test our ideas before posting them.

Re: Define/declare dates and use in CTE [message #645743 is a reply to message #645735] Mon, 14 December 2015 06:35 Go to previous messageGo to next message
To-Dai
Messages: 4
Registered: December 2015
Location: Denmark
Junior Member
Sorry if I am being unclear and if I am not providing you with all the necessary information - my total experience with SQL queries dates back to about 3-4 weeks ago Confused

My problem is this:
I'm not a DBA - "just" an analyst. And every week (or sometimes more often) I need to extract data from several tables joined in a variety of ways. All the extractions/queries are limited to the same time period, which I need to change almost every time I'm pulling the data again. So instead having to change the start date and end date all over the code at maybe 20 different places every time I need to update, I want to just change/set it 1 place.
The code I initially posted was only meant as an short example to show how I wanted to use the two date variables, because I haven't written my full query yet.

So keeping in mind that I have no idea of what the correct syntax looks like, I was thinking that I could start my query with something like:

Set start_date = '2015-05-01'
Set end_date = '2015-10-31'

...and then just refer to those variables across the subsequent SELECT queries.

I hope that makes sense at some point.

Re: Define/declare dates and use in CTE [message #645745 is a reply to message #645743] Mon, 14 December 2015 06:38 Go to previous messageGo to next message
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
Senior Member
So is not the example I gave using bind variables enough to get you started?
Re: Define/declare dates and use in CTE [message #645746 is a reply to message #645743] Mon, 14 December 2015 06:42 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,


In your case you have to create one metadata table for maintaining different dates and need to write pl/sql Procedure for your requirement.

It's good,If you maintain log table also for the same, for your tracking purpose.
Re: Define/declare dates and use in CTE [message #645747 is a reply to message #645743] Mon, 14 December 2015 06:58 Go to previous messageGo to next message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
To-Dai wrote on Mon, 14 December 2015 06:35
Sorry if I am being unclear and if I am not providing you with all the necessary information - my total experience with SQL queries dates back to about 3-4 weeks ago Confused

<snip>
Set start_date = '2015-05-01'
Set end_date = '2015-10-31'

...and then just refer to those variables across the subsequent SELECT queries.

I hope that makes sense at some point.



The very first thing you MUST get clear in your mind is the difference between a DATE and a STRING that looks like a date. Trying to treat strings as if they are DATES is a recipe for disaster. What you show above is an attempt to set a string variable to a string value that only looks like a date.

see: - But I want to store my date as ... for more clarification.
Re: Define/declare dates and use in CTE [message #645748 is a reply to message #645747] Mon, 14 December 2015 07:08 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear To-Dai,

Please read document given by EDsteven.Really it's very good document and I read same document long back.

Thanks EDSteven for giving this much clarity regarding date field.

I hope you should give more clarity regarding ANSI Date Format.

Re: Define/declare dates and use in CTE [message #645749 is a reply to message #645748] Mon, 14 December 2015 07:13 Go to previous messageGo to next message
To-Dai
Messages: 4
Registered: December 2015
Location: Denmark
Junior Member
Thank you all for your very quick responses! I will closer at your inputs and return with some feedback Smile
Re: Define/declare dates and use in CTE [message #645750 is a reply to message #645743] Mon, 14 December 2015 08:24 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

I think you probably are looking for this:

define start_date=20150501
define end_date=20151031

select <something> from some_table
where column1 >= to_date('&start_date','yyyymmdd')
and column2 < to_date('&end_date','yyyymmdd')
Re: Define/declare dates and use in CTE [message #645808 is a reply to message #645750] Tue, 15 December 2015 06:45 Go to previous messageGo to next message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
JNagtzaam wrote on Mon, 14 December 2015 08:24
I think you probably are looking for this:

define start_date=20150501
define end_date=20151031

select <something> from some_table
where column1 >= to_date('&start_date','yyyymmdd')
and column2 < to_date('&end_date','yyyymmdd')


No. That solution is assigning a NUMBER to start_date and end_date. Just like a string that looks like a date is not a DATE, neither is a NUMBER that looks like a date is not a DATE.
Then you compound the problem by feeding the number value of start_date and end_date to the TO_DATE function. TO_DATE takes a STRING as its input, so oracle has to do an implicit conversion of those number to strings before letting TO_DATE do its work.

What you SHOULD have suggested is

define start_date=to_date('20150501','yyyymmdd');
define end_date=to_date('20151031','yyyymmdd');

select <something> from some_table
where column1 >= &start_date
and column2 < &end_date;
Re: Define/declare dates and use in CTE [message #645813 is a reply to message #645808] Tue, 15 December 2015 07:35 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

@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.
Re: Define/declare dates and use in CTE [message #645816 is a reply to message #645808] Tue, 15 December 2015 07:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #645821 is a reply to message #645813] Tue, 15 December 2015 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
I'm pretty sure substitution variables are always CHAR:


Wrong they can also be number:
SQL> col val new_value val
SQL> select 1 val from dual;
       VAL
----------
         1

1 row selected.

SQL> def val
DEFINE VAL             =          1 (NUMBER)

Re: Define/declare dates and use in CTE [message #645827 is a reply to message #645816] Tue, 15 December 2015 08:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 15 December 2015 08:38
Actually 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 #645830 is a reply to message #645821] Tue, 15 December 2015 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
OK I stand corrected. Didn't realize you could create them that way. They still can't be date though.
Re: Define/declare dates and use in CTE [message #645831 is a reply to message #645827] Tue, 15 December 2015 08:36 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Tue, 15 December 2015 14:28
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:


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 #645833 is a reply to message #645830] Tue, 15 December 2015 08:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
Senior Member
Correct.

SY.
Re: Define/declare dates and use in CTE [message #645836 is a reply to message #645831] Tue, 15 December 2015 08:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #645843 is a reply to message #645838] Tue, 15 December 2015 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd say the main stumbling block here is lack of understanding of what you can and can't do with substitution variables rather than misunderstanding dates, which is why I prefer JNagtzaam's approach - it is in line with the standard examples of substitution variables you can find. I can't even find the bit in documents that says you can use " to preserve white space.
I found this https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm#i1211231 and it's got lots of simple examples but nothing about the complex stuff you and Michel used.
Re: Define/declare dates and use in CTE [message #645859 is a reply to message #645843] Tue, 15 December 2015 09:47 Go to previous messageGo to next message
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 #645873 is a reply to message #645859] Tue, 15 December 2015 10:23 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Where exactly are you looking in the docs SY?
Re: Define/declare dates and use in CTE [message #645887 is a reply to message #645873] Tue, 15 December 2015 15:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 15 December 2015 11:23
Where 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.
icon14.gif  Re: Define/declare dates and use in CTE [message #645908 is a reply to message #645887] Wed, 16 December 2015 02:41 Go to previous messageGo to next message
To-Dai
Messages: 4
Registered: December 2015
Location: Denmark
Junior Member
Hey all and thanks a lot you for all your inputs! I must admit I haven't tested all the suggestions but JNagtzaam's solution seems to work like a charm.
In an effort to learn and understand more about this though, I was wondering if anyone could explain to me why the "&" sign is required in:
to_date('&start_date','yyyymmdd')
Re: Define/declare dates and use in CTE [message #645909 is a reply to message #645908] Wed, 16 December 2015 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

http://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG437



Re: Define/declare dates and use in CTE [message #645910 is a reply to message #645908] Wed, 16 December 2015 03:09 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

At the & sign oracle recognizes, that it is a substitution variable you are referring to. This is the default, but you can alter it with the "SET DEFINE" statement.

You can also define bind variables, these have a leading ':'.
Re: Define/declare dates and use in CTE [message #645919 is a reply to message #645813] Wed, 16 December 2015 06:22 Go to previous message
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.

Previous Topic: Help any one with a code please
Next Topic: PL SQL Code with variable
Goto Forum:
  


Current Time: Mon Jul 20 20:11:46 CDT 2026