Solved

Case Statement using Else to pull All values

Posted on 2015-01-06
9
58 Views
Last Modified: 2015-01-10
I want to use a Case statement to pull a row or rows based on a criteria.  If the criteria is not met I want to retrieve  all values for the field.  This is what i have so far...I am struggling on how to pull all rows in the else statement

Select * from payroll
WHERE
mgrid =
CASE
OFFICEID = 10 THEN  BOB101
ELSE
'I NEED TO PULL IN ALL ROWS
END
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 100 total points
ID: 40534806
Try
Select * from payroll
WHERE
mgrid = 
CASE
OFFICEID = 10 THEN  BOB101
ELSE
mgrid
END

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40534856
Easier to use WHERE and precedence constraints in a WHERE clause than CASE

SELECT * FROM Payroll
WHERE (OFFICEID=10 AND mgrid='BOB101') OR (OFFICEID <> 10) 

Open in new window

0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 100 total points
ID: 40534971
Hello Try this

Select * from payroll
WHERE

CASE WHEN 
OFFICEID = 10 THEN  CASE WHEN mgrid = 'BOB101' THEN 1 ELSE 0 END ELSE 1 END =1 

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40534974
I agree with Jim and would prefer to see you use his suggestion.

I'm just correcting the case expression syntax (of the first comment)

Select * from payroll
WHERE
mgrid =
CASE WHEN
      OFFICEID = 10 THEN  'BOB101' -- this appears to be a literal
ELSE
      mgrid
END
0
 

Author Comment

by:upobDaPlaya
ID: 40539357
Jim,

Can you explain your precedence comment ?
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40539363
You can try the way shown in above.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 40539432
WHERE
 (OFFICEID=10 AND mgrid='BOB101')  -- BOTH conditions are evaluated together
OR                                 -- or
(OFFICEID <> 10)                   -- this condition is considered

Open in new window

so,
if a record with OFFICEID=10 but mgrid <> 'BOB101' then it is excluded
any record with OFFICEID <> 10 is included
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 200 total points
ID: 40539453
Precedence constraints is the order of execution in an expression, and using parenthesis ( ) to override that order.

1 + (2 * 3) = 7
(1 + 2) * 3 = 9

It also applies to logical expressions like AND or OR, as PortletPaul demonstrates above.

(TRUE AND FALSE) OR TRUE = TRUE
TRUE AND (FALSE OR FALSE) = FALSE
0
 

Author Closing Comment

by:upobDaPlaya
ID: 40542638
Thanks..I used Jim's suggestion, but all of the input allowed me to evaluate and learn the CASE vs OR possibilities.. thanks !
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question