Discuss Combine two queries in the Oracle Development forum on Dev Shed. Combine two queries Oracle Development forum discussing administration, Oracle queries, and other Oracle-related topics. Oracle is known as one of the most robust multi-platform relational databases available.
Receive the tools necessary to be the rock star of your field. Our 12-month program teaches you the evolving world of multi-channel marketing as well as the complex issues and opportunities found in the industry.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month! Download and Activate to enter!
Web development can be a daunting task, even for specialists. There is a lot of information to absorb and a lot of technologies to learn in order to manage a superior website. When trying to learn the ropes, developers need a reliable source to introduce new ideas that can be easily implemented. When working on large projects, even web veterans may run into a technology or an aspect of a technology that they are unfamiliar with.
Posts: 345
Time spent in forums: 3 Days 13 h 55 m 40 sec
Reputation Power: 5
Combine two queries
I was doing an update to a field using a view as a source but I think I want to combine this view.
What I want to do is by order of precedence select an email address if PREF_EMAIL_FLAG = 'Y'
then select by E_ADDR_TYPE first = 'CAMP' then by 'HOME' which ever first returns a non NULL value.
I have
PHP Code:
CASE WHEN b.PREF_EMAIL_FLAG = 'Y'
and
PHP Code:
CASE COALESCE ( (
SELECT a1.email_addr
FROM ps_email_addresses a1
WHERE a1.emplid = a.emplid
AND a1.e_addr_type = 'CAMP' )
, (
SELECT a1.email_addr
FROM ps_email_addresses a1
WHERE a1.emplid = a.emplid
AND a1.e_addr_type = 'HOME' ) )
END
END
FROM ps_email_addresses b
WHERE a.emplid = b.emplid
Posts: 345
Time spent in forums: 3 Days 13 h 55 m 40 sec
Reputation Power: 5
This is my update
PHP Code:
/*--Get Student email address--*/ MERGE INTO PS_NTSR_GF_STUFILE a USING PS_NTSR_EMAIL_VW b ON (a.EMPLID = b.EMPLID
AND a.run_cntl_id = %Bind(NTSR_GDFT_AET.RUN_CNTL_ID)
AND a.INSTITUTION = %Bind(NTSR_GDFT_AET.INSTITUTION)
AND a.STRM = %Bind(NTSR_GDFT_AET.STRM) ) WHEN MATCHED THEN
UPDATE
SET a.EMAIL_ADDR = b.EMAIL_ADDR
This is the view I was using in my update.
PHP Code:
SELECT a.emplid
, COALESCE ( (
SELECT a1.email_addr
FROM ps_email_addresses a1
WHERE a1.emplid = a.emplid
AND a1.e_addr_type = 'CAMP' ) , (
SELECT a1.email_addr
FROM ps_email_addresses a1
WHERE a1.emplid = a.emplid
AND a1.e_addr_type = 'HOME' ) )
FROM psoprdefn a;
I want to use b.PREF_EMAIL_FLAG = 'Y' before I check the existence of data from COALESCE (email_addr)
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Code:
SELECT a.emplid
, COALESCE(pref.email_addr
,camp.email_addr
,home.email_addr) AS email_addr
FROM ps_email_addresses a1
LEFT OUTER
JOIN ps_email_addresses pref
ON pref.emplid = a.emplid
AND pref.pref_email_flag = 'Y'
LEFT OUTER
JOIN ps_email_addresses camp
ON camp.emplid = a.emplid
AND camp.e_addr_type = 'CAMP'
LEFT OUTER
JOIN ps_email_addresses home
ON home.emplid = a.emplid
AND home.e_addr_type = 'HOME'
by the way, mysql does not support MERGE INTO -- i think you posted in the wrong forum
Posts: 345
Time spent in forums: 3 Days 13 h 55 m 40 sec
Reputation Power: 5
Trying to use this in an update statement.
PHP Code:
UPDATE
PS_NTSR_GF_STUFILE a
SET a.INSTR_EMAIL_ADDR = (
but it doesn't want to play nice.
I thought something like
PHP Code:
UPDATE
PS_NTSR_GF_STUFILE a
SET a.INSTR_EMAIL_ADDR = (SELECT COALESCE(pref.email_addr
,camp.email_addr
,home.email_addr) AS email_addr
FROM ps_email_addresses b
LEFT OUTER
JOIN ps_email_addresses pref
ON pref.emplid = b.emplid
AND pref.pref_email_flag = 'Y'
LEFT OUTER
JOIN ps_email_addresses camp
ON camp.emplid = b.emplid
AND camp.e_addr_type = 'CAMP'
LEFT OUTER
JOIN ps_email_addresses home
ON home.emplid = b.emplid
AND home.e_addr_type = 'HOME')
WHERE a.STRM = '1121'
AND a.RUN_CNTL_ID = 'sfd0012'
AND a.INSTITUTION = 'NT752';
but I'm getting "single-row subquery returns more than one row"
Posts: 345
Time spent in forums: 3 Days 13 h 55 m 40 sec
Reputation Power: 5
This tells me "missing right parenthesis"
PHP Code:
UPDATE
PS_NTSR_GF_STUFILE a
SET a.INSTR_EMAIL_ADDR = (SELECT COALESCE(pref.email_addr
,camp.email_addr
,home.email_addr) AS email_addr
FROM PS_NTSR_GF_STUFILE b, ps_email_addresses c
WHERE b.emplid = c.emplid
LEFT OUTER
JOIN ps_email_addresses pref
ON pref.emplid = c.emplid
AND pref.pref_email_flag = 'Y'
LEFT OUTER
JOIN ps_email_addresses camp
ON camp.emplid = c.emplid
AND camp.e_addr_type = 'CAMP'
LEFT OUTER
JOIN ps_email_addresses home
ON home.emplid = c.emplid
AND home.e_addr_type = 'HOME')
WHERE a.STRM = '1121'
AND a.RUN_CNTL_ID = 'sfd0012'
AND a.INSTITUTION = 'NT752';
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
there is a reason i don't visit the oracle forums... i hate oracle
that subquery looks like it's always gonna return more than one row
also, your "c" table seems superfluous, as it's already referenced in all three left joins
i think you'll have to make it a joined update -- if oracle supports it
Code:
UPDATE PS_NTSR_GF_STUFILE a
INNER
JOIN ( SELECT COALESCE(pref.email_addr
,camp.email_addr
,home.email_addr) AS email_addr
FROM PS_NTSR_GF_STUFILE b
LEFT OUTER
JOIN ps_email_addresses pref
ON pref.emplid = b.emplid
AND pref.pref_email_flag = 'Y'
LEFT OUTER
JOIN ps_email_addresses camp
ON camp.emplid = b.emplid
AND camp.e_addr_type = 'CAMP'
LEFT OUTER
JOIN ps_email_addresses home
ON home.emplid = b.emplid
AND home.e_addr_type = 'HOME' ) x
ON x.emplid = a.emplid
SET a.INSTR_EMAIL_ADDR = x.email_addr
WHERE a.STRM = '1121'
AND a.RUN_CNTL_ID = 'sfd0012'
AND a.INSTITUTION = 'NT752';
it's quite possible that this isn't the correct syntax, and it's also possible that oracle won't let you update a table while simultaneously selecting from it
i'm moving this thread over to the oracle forum where more knowledgeable people can help you
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
... but you are the most helpful person I know and don't visit the Oracle forums. 
Linear Mode
