We help IT Professionals succeed at work.

only return a field when a certain value is met.

scm0sml
scm0sml asked
on
239 Views
Last Modified: 2010-07-27
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?
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>Can this be done?
Not in a single statement, you need to use the 'IF' statement
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
Jai STech Arch

Commented:
you are already using CASE to check the critirea...that can be done...
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)

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.