conditional selection of 'AND'

[Rohit Dhawan]

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 query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

[AnaCDent]

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 query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

Yes, but only PL/SQL & not in SQL*PLUS.
Partly because straight SQL does not support the "IF".

[niranjan v sathe]

AnaCDent <an*******@hotmail.com> wrote in message news:<qekgc.55083$U83.8534@fed1read03>...

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 query gets executed.
I do not want to use procedure for the above as I think everyting can
be done using and,or,not

Yes, but only PL/SQL & not in SQL*PLUS.
Partly because straight SQL does not support the "IF".

Dear Rohit,
Keep in mind one thing : you cannot control execution of query in SQL
prompt instead you have to return null(empty) result. you can achieve this
by using insertion in temp table and join.
e.g.
insert into temp(singleColumn) values (:parameter);
select s.coursenum,s.sectionnum,s.instructor from
SECTION s,temp t where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and t.param = 'add';

if you want a better way try this but I am not sure whether it will work on
oracle.
select coursenum,sectionnum,instructor,:parameter p from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and p='add';
cheers and welcome

[Jan]

Hope this help:

select coursenum,sectionnum,instructor
FROM SECTION
WHERE (sectionnum = '001')
AND coursenum LIKE 'MATH%'
AND (CASE WHEN &my_param='add' THEN coursenum ELSE '1' END)
LIKE (CASE WHEN &my_param='add' THEN 'L%' ELSE '1' END)
Jan

ro**********@yahoo.com (Rohit Dhawan) wrote in message news:<c7**************************@posting.google. com>...

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 query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

[Mark C. Stock]

"niranjan v sathe" <sa***********@yahoo.com> wrote in message
news:c3*************************@posting.google.co m...
| AnaCDent <an*******@hotmail.com> wrote in message
news:<qekgc.55083$U83.8534@fed1read03>...

....
|
| Dear Rohit,
| Keep in mind one thing : you cannot control execution of query in
SQL
| prompt instead you have to return null(empty) result. you can achieve
this
| by using insertion in temp table and join.
| e.g.
| insert into temp(singleColumn) values (:parameter);
| select s.coursenum,s.sectionnum,s.instructor from
| SECTION s,temp t where (sectionnum = '001')
| and coursenum LIKE 'MATH%'
| and t.param = 'add';
|

sorry, not a good approach

the temporary table (which in oracle would need to be a GLOBAL TEMPORARY
TABLE, which is a permanent object with temporary contents) is not necessary

see the other posts that recommend CASE (or decode if you are pre-9i)

| if you want a better way try this but I am not sure whether it will work
on
| oracle.
| select coursenum,sectionnum,instructor,:parameter p from
| SECTION s where (sectionnum = '001')
| and coursenum LIKE 'MATH%'
| and p='add';
| cheers and welcome

this query would only work if the p is set to lower case 'add' -- if it is
anything else, then it will return no rows

it would need to be written like this (also note that the column alias
cannot be referenced in a predicate)

select
coursenum,sectionnum,instructor,:parameter p
from
SECTION s
where
sectionnum = '001'
and
(
(
coursenum LIKE 'MATH%'
and
:parameter ='add'
)
or
:parameter is null
)

but, see my other post in comp.database.oracle.misc on why the 'add'
variable is unnecessary

(also, Rohit, _please_ remember to not cross-post -- this thread is now
living in more than one forum)

;-{ mcs

[The Elemenatlist]

ro**********@yahoo.com (Rohit Dhawan) wrote in message news:<c7**************************@posting.google. com>...

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 query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

You could try something like this:

select coursenum,sectionnum,instructor from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and ( ( instructor LIKE 'L%'
and :parameter = 'add' )
OR
( :parameter != 'add' )
)

[/motten]

"Rohit Dhawan" <ro**********@yahoo.com> wrote in message
news:c7**************************@posting.google.c om...

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 query gets executed.

I do not want to use procedure for the above as I think everyting can
be done using and,or,not

I hope you have a better reason for not doing this in pl/sql than that you
"do not want to use a procedure".
IMO the pure sql solution is doable (using case or decode/nvl) but "ugly"
due to the fact, that some developers
will find it hard to grasp easily and maintain if they take over the project
later.
Personally I'd solve this using a ref-cursor which would
a: give you complete flexibility allowing you to construct your query at
runtime, meaning you'd be able to change
more than just the "and" parts of the query
b: give you much of the functionality inherent in explicit cursors

Bear in mind that I haven't been working with Oracle-stuff for that long and
am still learning new stuff everyday...

Cheers

Morten Olsson