Result set should return all values inspite of a "Where clause"

Question

Post reply

Result set should return all values inspite of a "Where clause"

mtz676

SSCrazy Eights

Points: 9212
More actions
January 14, 2016 at 7:15 am

#303760

I have 10 rows and 10 columns in a table with the where clause based on 2 columns
(where ColA > ColB).
If 6 rows satisfy the condition it is obvious that the result set will contain
only those 6 rows.
But my result set should contain all the 10 rows and also an 11th column which tags the
corresponding rows with a True or False value when the condition is Passed or Failed.
And if I have many rows and 10 columns where the result is based of a condition for all the 10 columns but I still need to return all the rows.
Ex:Col A ,col B,col C,col D,col E,col F,col G....... col J
Col A > 10
Col B > 100
col C < 20
My example of 10 rows was only to get my point across.My apologies for that.
I need to display in a SQL report the corresponding row/col value in green or red based on the validity of the condition (True/False)
Should I do this logic in the database level or should I have this logic in the SQL report.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Remove the where clause.

Add a column to your select

Iif (Cola >Colb, 'true','false')

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You just need to change the condition from the WHERE into a CASE statement in the column list.

--From this

SELECT ColA,

ColB,

ColC,

ColD,

ColE,

ColF,

ColG,

ColH,

ColI,

ColJ

FROM MyTable

WHERE ColA > ColB;

--To this:

SELECT ColA,

ColB,

ColC,

ColD,

ColE,

ColF,

ColG,

ColH,

ColI,

ColJ,

CASE WHEN ColA > ColB THEN 'True' ELSE 'False' END AS ColK

FROM MyTable;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 3

If my result set has a million rows wouldn't a case statement be very slow than a where clause.

Is there a better way of doing this.My environment is sqlserver 2008.

And what if I have 10 columns where the result is based of a condition for all the 10 columns but I still need to return all the 10 rows.

Ex:Col A ,col B,col C,col D,col E,col F,col G....... col J

Col A > 10

Col B > 100

col C < 20

My example of 10 rows was only to get my point across.My apologies for that.

I need to display in a SQL report the corresponding row/col value in green or red based on the validity of the condition (True/False)

Should I do this logic in the database level or should I have this logic in the SQL report.

Thanks

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.
Is there a better way of doing this.My environment is sqlserver 2008.
My example of 10 rows was only to get my point across.My apologies for that.

You have no choice.

A WHERE clause filters rows from the returned results.

This particular iif()/case statement will run very fast.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.

That's pretty immaterial. To do what you want requires a case statement, not a WHERE clause.

A WHERE clause filters rows out. You don't want to filter rows out, therefore you can't use a WHERE clause. You want to add a column with the result of a comparison. That's what a CASE statement does.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.
Is there a better way of doing this.My environment is sqlserver 2008.
My example of 10 rows was only to get my point across.My apologies for that.

Of course, because you're not limiting the rows. The case statement is not the problem, the problem is that you want to return all of the rows.

If you need to return all the rows, you need to read the whole table and that is expensive.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.

CASE won't make it slow...

Shifting gears... I don't know what you're doing with your million row result set but, if you're returning it to the screen, you're probably doing something wrong. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Jeff Moden (1/14/2016)
Shifting gears... I don't know what you're doing with your million row result set but, if you're returning it to the screen, you're probably doing something wrong. 😉

Oh, c'mon, Jeff!

Read the question again:

I have 10 rows and 10 columns in a table with the where clause based on 2 columns
(where ColA > ColB).

Got it?

"10 rows and 10 columns" - if it does not immediately ring a bell for you, you mast have totally forgotten how test questions looks like. :hehe:

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Sergiy (1/14/2016)
Jeff Moden (1/14/2016)
Shifting gears... I don't know what you're doing with your million row result set but, if you're returning it to the screen, you're probably doing something wrong. 😉
Oh, c'mon, Jeff!
Read the question again:
I have 10 rows and 10 columns in a table with the where clause based on 2 columns
(where ColA > ColB).
Got it?
"10 rows and 10 columns" - if it does not immediately ring a bell for you, you mast have totally forgotten how test questions looks like. :hehe:

Heh... I wonder what I posted on the other post where the OP said he wanted a million row result set. Certainly got my wires crossed on posts. :blush: Now to find the other post again. Hmmmm... the OP edited his post... in fact, he edited ALL of his posts! it might be THIS post. :blink: I guess I'm going to have to go back to quoting all original posts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.
Is there a better way of doing this.My environment is sqlserver 2008.
And what if I have 10 columns where the result is based of a condition for all the 10 columns but I still need to return all the 10 rows.
Ex:Col A ,col B,col C,col D,col E,col F,col G....... col J
Col A > 10
Col B > 100
col C < 20
My example of 10 rows was only to get my point across.My apologies for that.
I need to display in a SQL report the corresponding row/col value in green or red based on the validity of the condition (True/False)
Should I do this logic in the database level or should I have this logic in the SQL report.
Thanks

Ah... it WAS this post. I feel much better now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

sqlnewbie17 (1/14/2016)
Ex:Col A ,col B,col C,col D,col E,col F,col G....... col J
Col A > 10
Col B > 100
col C < 20

CASE WHEN <Condition 1> AND <Condition 2> AND <Condition 3>... THEN 'True' ELSE 'False' END

The colouring will be done in your report

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 12

sqlnewbie17 (1/14/2016)
I need to display in a SQL report the corresponding row/col value in green or red based on the validity of the condition (True/False)
Should I do this logic in the database level or should I have this logic in the SQL report.
Thanks

This means no work for SQL server at all as it has no any idea about colors. Do it at client app.

But as it was already mentioned by Jeff, browsing about a million of rows, even they are pictured red and green ...

Thing about some more help for poor one who will be gazing on this output.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 13

GilaMonster (1/15/2016)
sqlnewbie17 (1/14/2016)
Ex:Col A ,col B,col C,col D,col E,col F,col G....... col J
Col A > 10
Col B > 100
col C < 20
CASE WHEN <Condition 1> AND <Condition 2> AND <Condition 3>... THEN 'True' ELSE 'False' END
The colouring will be done in your report

Just to throw it out there , this could be done when you are populating the table , having a (condition met?) column already built into the table. That is a judgement call on your part and will depend heavily on how the table is used. You can build this even as a persisted calculated column. Loads will be slower and reads faster. But that is tricky if these conditions change and are only temporary, etc...

----------------------------------------------------