Conditionally adding a column in a SQL query

Question

I'm trying to add a conditional SELECT column in a query and I'm having trouble writing it out:

SELECT v.GLTypeID,
       v.something,
       v.somethignElse,
       CASE WHEN (SELECT TOP 1 Value FROM InterfaceParam WHERE InterfaceId = 0 AND Descr = 'gf') = 1 THEN a.CreditID ELSE NULL END AS CreditMemoID
FROM vGLDetails2 v
....
    LEFT OUTER JOIN AssociationFund f
        ON v.FundID = f.FundID
    LEFT JOIN dbo.APLedger a ON v.RelID = a.APLedgerID AND v.RelTypeID IN (39, 40)
....
ORDER BY v.Code;

The query above works, however if the CASE statement is still returning an additional column regardless of the result of the subquery. How can I remove it if the subquery doesn't return a row?

How can I do this?

AS can be applied to a column in the select list only, not to a partial expression. — The Impaler
– The Impaler, Commented Nov 7, 2022 at 17:07
A case expression cannot sometimes result in a column and other times not, your question is not clear. You are however selecting one random row from your InterfaceParam table which can change on every execution, presumably you're ok with that? — Stu
– Stu, Commented Nov 7, 2022 at 17:12
You cannot. When the subquery returns no record SQL will (or should?) return NULL. This is because the number of columns returned by an SQL script is fixed (by the definition of the SQL statement). — Luuk
– Luuk, Commented Nov 7, 2022 at 17:12
@Stu Yes, that's fine. Looks like I cannot return a dynamic column then. — user7496931
– user7496931, Commented Nov 7, 2022 at 17:14

The Impaler · Accepted Answer · 2022-11-07 17:09:20Z

1

Change the location of AS. For example:

SELECT v.GLTypeID,
       v.something,
       v.somethignElse,
       CASE WHEN (
         SELECT TOP 1 Value 
         FROM InterfaceParam 
         WHERE InterfaceId = 0 AND Descr = 'creditMemo') = 1 
       THEN a.CreditID     -- AS is not valid here
       END AS CreditMemoID -- AS is valid here
FROM vGLDetails2 v
....
    LEFT OUTER JOIN AssociationFund f
        ON v.FundID = f.FundID
    LEFT JOIN dbo.APLedger a ON v.RelID = a.APLedgerID AND v.RelTypeID IN (39, 40)
....
ORDER BY v.Code;

Note: I removed ELSE NULL since this is the default behavior of CASE.

answered Nov 7, 2022 at 17:09

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7496931 Over a year ago

This worked! However, I'm trying to avoid adding the column if the subqery doesn't return a record now.

The Impaler Over a year ago

You cannot do that in a simple way. To produce a variable number of columns you can use Dynamic SQL, but that's a whole different ball game. It's simpler to detect the null in the UI and remove the column there.

Collectives™ on Stack Overflow

Conditionally adding a column in a SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related