• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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?
0
scm0sml
Asked:
scm0sml
1 Solution
 
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
 
Jai STech ArchCommented:
you are already using CASE to check the critirea...that can be done...
wht other thing you want ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now