Can I use a derived column in SQL Server for performing a CASE function?

Question

I have a 'main' table, C1, which has a record identifier, which in turn may be linked to either an account or a customer ID. The relationship / linkage is stored on two separate tables, one with record ID - Account level relationship, and the other with record ID - Customer level relationship. It is possible for C1 records to have both account and customer relationships.

I am trying to create a join to bring in the relationships into one neat view, where I am looking for an output as follows:

ID         ---- LINKAGE --- REL_TYPE
C1 Record ID --- ABC123 --- ACCOUNT
C1 Record ID --- 1235 ---- CUSTOMER
C1 Record ID --- NULL ---- UNLINKED

As hopefully clear from the above, an account is alphanumeric, whereas a customer ID is numeric. I am using this in my COALESCE to derive the 'LINKAGE' column, which doesn't exist on its own.

My code currently looks like this:

SELECT 
     C1.ID,
     C1.Name,
     COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) AS LINKAGE,
     CASE 
        WHEN LINKAGE LIKE '[A-Z]%' THEN CAST('ACCOUNT' AS varchar(255))
        WHEN LINKAGE LIKE '10%' THEN CAST('CUSTOMER' AS varchar(255))
        ELSE 'Unlinked'
     END AS REL_TYPE

FROM C1

LEFT JOIN C2 ON C1.ID = C2.ID
LEFT JOIN C3 ON C1.ID = C3.ID

Syntactically the code looks fine in SQL Server in that I am not getting any errors, but when I execute, I get an error that 'LINKAGE' doesn't exist as a column - it doesn't, but wouldn't the coalesce tell the compiler that the linkage is the basis for the case function?

Please let me know if further clarity is required.

Cheers, SQLGeekInTraining

Answer 1

Please use below query. You cannot use LINKAGE in the case statement as it is a alias and not original database column. You have to use actual column name along with the function instead of alias name

SELECT 
   C1.ID,
   C1.Name,
   COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) AS LINKAGE,
   CASE 
     WHEN COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) LIKE '[A-Z]%' THEN CAST('ACCOUNT' AS 
     varchar(255))
     WHEN COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) LIKE '10%' THEN CAST('CUSTOMER' AS 
     varchar(255))
    ELSE 'Unlinked'
 END AS REL_TYPE

FROM C1

LEFT JOIN C2 ON C1.ID = C2.ID
LEFT JOIN C3 ON C1.ID = C3.ID

Answer 2

SQL Server allows you to use the APPLY keyword to define column aliases in the FROM clause. This will simplify your logic:

SELECT C1.ID, C1.Name, v.LINKAGE,
       (CASE WHEN v.LINKAGE LIKE '[A-Z]%' THEN CAST('ACCOUNT' AS varchar(255))
             WHEN v.LINKAGE LIKE '10%' THEN CAST('CUSTOMER' AS varchar(255))
             ELSE 'Unlinked'
        END) AS REL_TYPE
FROM C1 LEFT JOIN
     C2
     ON C1.ID = C2.ID LEFT JOIN
     C3 
     ON C1.ID = C3.ID CROSS APPLY
     (VALUES (COALESCE(C2.ACC_ID, C3.CUSTOMER_ID))) v(LINKAGE);

The CAST() in the CASE expression seems superfluous unless your intention is to save the result into a table and you want to be clear about the type.