scm0sml
asked on
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?
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?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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
you are already using CASE to check the critirea...that can be done...
wht other thing you want ?
wht other thing you want ?
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Not in a single statement, you need to use the 'IF' statement