conditional selection of 'AND'

[Rohit Dhawan]

I have one query similar to the following:-

select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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]

Re: conditional selection of 'AND'

Rohit Dhawan wrote:

I have one query similar to the following:-
>
select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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]

Re: conditional selection of 'AND'

AnaCDent <anacedent@hotm ail.comwrote in message news:<qekgc.550 83$U83.8534@fed 1read03>...

Rohit Dhawan wrote:

I have one query similar to the following:-

select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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(singleColu mn) values (:parameter);
select s.coursenum,s.s ectionnum,s.ins tructor 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,secti onnum,instructo r,:parameter p from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and p='add';
cheers and welcome

[Jan]

Re: conditional selection of 'AND'

Hope this help:

select coursenum,secti onnum,instructo r
FROM SECTION
WHERE (sectionnum = '001')
AND coursenum LIKE 'MATH%'
AND (CASE WHEN &my_param='a dd' THEN coursenum ELSE '1' END)
LIKE (CASE WHEN &my_param='a dd' THEN 'L%' ELSE '1' END)


Jan



rohit_reborn@ya hoo.com (Rohit Dhawan) wrote in message news:<c76d1c78. 0404171637.1365 adf0@posting.go ogle.com>...

I have one query similar to the following:-
>
select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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]

Re: conditional selection of 'AND'


"niranjan v sathe" <satheniranjan@ yahoo.comwrote in message
news:c3fae289.0 404182134.efbf6 ae@posting.goog le.com...
| AnaCDent <anacedent@hotm ail.comwrote in message
news:<qekgc.550 83$U83.8534@fed 1read03>...

....
|
| 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(singleColu mn) values (:parameter);
| select s.coursenum,s.s ectionnum,s.ins tructor 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,secti onnum,instructo r,: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,secti onnum,instructo r,: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.o racle.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]

Re: conditional selection of 'AND'

rohit_reborn@ya hoo.com (Rohit Dhawan) wrote in message news:<c76d1c78. 0404171637.1365 adf0@posting.go ogle.com>...

I have one query similar to the following:-
>
select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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,secti onnum,instructo r from
SECTION s where (sectionnum = '001')
and coursenum LIKE 'MATH%'
and ( ( instructor LIKE 'L%'
and :parameter = 'add' )
OR
( :parameter != 'add' )
)

[/motten]

Re: conditional selection of 'AND'


"Rohit Dhawan" <rohit_reborn@y ahoo.comwrote in message
news:c76d1c78.0 404171637.1365a df0@posting.goo gle.com...

I have one query similar to the following:-
>
select coursenum,secti onnum,instructo r 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,secti onnum,instructo r 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