Check Different Select query result

[Naunt]

Thank you for your reply. So far what I've found CASE are use like below

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END

UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)

But, I have no idea how to use in my query. Could you please give me some samples by using my scenarios? Thanks and best regards

[Naunt]

Thank you for you reply Sorry, I didn't get what you mentioned. This are some of my many different conditions

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0) AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0) OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0) > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))

[Mycroft Holmes]

Using your second format you basically take your where clauses and place them between WHEN and THEN

UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN whereclause1 THEN VacationHours + 40
WHEN whereclause2 THEN VacationHours + 10
WHEN whereclause3 THEN VacationHours + 19
WHEN whereclause4 THEN VacationHours + 11
END
)

The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck

Never underestimate the power of human stupidity RAH

[Wendelius]

Ok, If I take an example. Perhaps the easiest way to combine different statements would be to use union. For example if you have following logic (pseudo):

select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
select @result = count(*) from table where column1 > 100
if @result = 20 then color = yellow

Now you could combine these to something like:

cursor = select count(*) from table where column1 < 100
union all
select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then color = yellow

However, as you see, both select statements would get the count from different rows. In example 1 the second query isn't executed at all if count > 10. In the second example all the selects are executed even if the results in different portions aren't needed. This can be very time (and resource) consuming. This was a trivial case and most likely isn't exactly the same as in your situation but the point I'm trying to make is that is it really beneficial to combine all the statements. Another point of view is that the statement may become very large and hard to maintain if it contains too much logic. Another (perhaps an easy ) option is to use scalar queries. This might work more easily for your case. The previous example could be something like:

select
@result1 = (select count(*) from table where column1 < 100)
@ersult2 = (select count(*) from table where column1 > 100);

if @result1 > 10 then color = blue
else if @result2 = 20 then color = yellow

However the same performance problem as described earlier may arise.

The need to optimize rises from a bad design.My articles[^]

modified on Friday, August 12, 2011 6:43 AM

[Wendelius]

Mycroft Holmes wrote:

The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible

This is very true if the where clauses are even a bit complex...

The need to optimize rises from a bad design.My articles[^]

[Naunt]

Absolutely right :thumbsup: I'm at that situation now :((

[Naunt]

Many thanks Will try with yours suggestion also.

[Wendelius]

Instead of trying to combine everything to a giant statement, could you go the other way: brake it into pieces. Don't know the whole situation, but could you for example create small, separate functions that fetch the desired info and in the 'main' logic use these functions (or procedures if you like). When you create the parameterized functions I would guess that at some point you notice the similarities between conditions if there are any so perhaps the amount of functions won't be as many as you now have different selects. Also during the process you may find different kind of options to build the desired output that are now hard to see. In any case, best of luck!

The need to optimize rises from a bad design.My articles[^]

[Naunt]

Thanks again. Like your last example, it's look clear and easy :) But as you have told all selects will execute :(

[Naunt]

I hope this is a great idea. But to be honest I've never been used function and also not familiar. I'm gonna learn it on my Weekend. Any article or site you want to recommend?:) Thanks again and have a nice weekend.

[Wendelius]

naunt wrote:

Any article or site you want to recommend?

I think there are lots of articles and sites about both so I have no specific suggestions. With a quick look I found these: - http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm[^] - http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server[^] And of course go through msdn pages: - Stored procedures[^] - Returning Data from a Stored Procedure[^] - Create procedure[^] - Create function[^]

naunt wrote:

Thanks again and have a nice weekend.

You too :)

The need to optimize rises from a bad design.My articles[^]

[Pascal Ganaye]

I don't know your data but you could try to get all your counts in one go using a CASE WHEN. The COUNT aggregates only non null values.

SELECT
@result1 = COUNT(CASE WHEN (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 ...)
THEN 1 ELSE NULL END),
@result2 = COUNT(CASE WHEN (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 ...)
THEN 1 ELSE NULL END),
@result3 = COUNT(CASE WHEN (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 ...))
THEN 1 ELSE NULL END)
FROM table
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL

This would be efficient as you would only read your table once this way. Then the logic you can probably remove quite a bit of the begin end and it becomes (sometime) clearer.

IF @result1 = 20 SET @Color='Red Color'
Else IF @result2=20 SET @Color='Blue Color'
Else IF @result3>0 SET @Color='Blue Color'
Else IF @result4>0 SET @Color='Blue Color'
Else IF @result5=20 SET @Color='Green Color'
Else IF @result6>0 SET @Color='Yellow Color'
Else @Color='Orange Color'