Home » SQL & PL/SQL » SQL & PL/SQL » How do I select columns from changing tables ? (Oracle 10g)
How do I select columns from changing tables ? [message #311425] Fri, 04 April 2008 08:12 Go to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Hi,

Can someone point me in the right direction for the following please ?

I want to select delivery addresses for a customer but at runtime I must figure out the correct table to fetch the records from before returning the results.
Something like this ...

SELECT cust_name, deliv_street, deliv_city
FROM one-of-two-tables

one-of-two-tables can be one of two different tables. If the customer has a onetime delivery address, the deliv_street, deliv_city fields must be retrieved from table sls042. If no address is found in this table, the fixed addresses table must be used (table com013) which has similar field names.

What must my SQL look like to get this to work ?
thanks,

Walter



Re: How do I select columns from changing tables ? [message #311428 is a reply to message #311425] Fri, 04 April 2008 08:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You have two basic choices.

1. Build the query as a string dynamically at runtime, and use an execute immediate or similar idea to run the query string.

2. Since there are only two options, write both queries as static, regular queries, that "know" the table they need. Then have some logic which dictates which of the two queries to call.
Re: How do I select columns from changing tables ? [message #311429 is a reply to message #311425] Fri, 04 April 2008 08:18 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Join the two tables and use the NVL function similar to:
nvl(first_table_value, second_table_value)

You'll probably need an outer join, but without giving us much details of you database or data, I am just guessing.
Re: How do I select columns from changing tables ? [message #311434 is a reply to message #311425] Fri, 04 April 2008 08:43 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks for the quick replies and the NVL function.

Would a logic like this be possible ?
SELECT
cust_name,
if cust_name exists in table sls040 then
return columns sls040.deliv_street, sls040.deliv_city
else
if cust_name exists in table com013 then
return columns com013.deliv_street, com013.deliv_city
end if
end if
resulting in the three beforementioned columns.
Would a CASE statement or an in-line view be a solution for this ?
Are there any performance issues ?
Re: How do I select columns from changing tables ? [message #311449 is a reply to message #311429] Fri, 04 April 2008 09:20 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
I managed to get some results with the NVL function but I can imagine it's a bad solution performance wise.

SELECT
a.cust_name,
NVL( (SELECT b.deliv_street FROM sls042 b where b.cust_name = a.cust_name), SELECT b.deliv_street FROM com013 b where b.cust_name = a.cust_name) ),
NVL( (SELECT b.deliv_city FROM sls042 b where b.cust_name = a.cust_name), SELECT b.deliv_city FROM com013 b where b.cust_name = a.cust_name) ),
FROM
customers a
WHERE a.cust_name = 'some name';
Re: How do I select columns from changing tables ? [message #311457 is a reply to message #311434] Fri, 04 April 2008 09:43 Go to previous messageGo to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
Hi,
try using join and give the custname in where condition of both queries.one which satisfies the condition will pull out the data.
 select cust_name, deliv_street, deliv_city
 from tbl1 where cust_name = 'somevalue' 
 union
 select cust_name, deliv_street, deliv_city
 from tbl2 where cust_name = 'somevalue'


regards,
brintha

Re: How do I select columns from changing tables ? [message #311478 is a reply to message #311457] Fri, 04 April 2008 10:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
unless both tables hold (different) addresses
Re: How do I select columns from changing tables ? [message #311844 is a reply to message #311425] Mon, 07 April 2008 02:27 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Yes Frank. The tables can hold different addresses so the UNION isn't going to give me what I need.
Re: How do I select columns from changing tables ? [message #312056 is a reply to message #311425] Mon, 07 April 2008 21:03 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
You can also try this
************************************
BEGIN
SELECT
cust_name
,........
from sls040
exception
when no_data_found then
begin
select
SELECT
cust_name
,.......
from
com013;
exception
when others then
cust_name:= null;
end;
END;
***********************************
Alternatively, Also you can try this:
**************************************************
begin
begin
select
cust_name
,........
into v_cust_name
from sls040;
exception
when others then
v_cust_name:=null;
end;
if v_cust_name is null then
begin
select
cust_name
,........
into v_cust_name
from com013;
exception
when others then
v_cust_name:=null;
end;
end if;
end;
******************************************************
Re: How do I select columns from changing tables ? [message #312068 is a reply to message #311425] Mon, 07 April 2008 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
bibsdash,

Use <CODE_TAGS>, please!
Re: How do I select columns from changing tables ? [message #312600 is a reply to message #312056] Wed, 09 April 2008 07:10 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks bibsdash,

I'll give this a try.
Re: How do I select columns from changing tables ? [message #312612 is a reply to message #312068] Wed, 09 April 2008 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Tue, 08 April 2008 05:09
bibsdash,

Use <CODE_TAGS>, please!

As this has been asked to him many times I don't think he will ever do it. Sad

Regards
Michel
Re: How do I select columns from changing tables ? [message #312706 is a reply to message #312612] Wed, 09 April 2008 11:30 Go to previous message
bibsdash
Messages: 47
Registered: April 2008
Member
Michel
I am including <code> tag now in all my posts.
Sorry about that.

[Updated on: Wed, 09 April 2008 11:30]

Report message to a moderator

Previous Topic: IS THERE ANY EQUIVALENT FUNCTION IN ORACLE LIKE GROUP_CONCAT IN MYSQL (merged 3)
Next Topic: Create UNQ Random Number
Goto Forum:
  


Current Time: Wed Dec 07 10:44:30 CST 2016

Total time taken to generate the page: 0.20484 seconds