February 3rd, 2012, 09:22 AM
-
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
How do I nest these two?
February 3rd, 2012, 09:26 AM
-
please show the entire query
you can't put a SELECT inside a COALESCE (unless it returns only a single value)
February 3rd, 2012, 09:38 AM
-
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)
I thought a nested CASE statement would work.
February 3rd, 2012, 10:21 AM
-
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 
however, the solution above will still work
February 7th, 2012, 10:05 AM
-
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"
February 7th, 2012, 10:08 AM
-
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';
February 7th, 2012, 10:14 AM
-
is your database mysql (the forum you posted in) or microsoft sql server?
February 7th, 2012, 10:21 AM
-
Originally Posted by r937
is your database mysql (the forum you posted in) or microsoft sql server?
It's Oracle.
... but you are the most helpful person I know and don't visit the Oracle forums.
Comments on this post
February 7th, 2012, 10:47 AM
-
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