Home » SQL & PL/SQL » SQL & PL/SQL » changing coulmn name in where caluse depending on value passed
changing coulmn name in where caluse depending on value passed [message #377774] Thu, 25 December 2008 01:51 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
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 Go to previous messageGo to next message
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


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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: PLS-00201: identifier 'LIDS.NEXT' must be declared and PLS-00201: identifier 'LIDS.DELETE' must be
Next Topic: rowid
Goto Forum:
  


Current Time: Sat Dec 10 11:05:12 CST 2016

Total time taken to generate the page: 0.08042 seconds