[SFDonovan]

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

 CASE WHEN b.PREF_EMAIL_FLAG = 'Y' 

and

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?

[r937]

please show the entire query

you can't put a SELECT inside a COALESCE (unless it returns only a single value)

[SFDonovan]

This is my update

 /*--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.

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.

[r937]

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

[SFDonovan]

Trying to use this in an update statement.

UPDATE
  PS_NTSR_GF_STUFILE a
 SET a.INSTR_EMAIL_ADDR = ( 

but it doesn't want to play nice.

I thought something like

 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"

[SFDonovan]

This tells me "missing right parenthesis"

  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'; 

[r937]

is your database mysql (the forum you posted in) or microsoft sql server?

[SFDonovan]

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.

[r937]

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

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