dynamic where clause? urgent

[hemanr]

Gurus,

I need to generate a report where the user chooses parameters like date greater than a date field and price greater that a price field.

I mean, I pass date_field and price_field as in parameters.
Based on the conditions
date_field_parm > date_field
price_field_parm > price_field

I need to retrieve data from the table...

Could anyone please paste some sample code for this?
I think I need to use dynamic sql.

Ive been trying it but failing miserably....

Thanks!!!

[balajiyes]

declare
lv_table_name varchar2(100) := 'EMP';
lv_col_name varchar2(100) := 'SAL';
lv_col_value number := 100;
lv_get_count_record number;
begin
execute immediate 'select count(*) from ' || lv_table_name || ' WHERE ' || lv_col_name || ' = ' || lv_col_value INTO lv_get_count_record;
dbms_output.put_line(lv_get_count_record);
end;

hope this help You
Cheers!

[hemanr]

Hi Balaji,

Thanks.

I know the name of the table and dont need to pass that dynamically.

I had one more question.

What if I have more than one condition?

I mean, if the date > something I select a particular result set.

And if it is less than that date then I select another result set.

I mean
condition1: if date_field > date_field_parm
condition2: id date_field < date_field_parm

how would i include such conditions here?

Thanks!!!

[balajiyes]

is this wht u want?

declare
lv_table_name varchar2(100) := 'EMP';
lv_select_col_name varchar2(100);
lv_col_name varchar2(100) := 'HIREDATE';
lv_col_in_value date := '19-APR-1987';
lv_col_value date;
lv_get_ename varchar2(100);
lv_get_comm_sal number;
begin
if lv_col_in_value > '19-APR-1987' THEN
lv_col_value := '19-APR-1987';
lv_select_col_name := 'ename, sal ';
else
lv_col_value := lv_col_in_value;
lv_select_col_name := 'ename, comm ';
end if;
execute immediate
'select ' || lv_select_col_name || ' from ' || lv_table_name || ' WHERE ' || lv_col_name || ' = ' || CHR(39) || lv_col_value || CHR(39)
into lv_get_ename, lv_get_comm_sal;
dbms_output.put_line(lv_get_ename || lv_get_comm_sal);
end;

Cheers!

[pgoenka]

how about using a simple decode !!! wont it simplyfy the things!!!

[hemanr]

Hi Balaji!

Yes this is very close!

But here...

if lv_col_in_value > '19-APR-1987' THEN


I want to pass the date as a parameter to the procedure from outside....

How would I do that?

Thanks!!!

[hemanr]

Also,

The query results of the SELECT need to be passed as OUT parameters...


Thanks!!!

[balajiyes]

***

create or replace procedure test_me_proc (lv_col_in_value date, lv_col_in_value2 date, p_get_ename out varchar2, p_get_comm_sal out number) as
lv_table_name varchar2(100) := 'EMP';
lv_select_col_name varchar2(100);
lv_col_name varchar2(100) := 'HIREDATE';
--lv_col_in_value date := '20-APR-1987';
--lv_col_in_value2 date := '19-APR-1987';
lv_col_value date;
lv_get_ename varchar2(100);
lv_get_comm_sal number;
begin
if lv_col_in_value > lv_col_in_value2 THEN
lv_col_value := '19-APR-1987';
lv_select_col_name := 'ename, sal ';
else
lv_col_value := lv_col_in_value;
lv_select_col_name := 'ename, comm ';
end if;
execute immediate 'select ' || lv_select_col_name || ' from ' || lv_table_name || ' WHERE ' || lv_col_name || ' = ' || CHR(39) || lv_col_value || CHR(39)
into lv_get_ename, lv_get_comm_sal;
p_get_ename := lv_get_ename;
p_get_comm_sal := lv_get_comm_sal;
--dbms_output.put_line(p_get_ename || p_get_comm_sal);
end test_me_proc;

***

declare
p_get_ename varchar2(100);
p_get_comm_sal number;
begin
test_me_proc ('20-APR-1987', '19-APR-1987', p_get_ename, p_get_comm_sal);
dbms_output.put_line(p_get_ename || p_get_comm_sal);
end;

***

i dono how u can do this in decode

Cheers!

[hemanr]

Hi Balaji,

This is also very close.

My situation is that I need to pass two parameters to the procedure and get out the entire result set as output.

For example, if some one passes the date as > date_parameter then I need to pass out all the records occurring beyond that date which comes in as the parameter.

The procedure will not know the date except at run time.

Thanks a great deal man you are a great help!