parameter wild card

[sonny1974]

i want to do a parameter in my sql

where ord_table.project like {?Project}%

i but its not working, so bascially if the person enters the first 5 digits of the project number , it doesnt matter what appears after the 5 numbers on project number

 

[SantaMufasa]

Sonny, I presume that you wish to prompt for your "parameter" from a SQL*Plus prompt, right? Here is some code that will do that. (You must save this code to a script to run it properly...you cannot just run this as a copy and paste, due to the "ACCEPT...PROMPT" code.) I'll name the script "sonny.sql".

Section 1 -- Contents of "ord_table":

select * from ord_table;

  PROJECT NAME
--------- -----
123456789 abc
 23456789 def
123450003 ghi

Section 2 -- Contents of "sonny.sql":

set verify off
accept proj prompt "Enter the first 5 digits of the projects to show: "
select * from ord_table
where ord_table.project like '&proj%'
/

Section 3 -- Invocation and Results of "sonny.sql":

SQL>  @sonny
Enter the first 5 digits of the projects to show: 12345

   PROJECT NAME
---------- --------------------
 123456789 abc
 123450003 ghi

Let us know if this answers your question.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]