Home » SQL & PL/SQL » SQL & PL/SQL » substitution variable with value assigned and without value assigned(2 Merged) (Oracle 10g R2)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #511721 is a reply to message #511708] |
Tue, 14 June 2011 13:33   |
 |
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
|
Senior Member |
|
|
Here is the result for the question
SQL> select empno,ename,job,deptno from emp where empno= &a and deptno= &b;
Enter value for a: 7900
Enter value for b:
old 1: select empno,ename,job,deptno from emp where empno= &a and deptno= &b
new 1: select empno,ename,job,deptno from emp where empno= 7900 and deptno=
select empno,ename,job,deptno from emp where empno= 7900 and deptno=
*
ERROR at line 1:
ORA-00936: missing expression
Regards
Deepak
|
|
|
|
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #511725 is a reply to message #511724] |
Tue, 14 June 2011 13:55 |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
But the answer depends on what you want to do with the condition when no value is given. What does it become? Is this you want the query ignore the condition or is this you want to convert this condition to "the column is null" or is this another thing?
Regards
Michel
[Edit: Quickly answer to the question if you want an answer from me as I'm going to disconnect in a few minutes.]
[Updated on: Tue, 14 June 2011 13:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #511895 is a reply to message #511873] |
Wed, 15 June 2011 17:03 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following will only work from SQL*Plus. Note that you need to have single quotes around the substitution variables in order for them to be recognized as null when no input is provided, then you can use an nvl function to make it so that it results in evaluating column=column so that the condition is eliminated. If you are designing a form in SQL*Plus, then this will work. Otherwise, you need to use whatever sort of variable your front end uses, instead of SQL*Plus substitution variables.
SCOTT@orcl_11gR2> select empno, ename, job, deptno
2 from emp
3 where empno = nvl ('&a', empno)
4 and ename = nvl ('&b', ename)
5 and job = nvl ('&c', job)
6 and deptno = nvl ('&d', deptno)
7 /
Enter value for a: 7369
old 3: where empno = nvl ('&a', empno)
new 3: where empno = nvl ('7369', empno)
Enter value for b: SMITH
old 4: and ename = nvl ('&b', ename)
new 4: and ename = nvl ('SMITH', ename)
Enter value for c:
old 5: and job = nvl ('&c', job)
new 5: and job = nvl ('', job)
Enter value for d:
old 6: and deptno = nvl ('&d', deptno)
new 6: and deptno = nvl ('', deptno)
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #511992 is a reply to message #511895] |
Thu, 16 June 2011 08:03 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Wed, 15 June 2011 18:03The following will only work from SQL*Plus. Note that you need to have single quotes around the substitution variables in order for them to be recognized as null when no input is provided, then you can use an nvl function to make it so that it results in evaluating column=column so that the condition is eliminated. If you are designing a form in SQL*Plus, then this will work. Otherwise, you need to use whatever sort of variable your front end uses, instead of SQL*Plus substitution variables.
SCOTT@orcl_11gR2> select empno, ename, job, deptno
2 from emp
3 where empno = nvl ('&a', empno)
4 and ename = nvl ('&b', ename)
5 and job = nvl ('&c', job)
6 and deptno = nvl ('&d', deptno)
7 /
Enter value for a: 7369
old 3: where empno = nvl ('&a', empno)
new 3: where empno = nvl ('7369', empno)
Enter value for b: SMITH
old 4: and ename = nvl ('&b', ename)
new 4: and ename = nvl ('SMITH', ename)
Enter value for c:
old 5: and job = nvl ('&c', job)
new 5: and job = nvl ('', job)
Enter value for d:
old 6: and deptno = nvl ('&d', deptno)
new 6: and deptno = nvl ('', deptno)
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
1 row selected.
SCOTT@orcl_11gR2>
The only problem with this is if any of the columns is NULL, that row will not match.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO
---------- ---------- ---------- ----------
7369 SMITH CLERK
SQL> get afiedt.buf
1 select empno, ename, job, deptno
2 from emp
3 where empno = nvl ('&a', empno)
4 and ename = nvl ('&b', ename)
5 and job = nvl ('&c', job)
6* and deptno = nvl ('&d', deptno)
SQL>/
Enter value for a: 7369
old 3: where empno = nvl ('&a', empno)
new 3: where empno = nvl ('7369', empno)
Enter value for b:
SMITHold 4: and ename = nvl ('&b', ename)
new 4: and ename = nvl ('', ename)
Enter value for c:
old 5: and job = nvl ('&c', job)
new 5: and job = nvl ('SMITH', job)
Enter value for d:
old 6: and deptno = nvl ('&d', deptno)
new 6: and deptno = nvl ('', deptno)
no rows selected
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #511999 is a reply to message #511997] |
Thu, 16 June 2011 08:18 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 16 June 2011 09:14Quote:The only problem with this is if any of the columns is NULL, that row will not match.
The ONLY one?
The MAIN one is that it is a SQL*Plus (or a SQL tool) solution but we still don't know what OP uses.
Yeah, not the ONLY one. It's just a figure of speech. I'm good in math, not in english.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #512080 is a reply to message #512069] |
Thu, 16 June 2011 18:28 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If there is a value for the parameter, then there is no problem without the quotes, but if there is no value, then it produces an error without the quotes, as shown below. This is the problem that I was trying to work around by enclosing the substitution variable within quotes.
SCOTT@orcl_11gR2> accept d prompt 'enter value for d: '
enter value for d: 10
SCOTT@orcl_11gR2> select empno, ename, job, deptno
2 from emp
3 where (deptno = &d or &d is null)
4 /
old 3: where (deptno = &d or &d is null)
new 3: where (deptno = 10 or 10 is null)
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7782 CLARK MANAGER 10
7839 KING PRESIDENT 10
7934 MILLER CLERK 10
3 rows selected.
SCOTT@orcl_11gR2> accept d prompt 'enter value for d: '
enter value for d:
SCOTT@orcl_11gR2> select empno, ename, job, deptno
2 from emp
3 where (deptno = &d or &d is null)
4 /
old 3: where (deptno = &d or &d is null)
new 3: where (deptno = or is null)
where (deptno = or is null)
*
ERROR at line 3:
ORA-00936: missing expression
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #512264 is a reply to message #512069] |
Fri, 17 June 2011 13:41 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 16 June 2011 16:09in the addition to the invalidation of index usage, the conversion is on the column not on the constant value
Really??? Comparing number to string causes string conversion to number, not other way around, so empno = '&a' will use index:
SQL> set autotrace traceonly
SQL> select empno
2 from emp
3 where empno = '&a'
4 /
Enter value for a: 1
old 3: where empno = '&a'
new 3: where empno = '1'
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 56244932
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=1)
SQL>
And give optimizer some credit:
1 - access("EMPNO"=1)
SY.
[Updated on: Fri, 17 June 2011 13:42] Report message to a moderator |
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #512277 is a reply to message #512268] |
Fri, 17 June 2011 15:14 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 17 June 2011 15:24Funny, isn't it?
SQL*Plus is not a neutral tool to make this kind of test, it is smarter than we think.
This is why you must never rely on implicit behaviour.
Not sure what you mean. It has nothing to to with SQL*Plus or any tool for that matter. As I said, give optimizer some credit. Go to Toad, SQL*Navigator, SQL*Delevloper, etc. and issue:
explain plan for
select empno from emp where empno = '123';
select * from table(dbms_xplan.display);
and
explain plan for
select empno from emp where empno = 'abc';
select * from table(dbms_xplan.display);
This is SQL*Navigator output for 123:
1 Plan hash value: 56244932
2
3 ----------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
7 |* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
8 ----------------------------------------------------------------------------
9
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12
13 1 - access("EMPNO"=123)
And for abc:
1 Plan hash value: 56244932
2
3 ----------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
7 |* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
8 ----------------------------------------------------------------------------
9
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12
13 1 - access("EMPNO"=TO_NUMBER('abc'))
As you can see, it is optimizer, not tool who is making decision.
SY.
|
|
|
|
|
|
| Re: substitution variable with value assigned and without value assigned [message #512344 is a reply to message #512252] |
Sun, 19 June 2011 09:10 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Barbara Boehmer wrote on Fri, 17 June 2011 19:24Michel Cadot wrote on Fri, 17 June 2011 10:20
... there is a far better way to do the wanted feature in SQL*Plus.
Care to share?
The answer is in one of my previous post:
Quote:you build the query on the fly depending on the parameter given by the user
undef a
undef b
undef c
undef d
accept a number prompt "Empno? "
accept b char format a10 prompt "Name? "
accept c char format a9 prompt "Job? "
accept d number prompt "deptno? "
set termout off
col a new_value ca
col b new_value cb
col c new_value cc
col d new_value cd
select nvl2 (nullif(&a, 0), ' and empno = &a', '') a,
nvl2 ('&b', ' and ename = ''&b''', '') b,
nvl2 ('&c', ' and job = ''&c''', '') c,
nvl2 (nullif(&d, 0), ' and deptno = &d', '') d
from dual
/
set termout on
select empno, ename, job, deptno
from emp
where 1 = 1 &ca &cb &cc &cd
/
SQL> @c:\t
Empno? 7369
Name?
Job?
deptno?
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
1 row selected.
SQL> l
1 select empno, ename, job, deptno
2 from emp
3* where 1 = 1 &ca &cb &cc &cd
SQL> def
DEFINE _DATE = "19/06/2011 16:06:22" (CHAR)
DEFINE _USER = "MICHEL" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE A = 7369 (NUMBER)
DEFINE B = "" (CHAR)
DEFINE C = "" (CHAR)
DEFINE D = 0 (NUMBER)
DEFINE CA = " and empno = 7369" (CHAR)
DEFINE CB = "" (CHAR)
DEFINE CC = "" (CHAR)
DEFINE CD = "" (CHAR)
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Wed Aug 20 07:23:12 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
