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

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

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