only return a field when a certain value is met.

Hi,

Simple question.

SELECT X,Y,Z
FROM TABLE
WHERE X = 55

In the above query I want to put a check on variable and only return one of the fields if the variable meets a certain criteria.

For instance:

SELECT
  X,
  CASE @CHECK
     WHEN 1 THEN
        --HERE I WANT TO RETURN Y
     ELSE
        --DONT RETURN THE FIELD AT ALL.
   END,
  Z

FROM TABLE
ETC ETC

Can this be done?
scm0smlAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot "skip" a complete column, but you can set the value to null in the output...

or write the query 2 times.

SELECT
  X,
  CASE @CHECK WHEN 1 THEN Y  ELSE NULL END,
  Z

FROM TABLE
ETC ETC

or

IF (@CHECK = 1)
SELECT
  X,
  Y,
  Z

FROM TABLE
 ETC ETC
ELSE
SELECT
  X,
  Z
FROM TABLE
 ETC ETC
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Can this be done?
Not in a single statement, you need to use the 'IF' statement
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot "skip" a complete column, but you can set the value to null in the output...

or write the query 2 times.

SELECT
  X,
  CASE @CHECK WHEN 1 THEN Y  ELSE NULL END,
  Z

FROM TABLE
ETC ETC

or

IF (@CHECK = 1)
SELECT
  X,
  Y,
  Z

FROM TABLE
 ETC ETC
ELSE
SELECT
  X,
  Z
FROM TABLE
 ETC ETC
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jai STech ArchCommented:
you are already using CASE to check the critirea...that can be done...
wht other thing you want ?
0
 
Ashish PatelCommented:
Yes but you have to this way,

Declare @Sql as varchar(2000)
Declare @CHECK Int
Set @CHECK = 5
Set @SQL = 'SELECT   X, CASE ' + Cast(@CHECK as varchar) + ' WHEN 1 THEN
        --HERE I WANT TO RETURN Y
     ELSE
        --DONT RETURN THE FIELD AT ALL.
   END,
  Z '
Exec (@Sql)

0
 
scm0smlAuthor Commented:
yeah but i need to exlcude the field from the results based on the value of the variable,
angelIII: is saying that can't be done in one go.

The actual statement im trying to use this in is too big to use an if else statement so will have to just return the field by the look of it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.