| Query With 'Like' Option [message #370141] |
Wed, 20 December 2000 10:07  |
Tamjeed
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
My primary key is like A0001, A0002 ... B0001, B0002...
I am having user input of 'A' or 'B' in a VARIABLE. I want to get the MAX value of the series A or B by comparing it with the user input VARIABLE.
If the user entres 'A', in the VARIABLE, it finds the series starting with 'A' '0001'.... and get the maximum value.
If my question is unclear, ask me, I will post in detail.
|
|
|
|
|
|
| Re: Query With 'Like' Option [message #370151 is a reply to message #370141] |
Fri, 22 December 2000 22:09  |
Robert Moy
Messages: 15
Registered: December 2000
|
Junior Member |
|
|
I also solution to that problem:
SQL> run
1 create or replace package find_letter as
2 procedure locate_letter
3 (get_salary out My_id.salary%type,
4 get_letter in My_id.id%type:=&f_letter,
5 get2_letter out My_id.id%type,
6 get_name out My_id.name%type);
7* end find_letter;
old 4: get_letter in My_id.id%type:=&f_letter,
new 4: get_letter in My_id.id%type:='A',
Package created.
SQL> create or replace package body find_letter as
2 procedure locate_letter
3 (get_salary out My_id.salary%type,
4 get_letter in My_id.id%type:=&f_letter,
5 get2_letter out My_id.id%type,
6 get_name out My_id.name%type) as
7 cursor meet_letter is
8 select salary,id,name
9 from My_id
10 where substr(id,1,1)=&f_letter
11 and
12 To_Number(substr(id,2,8)) =
13 (select Max(to_Number(substr(id,2,8)))
14 from My_id);
15 begin
16 open meet_letter;
17 loop
18 fetch
19 meet_letter into get_salary,get2_letter,get_name;
20 exit when meet_letter%NotFound;
21 end loop;
22 exception
23 when no_data_found then
24 dbms_output.put_line('No Data');
25 return;
26 end locate_letter;
27 end find_letter;
28 /
old 4: get_letter in My_id.id%type:=&f_letter,
new 4: get_letter in My_id.id%type:='A',
old 10: where substr(id,1,1)=&f_letter
new 10: where substr(id,1,1)='A'
Package body created.
SQL> run
1 declare
2 Found_salary My_id.salary%type;
3 Found_letter My_id.id%type:=&f_letter;
4 Found2_letter My_id.id%type;
5 Found_name My_id.name%type;
6 begin
7 find_letter.locate_letter(Found_salary,Found_letter,Found2_letter,Found_name);
8 dbms_output.put_line(Found_salary|| ' ' ||Found2_letter|| ' ' ||Found_name);
9* end;
old 3: Found_letter My_id.id%type:=&f_letter;
new 3: Found_letter My_id.id%type:='A';
100 A0003 Frank
PL/SQL procedure successfully completed.
Good Luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
