locked
include column in select list based on criteria RRS feed

  • Question

  • Hi, 

    I need column to be included in select list only if criteria is met. Otherwise, it should not.

    When @includeAccount is set to true, then td2 column in resultset should show up (total 3 columns

    when @includeaccount is set to false, then td2 column in resultset SHOULD NOT show up (only 2 columns)

    DECLARE @tmpTable table
    (
    ID bigint IDENTITY(1,1) NOT NULL,
    VnID bigint NOT NULL,
    ShortName varchar(10) NOT NULL,
    AccountID int NOT NULL,
    Amount decimal (10,2)
    )

    DECLARE @actTable table
    (
    AccountID bigint NOT NULL,
    AccountName varchar(350)
    )

    declare @includeAccount bit = 0

    Insert into @tmpTable 
    select 1234, 'JPMC', 1456789, 450.00

    Insert into @actTable
    select 1456789, 'BankName'

    --select * from @tmpTable

    SELECT td1 = TRANS.ShortName,  
    td2 = case when @includeAccount = 1 then ACT.AccountName
    end, 
    td3 = '$' + CONVERT(varchar(100),Trans.Amount,1)
    FROM @tmpTable TRANS 
    LEFT OUTER JOIN @actTable ACT ON ACT.AccountID = 1456789

    How can I do it. Thank you. 

    Thursday, July 30, 2020 6:40 PM

Answers

  • If the caller indeed expects different output and different number of columns (which is a strange requirement), then you can simply use two separate statements with IF condition, e.g.

    IF @includeAccount

           select columns, Act.AccountName from ...

    else

         select columns from ... --- no need to join with the extra table


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, July 30, 2020 6:44 PM
    • Marked as answer by Spunny Monday, August 3, 2020 8:40 PM
    Thursday, July 30, 2020 6:44 PM
  • Hi Spunny,

    As Naomi said, the code is as follows:

    DECLARE @tmpTable table
    (ID bigint IDENTITY(1,1) NOT NULL,
    VnID bigint NOT NULL,
    ShortName varchar(10) NOT NULL,
    AccountID int NOT NULL,
    Amount decimal (10,2))
    DECLARE @actTable table
    (AccountID bigint NOT NULL,
    AccountName varchar(350))
    Insert into @tmpTable 
    select 1234, 'JPMC', 1456789, 450.00
    Insert into @actTable
    select 1456789, 'BankName'
    --select * from @tmpTable
    
    declare @includeAccount bit = 0
    if @includeAccount = 1
     begin
       SELECT td1 = TRANS.ShortName,  
       td2 = case when @includeAccount = 1 then ACT.AccountName
       end, 
       td3 = '$' + CONVERT(varchar(100),Trans.Amount,1)
       FROM @tmpTable TRANS 
       LEFT OUTER JOIN @actTable ACT ON ACT.AccountID = 1456789
     end
    else 
      begin 
        SELECT td1 = TRANS.ShortName, 
        td3 = '$' + CONVERT(varchar(100),Trans.Amount,1)
        FROM @tmpTable TRANS 
        LEFT OUTER JOIN @actTable ACT ON ACT.AccountID = 1456789
      end

    Best Regards
    Echo  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com




    • Edited by Echo Liuz Friday, July 31, 2020 3:02 AM
    • Marked as answer by Spunny Monday, August 3, 2020 8:40 PM
    Friday, July 31, 2020 2:49 AM

All replies

  • If the caller indeed expects different output and different number of columns (which is a strange requirement), then you can simply use two separate statements with IF condition, e.g.

    IF @includeAccount

           select columns, Act.AccountName from ...

    else

         select columns from ... --- no need to join with the extra table


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, July 30, 2020 6:44 PM
    • Marked as answer by Spunny Monday, August 3, 2020 8:40 PM
    Thursday, July 30, 2020 6:44 PM
  • Hi Spunny,

    As Naomi said, the code is as follows:

    DECLARE @tmpTable table
    (ID bigint IDENTITY(1,1) NOT NULL,
    VnID bigint NOT NULL,
    ShortName varchar(10) NOT NULL,
    AccountID int NOT NULL,
    Amount decimal (10,2))
    DECLARE @actTable table
    (AccountID bigint NOT NULL,
    AccountName varchar(350))
    Insert into @tmpTable 
    select 1234, 'JPMC', 1456789, 450.00
    Insert into @actTable
    select 1456789, 'BankName'
    --select * from @tmpTable
    
    declare @includeAccount bit = 0
    if @includeAccount = 1
     begin
       SELECT td1 = TRANS.ShortName,  
       td2 = case when @includeAccount = 1 then ACT.AccountName
       end, 
       td3 = '$' + CONVERT(varchar(100),Trans.Amount,1)
       FROM @tmpTable TRANS 
       LEFT OUTER JOIN @actTable ACT ON ACT.AccountID = 1456789
     end
    else 
      begin 
        SELECT td1 = TRANS.ShortName, 
        td3 = '$' + CONVERT(varchar(100),Trans.Amount,1)
        FROM @tmpTable TRANS 
        LEFT OUTER JOIN @actTable ACT ON ACT.AccountID = 1456789
      end

    Best Regards
    Echo  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com




    • Edited by Echo Liuz Friday, July 31, 2020 3:02 AM
    • Marked as answer by Spunny Monday, August 3, 2020 8:40 PM
    Friday, July 31, 2020 2:49 AM
  • THanks Naomi. I will go with what you suggested.
    Monday, August 3, 2020 8:40 PM
  • Thanks Echo. 
    Monday, August 3, 2020 8:40 PM

  • You've missed a fundamental concept in SQL and RDBMS. By its nature a table does not change structure. It can be three columns or can be to columns but can't flip-flop back and forth between them. Your DDL is also wrong . Remember the first day of RDBMS classes? A table must have a key. By definition. You also don't know. Current syntax for ANSI/ISO standard SQL.

    You can use an if-THEN-ELSE statement the switch between the two tables that are created by two separate queries. Or more properly you can pass a query result to the presentation layer in your tiered architecture. 

    The identity property of a table is never used as an identifier or a key. Since identifiers are on a nominal scale, numerics such as your BIGINT, can never be used the program in a proper schema. Numerics are used for magnitudes and quantities, or ordinal values. Let's explain what's wrong. Step-by-step:

    What you are you using temporary tables? Let's take a look at your accounts table:

    CREATE TABLE Accounts
    (account _id CHAR(15) NOT NULL PRIMARY KEY,
     account_name VARCHAR(350) NOT NULL) ;

    INSERT INTO Accounts
    VALUES ('1456789', 'BankName');

    See how it has a key? Notice how the account identifier the string of characters, and not a quantity. Later we'll see how it is referenced in other tables. Microsoft has had the ANSI/ISO standard syntax for insertion statements for many years now. You really should stop using the original Sybase syntax. 

    Your other table has no obvious name. The first thing we have to do is remove the non-relational IDENTITY table property. I have no idea what a VNID might be in your industry. But I know an identifier can never be an integer of any kind. We need a valid name that conforms to the metadata committee and ISO 11179 naming roles I'm going to assume that "short name" is a standard term in your industry. The account ID needs to reference the accounts table; this is why we call it a relational database. There is no such thing as a generic, universal "amount"; it has to be the amount of something in particular. This is a direct result of the law of identity and logic.

    CREATE TABLE Foobars 
    (vn _something BIGINT NOT NULL, 
    short_name VARCHAR(10) NOT NULL,
    account _id CHAR(15) NOT NULL
     REFERENCES Accounts(account _id)
       ON DELETE CASCADE
    foobar_amt DECIMAL (10,2) NOT NULL);

    We do not write with bit flags in SQL. That's how you used to do it in assembly language and some procedural languages. Have you ever had a course in basic data modeling that included the concepts of coupling and cohesion?

    INSERT INTO Foobars 
    VALUES (1234, 'foostuff', '1456789', 450.00);

    You also don't seem to understand how a CASE expression works. You have the option of an explicit ELSE clause; CASE.. THEN.. [ELSE ..] If the next listed ELSE clause is not given, THEN it acts as if there was an "ELSE NULL" clause. The datatype of the result of a CASE expression is the highest type in any of the THEN clauses or ELSE clause.

    Why are you concatenating a $ to a coumn expression. Again, remember your first week or two of RDBMS. Display formatting is done in a separate display tier and never done in the query itself. Also, the use of the = for naming expressions in the SELECT list is another old Sybase hangover; the ANSI/ISO standards use the AS. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, August 4, 2020 10:23 PM