Order Of Statements in Cast - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Order Of Statements in Cast

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-26 : 10:01:29

Maybee I am Missing Something.
I run this Cast and I get Correct Ouput.

ReadyCode = case When Board_status = 'Part' And Prints = 0
Then 'Y'
When Prints = 1 And Master_print_status = 'Part'
Then 'Y'
When Prints = 1 And Master_print_status = 'Pend'
Then 'R'
when Board_status = 'C' Or Board_status = 'st' Or Board_status = 'IN' And Prints = 0
Then 'G'
When Prints = 1 And Master_print_status = 'Comp'
Then 'G'
Else 'R'
end

How ever if I change the order of statements I get an incorrect output

Example

ReadyCode = case When Prints = 1 And Master_print_status = 'Pend'
Then 'R'
when Board_status = 'C' Or Board_status = 'st' Or Board_status = 'IN' And Prints = 0
Then 'G'
When Prints = 1 And Master_print_status = 'Comp'
Then 'G'
When Board_status = 'Part' And Prints = 0
Then 'Y'
When Prints = 1 And Master_print_status = 'Part'
Then 'Y'
Else 'R'
end

Are they not effectivly the same statement?????
Or do I have the wrong Idea About the Else Statement
I need to understand whats happening as I have alot of these to write and most more complex than this.

FYI The R, Y, G, stands for a color code Red, Yellow, Green a stoplight effect for scheduling screens.

Jim
Users <> Logic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 10:23:05

quote:

Board_status = 'C' Or Board_status = 'st' Or Board_status = 'IN' And Prints = 0

NEVER (And I really mean this) mix AND's and OR's without using parenthesis in a boolean expression. Try taking care of that, first and see if that works.

Which situation changes when you change the order?

i.e.,

in the first one, XYZ comes out as A
in the second, XYZ comes out as B

?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 10:25:35

Jim,

Go to BOL and check it out...

But the first TRUE statement wins....It's not a composite thing

And, you're CASE (Not Cast) statement is comparing apples and Oranges, you have to be careful.

I guess the question is...how do you know what the correct output is suppose to be...

Again it gets back to business rules...nothing technical about that....

Once you determine that...then you convert it in to code...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 10:29:22

quote:
Originally posted by jsmith8858

quote:

Board_status = 'C' Or Board_status = 'st' Or Board_status = 'IN' And Prints = 0

NEVER (And I really mean this) mix AND's and OR's without using parenthesis in a boolean expression. Try taking care of that, first and see if that works.

Which situation changes when you change the order?

i.e.,

in the first one, XYZ comes out as A
in the second, XYZ comes out as B

?

- Jeff

I was going to say something about that, and I do agree with Jeff here, but I don't think (damn, again) that that's a problem..

It actuall translates to:

Board_status IN ('C', 'st', 'IN') And Prints = 0

Which is no big deal...but it makes it easier, as Jeff suggests, esp when thing get really complicated.

Makes it easier to read..

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-26 : 10:37:20

Thanks Guys I should have known better.

Makeing a stupid newbee mistake. ( )

Then Listing it wrong.

Case Not Cast Daaaaaaaaaaaa

Oh Well it gave Brett a laugh.

Thank God Its almost the Weekend.

Jim
Users <> Logic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 10:43:36

quote:

Board_status IN ('C', 'st', 'IN') And Prints = 0

Which is no big deal...but it makes it easier, as Jeff suggests, esp when thing get really complicated.

Makes it easier to read..

Again, ALWAYS put parenthesis when mixing AND's and OR's, even if you know exactly how it will be interpreted. ALWAYS. No exceptions, never, ever, ever.

(You can tell I feel pretty strongly about this one !)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 10:43:55

quote:
Originally posted by JimL
Thank God Its almost the Weekend.

Taking a trip?

I reccomend a vist to margaritaville

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 10:56:16

By the way, it's not really evaluated the way you think, Brett.

check this out:


select 1 where 1 in (1,2,3) and 1=2

select 1 where 1=1 or 1=2 or 1=3 and 1=2

they return different results. AND has higher precendence than OR. But don't EVER rely on that and not use parens; as you see, it causes confusion !!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 11:22:08

I did say I agree with you...didn't I?

(what fool wouldn't

)

Absolutley..always..always..always..

Hell, Not only using paren's..but identation to the max...

(I'm going blind after all these years staring at a stupid screen...ah but you never had to stare at a green on black CRT...did'ya...)


SELECT   1 
 WHERE  (    1=1 
	  OR 1=2 
	  OR 1=3 
	)
   AND 1=2

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-26 : 11:35:40

[quote
(I'm going blind after all these years staring at a stupid screen...ah but you never had to stare at a green on black CRT...did'ya...)

Brett

[/quote]

Been there, done that, And before! (one of the reasons I got out For a while, Tech was not up to what I wanted To do with it)

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 11:43:31

quote:
Originally posted by JimL
(I'm going blind after all these years staring at a stupid screen...ah but you never had to stare at a green on black CRT...did'ya...)

After checking out the bio...no doubt....

That was mostly for Jeff though....

quote:

Been there, done that, And before! (one of the reasons I got out For a while, Tech was not up to what I wanted To do with it)

And is it any better now?

What did you do in the dark ages?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-26 : 11:57:36

Yep its better!!!!!!!!

As to the dark ages I was working in the steel industry, in data processing, We were a beta test site for joit venture between Burrous Corp. and Florida software (contenders for a piece of big Blues pie back in the 70s)We setup the 1st production level "Real time" system for them. Prior to that all updates were run at night. Yep all us computer operators where night owls feeding in stacks of cards and backing up to massive reel tapes placing them in storage vaults the size of a baseball field. Oh yes and the best part seperating and distributing report after report. Boxes and Boxes of paper.

So for a while I went and Blew Things up for the goverment.

Jim
Users <> Logic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 12:16:40

quote:

(I'm going blind after all these years staring at a stupid screen...ah but you never had to stare at a green on black CRT...did'ya...)

Yes sir! I've been programming since the early 80's, so I'm pretty familiar with the old CRT's. I am still quite fluent in 6502 8-bit assembly language !

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-26 : 14:32:41

Gasp! I never realized you were all so...so...old!!!

geez, I'm just a kid...why am I even talking...hats off to you!

O S

Make it idiot proof and someone will make a better idiot

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 14:48:24

of course, i was 12 back then !

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 15:23:26

quote:
Originally posted by jsmith8858

of course, i was 12 back then !

- Jeff

Get out of town...I didn't start my love affair until I was 15 (Timeshare TTY from high school to Columbia)...then again there weren't any computers back then before high school

Unless you count a programmable texas intrument TI..remeber the magnetic storage strips?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources