more than one query in CASE-WHEN-THEN statement

Question

more than one query in CASE-WHEN-THEN statement

Viewing 8 posts - 16 through 22 (of 22 total)

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

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

Hi,

Sorry, I'm kinda busy at work so I can't reply immediately. Any way, I failed to send one question here. Do you want to save this in columns or in a variable?

Hmmmm, if your saving it in a column... I think it is impossible to return multiple values... but if anyone wanna correct me, I would appreciate it.

By the way sorry for the bad english.

If your not doing this in a select statement that returns a table and not save a variable value, I think it MIGHT be possible to return multiple values.

But sorry to say, I might be able to answer it tomorrow, I'm still a bit busy

column example:

SELECT col1, col2, col1+col2 FROM table1

variable example:

SELECT @var3=@Var1+@Var2

Simple IF or case:

IF (...)

....

ELSE IF (...)

...

ELSE

....

I realy don't know if i named them right but could you tell me which one of these three examples do you need?

From your last example, I think its better to stick with "IF"

See ya tomorrow

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

gan-685682 Ten Centuries Points: 1166 More actions · Answer 2

Quatrei.X (9/1/2008)
Simple IF or case:
IF (...)
....
ELSE IF (...)
...
ELSE
....
I realy don't know if i named them right but could you tell me which one of these three examples do you need?
From your last example, I think its better to stick with "IF"
See ya tomorrow

Actually this is the original queries 😀 .

if @FieldName='var1'

select @FValue = Col1 from Table1

else if @FieldName = 'var2'

select @FValue = col1 from Table2

else if @FieldName = 'var3'

select @FValue = col1 from Table3

else if @FieldName = 'var4'

select @FValue = col1 from Table4

...

else if @FieldName = 'var5'

begin -

if @bIsToTester = 1

begin

exec @ret = dbo.uspXXX ...

insert @BBB ( ...)

select ...

I am trying to improve it, since 99% (from 'var1' to var4') can be solved with CASE, but left 'var5'. Anyway, thanks for all your suggestion. 🙂

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

gan (8/31/2008)
I get 1469283328 for that. Sorry I do not quite understand what you are trying to explain. Can elaborate more? 😀

Yes... but please look at the actual execution plan for my query... look at the arrows that connect the different symbols. Internally, it is processing more that 2300 rows to get a single value.

You have many pieces of code that do the same thing... they don't have TOP 1 nore any other criteria to keep them from reading all the rows in the table. THAT makes for a huge performance problem because to resolve one row in your outer table requires that SQL Server resolve ALL the rows in the inner query. If you have 10,000 rows in your outer query and 1000 rows in the table in the inner query, that means that your query must resolve 10,000,000 rows to solve the whole query. And you have multiple instances of such inner queries within the same outer query.

Look at your second posting...

if @FieldName='var1'

select @FValue = Col1 from Table1

else if @FieldName = 'var2'

select @FValue = col1 from Table2

else if @FieldName = 'var3'

select @FValue = col1 from Table3

else if @FieldName = 'var4'

select @FValue = col1 from Table4

Wherever you've said "SELECT somevariable = col1 from sometable", you are actually reading the WHOLE sometable to get just one value. You must either at TOP 1 or, more likely, some more stringent criteria to the FROM clause of each SELECT... THAT is your performance problem.

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

gan-685682 Ten Centuries Points: 1166 More actions · Answer 4

Jeff Moden (9/1/2008)You must either at TOP 1 or, more likely, some more stringent criteria to the FROM clause of each SELECT... THAT is your performance problem.

using top 1 maybe a good suggestion, thanks for your explaination. 😀

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Actually, using TOP 1 may b the worst suggestion... you don't care which value you get from the respective tables?

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

gan-685682 Ten Centuries Points: 1166 More actions · Answer 6

Maybe, but the data can filtered with identity, should be unique enough.

select TOP 1 PARTNumber from AAA where [ID]=@PartID 😀

Quatrei.X SSCrazy Points: 2697 More actions · Answer 7

Hmmm.. If you have unique id's, I think theres no need for the TOP

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

gan-685682 Ten Centuries Points: 1166 More actions · Answer 8

you are right. I checked the execution plan and it does not differ much, thanks.