none
Help Using COALESCE

    Question

  • Hi,

    I have the below code.

    SELECT
    AccountNumber AS [Key],
    ( SELECT
    'Phone' AS "ITContact/ITConnectionType",
    MainPhone AS "ITContact/ConnectionSequence",
    NULL,
    'Fax' AS "ITContact/ITConnectionType",
    Fax AS "ITContact/ConnectionSequence",
    NULL,
    'EMail' AS "ITContact/ITConnectionType",
    EmailAddress1 AS "ITContact/ConnectionSequence"
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('ItContacts') ,
    TYPE
    )
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('BusinessPartner')

    If there is no data in the source columns for "MainPhone", "Fax" or "EmailAddress1", then my XML output will be as follows:

    <BusinessPartner>
      <Key>1487</Key>
      <ItContacts>
        <ITContact>
          <ITConnectionType>Phone</ITConnectionType>
        </ITContact>
        <ITContact>
          <ITConnectionType>Fax</ITConnectionType>
        </ITContact>
        <ITContact>
          <ITConnectionType>EMail</ITConnectionType>
        </ITContact>
      </ItContacts>
    </BusinessPartner>

    I'd like the "ITConnectionType" sections to only appear if the corresponding "ConnectionSequence" fields contained data. Can I use COALESCE for this? I'm struggling with getting the correct syntax.

    Any help would be appreciated.

    Many Thanks,


    James Bratley

    Tuesday, March 05, 2013 12:46 PM

Answers

  • Hello James ,

      You may have to CASE statement. Try something as below

      Note : SQL not test.Please consider this as a Pseudocode.

    SELECT
    AccountNumber AS [Key],
    ( SELECT
    CASE 
    WHEN MainPhone IS NULL THEN NULL
    ELSE 'Phone' 
    END  AS "ITContact/ITConnectionType",
    MainPhone AS "ITContact/ConnectionSequence",
    NULL,
    CASE 
    WHEN Fax IS NULL THEN NULL
    ELSE 'Fax' 
    END 
     AS "ITContact/ITConnectionType",
    Fax AS "ITContact/ConnectionSequence",
    NULL,
    CASE 
    WHEN EmailAddress1 IS NULL THEN NULL
    ELSE 'EMail' 
    END 
    AS "ITContact/ITConnectionType",
    EmailAddress1 AS "ITContact/ConnectionSequence"
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('ItContacts') ,
    TYPE
    )
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('BusinessPartner')


    Best Regards Sorna

    • Marked as answer by James Bratley Tuesday, March 05, 2013 2:41 PM
    Tuesday, March 05, 2013 2:16 PM

All replies

  • You need to extract the contact data into a separate table (expression):

    WITH    Normalized
              AS ( SELECT   AccountNumber AS [Key] ,
                            'Phone' AS [Type] ,
                            MainPhone AS [Value]
                   FROM     Accounts
                   WHERE    NOT MainPhone IS NULL
                   UNION ALL
                   SELECT   AccountNumber ,
                            'Fax' ,
                            Fax
                   FROM     Accounts
                   WHERE    NOT Fax IS NULL
                   UNION ALL
                   SELECT   AccountNumber ,
                            'EMail' ,
                            EmailAddress1
                   FROM     Accounts
                   WHERE    NOT EmailAddress1 IS NULL
                 )
        SELECT  *
        FROM    Normalized
        WHERE   AccountNumber = '?'


    Tuesday, March 05, 2013 1:15 PM
  • Thanks for the help Stefan. I'm a bit confused with your example. Would this still allow for the use of FOR XML, and would it be able to keep my existing XML structure with <Key> contained in the root and the contact data stored in <ITContacts>? I can't figure it out from your code.

    James Bratley

    Tuesday, March 05, 2013 1:38 PM
  • Hello James ,

      You may have to CASE statement. Try something as below

      Note : SQL not test.Please consider this as a Pseudocode.

    SELECT
    AccountNumber AS [Key],
    ( SELECT
    CASE 
    WHEN MainPhone IS NULL THEN NULL
    ELSE 'Phone' 
    END  AS "ITContact/ITConnectionType",
    MainPhone AS "ITContact/ConnectionSequence",
    NULL,
    CASE 
    WHEN Fax IS NULL THEN NULL
    ELSE 'Fax' 
    END 
     AS "ITContact/ITConnectionType",
    Fax AS "ITContact/ConnectionSequence",
    NULL,
    CASE 
    WHEN EmailAddress1 IS NULL THEN NULL
    ELSE 'EMail' 
    END 
    AS "ITContact/ITConnectionType",
    EmailAddress1 AS "ITContact/ConnectionSequence"
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('ItContacts') ,
    TYPE
    )
    FROM Accounts
    WHERE AccountNumber = ?
    FOR XML PATH('BusinessPartner')


    Best Regards Sorna

    • Marked as answer by James Bratley Tuesday, March 05, 2013 2:41 PM
    Tuesday, March 05, 2013 2:16 PM
  • Thanks Sorna, that works perfectly :)

    James Bratley

    Tuesday, March 05, 2013 2:40 PM