CODEWITHSUNDEEP
sql-server-2008t-sqlselectconditional-statements
Uchenna Ebilah
Uchenna Ebilah

Reputation: 1081

TSQL SELECT based on a condition

I am trying to do a select from CTE based on a condition.

There is a variable I've declared for today's period (@PRD). It holds the value of what period we are currently in.

Now I would like to do a selection from a table that will restrict what information is returned based on whether we are in the first half of the year or not.

For instance, we are in period 2 so I want everything returned from my CTE which falls between PRD 1 and 5. IF we were in say period 6 (after 5), then yes I'd want everything returned from the table.

This is the pseudocode of what I'm trying to accomplish:

SELECT
    CASE
       WHEN @PRD <= 5
         THEN (SELECT * FROM DISPLAY WHERE PERIOD IN (1,2,3,4,5))
         ELSE (SELECT * FROM DISPLAY)
    END

I'm getting an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Please any thoughts on how I can do this?

Thanks x

EDITED/UPDATED:

More of the code involves a CTE and is really long. Bottom line is lets say I have this CTE

;WITH DISPLAY as (
     select * from lots_of_things
)
SELECT * FROM DISPLAY

Having done a regular select on this CTE, it returns data that looks like this:

PERIOD (INT)    DEPARTMENT   GROUP  BUDGET
1               ENERGY       HE     500
2               ENERGY       HE     780
3               ENERGY       HE     1500
4               ENERGY       HE     4500
5               ENERGY       HE     400
6               ENERGY       HE     3500
7               ENERGY       HE     940
8               ENERGY       HE     1200

I want it to show me just the top 5 rows if we the current period is 1,2,3,4,5. But to display ALL table rows if we are in any other period like 6,7,8,9 and onwards. The current period is held in the variable @PRD which is derived from doing a comparison of today's date with ranges held in a table. The value is accurate and also type INT

Hope this helps

SQL FIDDLE

Upvotes: 1

Views: 308

Answers (2)

Fedor Hajdu
Fedor Hajdu

Reputation: 4695

This will work:

SELECT * FROM DISPLAY WHERE (@PRD > 5 OR PERIOD IN (1, 2, 3, 4, 5))

If this code confuses you, what's happening is that we check if @PRD > 5 and if that returns true, our expression is always true so we return all the rows. If the variable is less or equal to 5 (like you checked in your example), the first check is false and then we check if the period is the list.

Upvotes: 5

Smileek
Smileek

Reputation: 2797

This might be a solution:

IF @PRD <= 5
  SELECT * FROM DISPLAY WHERE PERIOD IN (1,2,3,4,5)
ELSE
  SELECT * FROM DISPLAY

UPD

In this case you should use variable instead of CTE, if it's possible.

DECLARE @PRD INT;
SELECT @PRD = PERIOD FROM SOME_TABLE WHERE ...

Upvotes: 1

Related Questions