 | |
can I create Date Variable?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: can I create Date Variable? -
09-05-2008
, 12:46 PM
|
On Sep 4, 11:05*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote: On Sep 4, 9:01*am, AP <adamwphoe... (AT) gmail (DOT) com> wrote: I have a union query that combines the results of several queries. Each of these queries has a condition to extract the row where the date is between 1/1/08 and 1/31/08. Each month when I run this I have to do a find and replace to change the date range. Is there a way to declare a date variable and reference that in the select statement instead of the literal date? For example instead of between 1/1/08 and 1/31/08 it would be something like... startDate = 1/1/08 endDate = 1/31/08 statement = between startdate and enddate Thanks in advance Why are you relying upon default formats for your date strings? *This is the surest way to write code that can, and will, fail should that default be changed. Now, to answer your question, yes, you can declare variables, use them, and reassign values to them: SQL> variable startdt varchar2(8) SQL> variable enddt varchar2(8) SQL SQL> exec :startdt := '01/01/82'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/82'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7934 MILLER * * CLERK * * * * * 7782 23-JAN-82 1300 * * * * * * * * * *10 SQL SQL> exec :startdt := '02/01/81'; PL/SQL procedure successfully completed. SQL> exec :enddt := '02/28/81'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7499 ALLEN * * *SALESMAN * * * *7698 20-FEB-81 * * * 1600 300 * * * * 30 * * * 7521 WARD * * * SALESMAN * * * *7698 22-FEB-81 * * * 1250 500 * * * * 30 SQL SQL> exec :startdt := '01/01/83'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/83'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7876 ADAMS * * *CLERK * * * * * 7788 12-JAN-83 1100 * * * * * * * * * *20 SQL David Fitzjarrell- Hide quoted text - - Show quoted text - If you are running the query from SQLPLus you might just be able to use a SQLPlus substitution variable. select .... from ... where date_col >= to_date('&the_date','YYYMMDD') You will be prompted for the value of &the_date by SQLPlus. *Use && if you need to use the variable in the query several times See the SQLPlus manual and look up the ACCEPT, PROMPT, and UNDEFINE commands as well as look for substitiution (label) variables to provide more control over the variable input and reuse. HTH -- Mark D Powell --- Hide quoted text - - Show quoted text - |
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '02/01/81'
Enter value for enddt: '02/28/81'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('02/01/81', 'MM/DD/RR') and
to_date('02/28/81', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
SQL>
Note the single & maintains the value for the duration of the query.
Using && allows you to pass the value through the script to several
queries:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/83'
Enter value for enddt: '01/31/83'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
which can 'backfire' if you're not careful (like it did above). To
fix that it's necessary to undefine the variables:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
Once undefined the variables are ready for new assignments. You can
do this with any name you like (even numerics) but it's difficult for
others to know what, say, &&1 mighr be used to do:
Enter value for 1:
Not very descriptive, which is why one should use more definitive
names for such variables (as illustrated).
David Fitzjarrell
#12
| |||
| |||
|
|
On Sep 4, 11:05*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote: On Sep 4, 9:01*am, AP <adamwphoe... (AT) gmail (DOT) com> wrote: I have a union query that combines the results of several queries. Each of these queries has a condition to extract the row where the date is between 1/1/08 and 1/31/08. Each month when I run this I have to do a find and replace to change the date range. Is there a way to declare a date variable and reference that in the select statement instead of the literal date? For example instead of between 1/1/08 and 1/31/08 it would be something like... startDate = 1/1/08 endDate = 1/31/08 statement = between startdate and enddate Thanks in advance Why are you relying upon default formats for your date strings? *This is the surest way to write code that can, and will, fail should that default be changed. Now, to answer your question, yes, you can declare variables, use them, and reassign values to them: SQL> variable startdt varchar2(8) SQL> variable enddt varchar2(8) SQL SQL> exec :startdt := '01/01/82'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/82'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7934 MILLER * * CLERK * * * * * 7782 23-JAN-82 1300 * * * * * * * * * *10 SQL SQL> exec :startdt := '02/01/81'; PL/SQL procedure successfully completed. SQL> exec :enddt := '02/28/81'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7499 ALLEN * * *SALESMAN * * * *7698 20-FEB-81 * * * 1600 300 * * * * 30 * * * 7521 WARD * * * SALESMAN * * * *7698 22-FEB-81 * * * 1250 500 * * * * 30 SQL SQL> exec :startdt := '01/01/83'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/83'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7876 ADAMS * * *CLERK * * * * * 7788 12-JAN-83 1100 * * * * * * * * * *20 SQL David Fitzjarrell- Hide quoted text - - Show quoted text - If you are running the query from SQLPLus you might just be able to use a SQLPlus substitution variable. select .... from ... where date_col >= to_date('&the_date','YYYMMDD') You will be prompted for the value of &the_date by SQLPlus. *Use && if you need to use the variable in the query several times See the SQLPlus manual and look up the ACCEPT, PROMPT, and UNDEFINE commands as well as look for substitiution (label) variables to provide more control over the variable input and reuse. HTH -- Mark D Powell --- Hide quoted text - - Show quoted text - |
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '02/01/81'
Enter value for enddt: '02/28/81'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('02/01/81', 'MM/DD/RR') and
to_date('02/28/81', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
SQL>
Note the single & maintains the value for the duration of the query.
Using && allows you to pass the value through the script to several
queries:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/83'
Enter value for enddt: '01/31/83'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
which can 'backfire' if you're not careful (like it did above). To
fix that it's necessary to undefine the variables:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
Once undefined the variables are ready for new assignments. You can
do this with any name you like (even numerics) but it's difficult for
others to know what, say, &&1 mighr be used to do:
Enter value for 1:
Not very descriptive, which is why one should use more definitive
names for such variables (as illustrated).
David Fitzjarrell
#13
| |||
| |||
|
|
On Sep 4, 11:05*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote: On Sep 4, 9:01*am, AP <adamwphoe... (AT) gmail (DOT) com> wrote: I have a union query that combines the results of several queries. Each of these queries has a condition to extract the row where the date is between 1/1/08 and 1/31/08. Each month when I run this I have to do a find and replace to change the date range. Is there a way to declare a date variable and reference that in the select statement instead of the literal date? For example instead of between 1/1/08 and 1/31/08 it would be something like... startDate = 1/1/08 endDate = 1/31/08 statement = between startdate and enddate Thanks in advance Why are you relying upon default formats for your date strings? *This is the surest way to write code that can, and will, fail should that default be changed. Now, to answer your question, yes, you can declare variables, use them, and reassign values to them: SQL> variable startdt varchar2(8) SQL> variable enddt varchar2(8) SQL SQL> exec :startdt := '01/01/82'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/82'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7934 MILLER * * CLERK * * * * * 7782 23-JAN-82 1300 * * * * * * * * * *10 SQL SQL> exec :startdt := '02/01/81'; PL/SQL procedure successfully completed. SQL> exec :enddt := '02/28/81'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7499 ALLEN * * *SALESMAN * * * *7698 20-FEB-81 * * * 1600 300 * * * * 30 * * * 7521 WARD * * * SALESMAN * * * *7698 22-FEB-81 * * * 1250 500 * * * * 30 SQL SQL> exec :startdt := '01/01/83'; PL/SQL procedure successfully completed. SQL> exec :enddt := '01/31/83'; PL/SQL procedure successfully completed. SQL SQL> select * * 2 *from emp * 3 *where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR') * 4 */ * * *EMPNO ENAME * * *JOB * * * * * * *MGR HIREDATE * * * * SAL COMM * * DEPTNO * * *BONUS ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- * * * 7876 ADAMS * * *CLERK * * * * * 7788 12-JAN-83 1100 * * * * * * * * * *20 SQL David Fitzjarrell- Hide quoted text - - Show quoted text - If you are running the query from SQLPLus you might just be able to use a SQLPlus substitution variable. select .... from ... where date_col >= to_date('&the_date','YYYMMDD') You will be prompted for the value of &the_date by SQLPlus. *Use && if you need to use the variable in the query several times See the SQLPlus manual and look up the ACCEPT, PROMPT, and UNDEFINE commands as well as look for substitiution (label) variables to provide more control over the variable input and reuse. HTH -- Mark D Powell --- Hide quoted text - - Show quoted text - |
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '02/01/81'
Enter value for enddt: '02/28/81'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('02/01/81', 'MM/DD/RR') and
to_date('02/28/81', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
SQL>
Note the single & maintains the value for the duration of the query.
Using && allows you to pass the value through the script to several
queries:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/83'
Enter value for enddt: '01/31/83'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
which can 'backfire' if you're not careful (like it did above). To
fix that it's necessary to undefine the variables:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ----------
---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
Once undefined the variables are ready for new assignments. You can
do this with any name you like (even numerics) but it's difficult for
others to know what, say, &&1 mighr be used to do:
Enter value for 1:
Not very descriptive, which is why one should use more definitive
names for such variables (as illustrated).
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.