#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    please show the entire query

    you can't put a SELECT inside a COALESCE (unless it returns only a single value)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    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"
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    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'
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    is your database mysql (the forum you posted in) or microsoft sql server?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    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

    • cafelatte agrees : :-)
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo