CODEWITHSUNDEEP
oracle-databaseoraclereports
gehirn seele
gehirn seele

Reputation: 1

oracle report lexical parameter

I am using oracle report and have problem with "SELECT ALL" here is my query

SELECT * FROM company A, seller B
WHERE a.id = b.id 
&(P)Company_id

and in my after parameter in oracle report i use 

begin 
    if (:(V)Company_id is not null and :(V)Company_id<> '0')
       :(P)Company_id:= ' and a.id ='||:(V)Company_id;
    end if;
    return (true)`
end;

if the id is all digit like 000123 works fine, but if id like ([L]00123) the result is show all data. need help with my lexical parameter.

Upvotes: 0

Views: 1900

Answers (2)

Littlefoot
Littlefoot

Reputation: 142710

Information you post is misleading. I've been using Oracle Reports for ages, and I've never seen syntax you use. Code you wrote doesn't even compile; how would it work, then (which is what you claim)? There's no (V)something syntax at all.

Anyway, from my point of view, you don't need a lexical parameter but a simple OR condition, e.g.

select *
from company a join seller b on b.id = a.id
where (a.id = :par_company_id or :par_company_id is null)
  • the first part of it, a.id = :par_company_id will return rows whose ID is equal to value you enter in the parameter form
  • the second part, or :par_company_id is null will return all rows if you leave the parameter value empty

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

I would have thought you'd get an error from that rather than all data, but maybe Reports does something weird in that scenario. Anyway, it looks like you just need to enclose the passed-in value in single quotes, which you will need to escape; so instead of this line:

   :(P)Company_id:= ' and a.id ='||:(V)Company_id;

use:

   :(P)Company_id:= ' and a.id = ''' || :(V)Company_id || '''';

although it would be better if you could keep it as a bind variable. I'm not familiar with Reports but something like this might work:

select *
from company A
join seller B
on a.id = b.id
where &(V)Company_id is null or a.id = &(V)Company_id

(I've switched to ANSI join syntax as well...)

Upvotes: 0

Related Questions