OraFAQ Forum: SQL & PL/SQL » changing coulmn name in where caluse depending on value passed

Home » SQL & PL/SQL » SQL & PL/SQL » changing coulmn name in where caluse depending on value passed

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

changing coulmn name in where caluse depending on value passed [message #377774]

Thu, 25 December 2008 01:51

smithagirish
Messages: 10
Registered: March 2008
Location: kochi

Junior Member

hello all

table_a
-------
po_no number
so_no number
amt number

i have a SQL

select * from table_a where so_no(+) = &v_po_so_no

now this will work fine if "&v_po_so_no" contains So_no value

but if i am passing a po_no value to "&v_po_so_no" then
the sql should be like this

select * from table_a where po_no(+) = &v_po_so_no

how can i write a single query for both

thank you

Report message to a moderator

Re: changing coulmn name in where caluse depending on value passed [message #377776 is a reply to message #377774]

Thu, 25 December 2008 02:05

Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How could Oracle know if it is a po_no or so_no?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: changing coulmn name in where caluse depending on value passed [message #377788 is a reply to message #377774]

Thu, 25 December 2008 02:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you don't care which column is to be used, perhaps an OR operator might help:

select * 
from table_a 
where so_no = &v_po_so_no
   or po_no = &v_po_so_no

Report message to a moderator

Re: changing coulmn name in where caluse depending on value passed [message #377843 is a reply to message #377774]

Thu, 25 December 2008 21:32

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

there are several ways to do this depending upon as Michel says, "how you know what you are passing".

Quote:

1) use DYNAMIC SQL
2) use CARELESS OR as suggested by littlefoot
3) use MANUALLY PARTITIONED VIEWS (same idea as littlefoots, only more sophisticated and not so careless)

Consider what the following means.

create or replace view v_split_query
as
select 'PNO' query_name,1 c1 from dual 
union all
select 'SNO',2 from dual
/

select *
from v_split_query
where query_name = 'PNO'
/

There is a lot going on in the above, especially when you consider the fact that you supplied a value that is a constant in the select of one of the queries that makes up the view. This has implications for both query results, and query tuning, a tuning that Oracle will do automagically for you.

Good luck, Kevin

Report message to a moderator

Re: changing coulmn name in where caluse depending on value passed [message #377844 is a reply to message #377843]

Thu, 25 December 2008 21:46

smithagirish
Messages: 10
Registered: March 2008
Location: kochi

Junior Member

thak you very much for your reply

we can know what we are passing with the help of a variable v_po_so

now i got a solution like this

select * from table_a
where decode(&v_po_so,'P',po_no(+),so_no(+)) = &v_po_so_no

Report message to a moderator

Re: changing coulmn name in where caluse depending on value passed [message #377845 is a reply to message #377844]

Thu, 25 December 2008 21:57

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

yes, that will work, but you will find that performance may be very bad because you will not be able to use indexes on po_no or so_no. That is why instead you might wish to consider manually partitioned views (or manually partitioned queries as the case may be). There may also be a simple OR alternative coding that will also work, but I am not sure the or is easy to code given the outerjoins you have. Consider this:

select * from table_a where 'PO_NO' = '&po_so' and PO_NO = &v_po_so_no
union all
select * from table_a where 'SO_NO' = '&po_so' and SO_NO = &v_po_so_no
/

In the above, only one of the two queries will be executed because Oracle will remove the one that does not match your parameter and will do this at the begining of execution (or possibly bind time).

Additionally, if indexes exist on PO_NO and SO_NO, then they might actually be used if the optimizer likes what it sees for stats.

In your decode example, indexes on PO_NO and SO_NO will not be used (unless Oracle Optimizer has gotten real smart in the last release or two).

Good luck, Kevin

Report message to a moderator

Previous Topic:	PLS-00201: identifier 'LIDS.NEXT' must be declared and PLS-00201: identifier 'LIDS.DELETE' must be
Next Topic:	rowid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 01:51:40 CST 2024