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

Does this answer your question? [How to use an Alias in a Calculation for Another Field](https://stackoverflow.com/questions/46472513/how-to-use-an-alias-in-a-calculation-for-another-field) — GSerg, Jul 12 '20 at 08:05

score 0 · Accepted Answer · answered Jul 12 '20 at 08:19

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

Thanks for clarifying, Jim. Much appreciated. – SQLGIT_GeekInTraining Jul 12 '20 at 10:08 — SQLGIT_GeekInTraining, Jul 12 '20 at 10:08

score 0 · Answer 2 · answered Jul 12 '20 at 12:14

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.

Thank you - this is a new function for me and will try to keep this in mind for something similar next time. You're right about the intention behind the CAST() function - I do want a marker next to each relationship so that it's easy to lookup the linkage.Cheers. — SQLGIT_GeekInTraining, Jul 12 '20 at 21:09

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

2 Answers2