|
|
Dear all,
Please suggest me to write shortest and effective query in Storeprocedure.
To assign value to @Color, there will be different select queries with different conditions, My query sample will be as the following,
Select @result=count(*) from table where condition1
IF @result = 20
Begin
@Color='Red Color'
End
Else
Begin
select @result=count(*) from table where condition2
IF @result=20
Begin
@Color='Blue Color'
End
Else
Begin
select @result=count(*) from table where condition3
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from table where Condition4
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from where Condition5
IF @result=20
Begin
@Color='Green Color'
End
Else
Begin
Select @result=count(*) from where Condition6
IF @result>0
Begin
@Color='Yellow Color'
End
Else
Begin
@Color='Orange Color'
End
End
End
End
End
End
Thanks and best regards
|
|
|
|
|
You should let us know what database you are using.
Look into CASE if you are using SQL Server
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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...
|
|
|
|
|
Absolutely right 
I'm at that situation now
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
|
Many thanks
Will try with yours suggestion also.
|
|
|
|
|
What's the best way to combine these statements depends totally on the conditions you're going to use. For example if the conditions fetch different subsets from the tables and you have written the if statements in such order where they most likely are true, then combining the different selects could lower the performance.
|
|
|
|
|
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))
|
|
|
|
|
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
|
|
|
|
|
Thanks again.
Like your last example, it's look clear and easy 
But as you have told all selects will execute 
|
|
|
|
|
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'
|
|
|
|
|
http://www.codeproject.com/Forums/1649/Csharp.aspx?select=3989498&tid=3989498
|
|
|
|
|
Dear all,
Please kindly advise my case
How to Compare 3columns from a row with data are Null value
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 Null Null 25 Null Null 25 Null Null
Null 20 Null Null 20 Null Null 20 Null
Select * from Table1 Where (A1=B1 and B1=C1) And (A2=B2 and B2=C2) And (A3=B3 and B3=C3)
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 0 0 25 0 0 25 0 0
0 20 0 0 20 0 0<big></big> 20 0
When data value are Null I didn't get result from my query, but after change Null value to 0 (zero) then I have got the result.
Could you please advise how to compare data with Null values ?
Thanks and best regards
|
|
|
|
|
Read up about ISNULL
Select * from Table1 Where (ISNULL(A1,'')=ISNULL(B1,'') and ISNULL(B1,'')=ISNULL(C1,'')) And (ISNULL(A2,'')=ISNULL(B2,'') and ISNULL(B2,'')=ISNULL(C2,'')) And (ISNULL(A3,'')=ISNULL(B3,'') and ISNULL(B3,'')=ISNULL(C3,''))
I hope that copy/paste worked
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try the below query
Select * from Table1 Where (ISNULL(A1,0)=ISNULL(B1,0) and ISNULL(B1,0)=ISNULL(C1,0)) And (ISNULL(A2,0)=ISNULL(B2,0) and ISNULL(B2,0)=ISNULL(C2,0)) And (ISNULL(A3,0)=ISNULL(B3,0) and ISNULL(B3,0)=ISNULL(C3,0))
Use isnull with all the column fields. If the value is null it will take it as zero else it will take the coressponding values.
I hope this will help you out!!!!
|
|
|
|
|
Many Thanks to Mycroft Holmes and Sujit0761
Both answer are work in order.
|
|
|
|
|
Does anyone know where i may be able to find / download a English to Afrikaans OR Afrikaans to English Database ??? I need it for a project i am doing, i do not want one that connects online or a translator, i want an actual database with the words and translations.
Thanx alot,
Josh
|
|
|
|
|
First of all, don't start a topic with some generic topic like "help". I had to click on it and read it to determine it's contents - something I don't do when there's not much time. People could have an easy answer and skip your question, simply because the topic doesn't provide any clue. Most people posting questions here on the boards are looking for help in one way or the other.
On to your problem; there are some free dictionaries here[^], but I never seen them and can't say much about them.
Ergane[^] has it's dictionaries available as a separate download.
Last alternative, you can download the wiktionary[^].
Good luck
Bastard Programmer from Hell 
|
|
|
|
|
Hi,
I am using a SSRS report and the report is generated by executing a stored procedure.
The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values.
I am executing a Complex SQL statement for all values of c and d.
For this reason i am using nested loop:
A as INPUT
B as INPUT
C as INPUT
D as input
Cursor Cur as OUTPUT
Loop for c
Loop for d
execute the SQL Statement(select
END loop
END Loop
problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed use permanent tables and I m struglling using temporary tables.
If i save the value in the cursor each time the loop runs the previous results get overwritten.
|
|
|
|
|
Use a Table Variable to store temporary data.
Syntax:
DECLARE @TableVar1 table(
Col1 int NOT NULL,
Col2 varchar(100),
Col3 datetime
)
And then you can use it just like a physical table, e.g:
INSERT INTO @TableVar1 (Col1, Col2, Col3) VALUES (10, 'Hello', GETDATE())
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
