CODEWITHSUNDEEP
sqlsql-servert-sql
Oday Salim
Oday Salim

Reputation: 1147

Select with CASE and Union [TSQL]

I have a payments table. I would like to select a few columns based on a receipt number. I would like to then display a conditional status col depending on a few values.

I have written a SQL statement that works fine just to do this:

CODE

SELECT 
    [Number], [Name], [Amount],
    CASE 
       WHEN [Downloaded by] IS NULL 
            AND [Request Accepted?] IS NULL 
          THEN 'Sent to Payments'
       WHEN [Downloaded by] IS NOT NULL 
            AND [Request Accepted?] IS NULL
          THEN 'Received by Payments'
       WHEN [Downloaded by] IS NOT NULL
            AND [Request Accepted?] IS NOT NULL 
           THEN 'Sent to Finance'
    END AS 'Real-time Status' 
FROM
    [DB] 
WHERE
    [Request Receipt Id] = 'PR0001'

I am building a form where users will be able to scroll through these values and I would like to display another column that is on the database. However, I do not want to show this column in the main search box. Therefore I set up the search box to contain only 4x columns (Number, Name, Amount and Real-time status).

The idea is if I have a 5th column, it wont show in the main userform unless they click on it and it will appear in another box.

My issue is setting up this SQL statement to organise the data as I want:

[Number], [Name], [Amount], [Real-time Status], [Description]

The code that I used to do this is:

SELECT 
    [Number], [Name], [Amount],
    CASE 
       WHEN [Downloaded by] IS NULL 
            AND [Request Accepted?] IS NULL 
          THEN 'Sent to Payments'
       WHEN [Downloaded by] IS NOT NULL 
            AND [Request Accepted?] IS NULL
          THEN 'Received by Payments'
       WHEN [Downloaded by] IS NOT NULL
            AND [Request Accepted?] IS NOT NULL 
           THEN 'Sent to Finance'
    END AS 'Real-time Status' 
FROM
    TEP_Payments_Table 
WHERE 
    [Request Receipt Id] = 'PR0001' 

UNION 

SELECT 
    [Description] 
WHERE 
    [Request Receipt Id] = 'PR0001'

However, when I try this, SQL Server does not like it and throws errors.

Can you guys please advise where I am going wrong and what is the most efficient way to get to what I want.

Thanks

Upvotes: 0

Views: 55

Answers (1)

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

If all data comes from a single table you just need to add the description field to your query.

        SELECT [Number],[Name],[Amount],
        CASE WHEN [Downloaded by] is null and [Request Accepted?] is null then 'Sent to Payments'
        WHEN [Downloaded by] is not null and [Request Accepted?] is null then 'Received by Payments'
        When [Downloaded by] is not null and [Request Accepted?] is not null then 'Sent to Finance'
        END as 'Real-time Status',
        Description
     from TEP_Payments_Table where [Request Receipt Id] = 'PR0001'

Upvotes: 2

Related Questions