Re: conditional selection of 'AND' or 'Select' in Oracle

On 17 Apr 2004 17:46:55 -0700, rohit_reborn_at_yahoo.com (Rohit Dhawan) wrote:

>I have one query similar to the following:-
>
>select coursenum,sectionnum,instructor from
>SECTION s where (sectionnum = '001')
>and coursenum LIKE 'MATH%'
>
>
>but is it possible to add another 'and' condition to the above query
>provided a certain user parameter = 'add'
>
>for e.g
>
>select coursenum,sectionnum,instructor from
>SECTION s where (sectionnum = '001')
>and coursenum LIKE 'MATH%'
>and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
>the above (first)query gets executed.
>
>I do not want to use procedure for the above as I think everyting can
>be done using and,or,not

With Reports 6i, you can create a Formula Column, that formula column will return the additional condition.

Ex: This is the report's query:

select coursenum,sectionnum,instructor from SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'

and if you have a parameter called "and" with something on it, say, "Y", you code a formula called "Add_And" like this:

begin
  if :AND = 'Y' then
    Return ' and instructor LIKE '''L%'''' ; --> you should count the quot. marks  else
  return null ;
end if ;
end ;

Ok, now you modify the report's query:

select coursenum,sectionnum,instructor from SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
&Add_And

If Add_And return null, nothing happens. If Add_And returns something, it will be appended to the query before reports execute it.

I'm using this technique on almost report I write... Works if you want a custom order_by too. I don't think you can do it with only or/and/not. This way is much easier...

Good Luck.

-- 
Tiago Rocha
Recife - Brasil
www.diariodastrilhas.cjb.net