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

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    359
    Rep Power
    10

    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,664
    Rep Power
    4288
    please show the entire query

    you can't put a SELECT inside a COALESCE (unless it returns only a single value)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    359
    Rep Power
    10
    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,664
    Rep Power
    4288
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    359
    Rep Power
    10
    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
    359
    Rep Power
    10
    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,664
    Rep Power
    4288
    is your database mysql (the forum you posted in) or microsoft sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    359
    Rep Power
    10
    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,664
    Rep Power
    4288
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo