CODEWITHSUNDEEP
oracle-databaseoracleforms
user2260896
user2260896

Reputation: 1

Select Query With Variable

I'm working withing Oracle 6i Form the form is search form , it has many text items for criteria search and there is command button when pressed , the form will show all records is user selected some items in creteria text items , the result will be filtered based on those creteria now this is done , and then I want to export the result to a text file using the following code

OUTFILE TEXT_IO.FILE_TYPE;
filename  varchar2(100);
sql_string varchar2(8000);
a number(10);
begin
    a := 0;

  if :norm.Dob_fr is not null and :norm.Dob_to is not null then
sql_string :=  sql_string || ' (DATE_FROM >= to_date(''' || to_char(:norm.Dob_fr,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and date_to <= to_date(''' || to_char(:norm.Dob_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')';
 a := 1;
  end if;


if  :norm.per_fr is not null and :norm.per_to is not null then
    if a = 1 then
        sql_where := ' and ';
    else
        sql_where := ' ( ';
        a := 1;
    end if;
    sql_string :=  sql_string || sql_where || ' pay_date >= to_date(''' || to_char(:norm.per_fr,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and pay_date <= to_date(''' || to_char(:norm.per_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')';

end if; 

       sql_string :=  sql_string  || ' ) ';  
FOR SE IN ('select * from V_BILL_TRAN where ' || nvl(sql_string,'1=1') loop

the export code is working well , but my issue is in the last statement shown above , the where statement "sql_string: it may or not contain a data then the code is not accepted by oracle form 6i there is something wrong

Upvotes: 0

Views: 30

Answers (2)

user2260896
user2260896

Reputation: 1

this is the initial code

OUTFILE TEXT_IO.FILE_TYPE;
filename  varchar2(100);
sql_string varchar2(8000);
a number(10);
begin
    a := 0;

  if :norm.Dob_fr is not null and :norm.Dob_to is not null then
sql_string :=  sql_string || ' (DATE_FROM >= to_date(''' || to_char(:norm.Dob_fr,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and date_to <= to_date(''' || to_char(:norm.Dob_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')';
 a := 1;
  end if;


if  :norm.per_fr is not null and :norm.per_to is not null then
    if a = 1 then
        sql_where := ' and ';
    else
        sql_where := ' ( ';
        a := 1;
    end if;
    sql_string :=  sql_string || sql_where || ' pay_date >= to_date(''' || to_char(:norm.per_fr,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and pay_date <= to_date(''' || to_char(:norm.per_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')';

end if; 

       sql_string :=  sql_string  || ' ) ';  
FOR SE IN ('select * from V_BILL_TRAN where ' || nvl(sql_string,'1=1') loop

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

This is wrong:

sql_string :=  sql_string  || ' ) ';

The closing bracket causes problems because - even if there's nothing in sql_string, after concatenating the bracket, sql_string will become ) and it'll cause problems in the next statement (FOR loop) as nvl(sql_string, 1=1) won't take effect - the result will be

select * from V_BILL_TRAN where )

instead of

select * from V_BILL_TRAN where 1=1

Rewrite it to 

sql_string := sql_string || case when sql_string is not null then ')' end;

Upvotes: 1

Related Questions