September 26, 2016 at 2:44 pm
I am trying to implement a while loop and if condition (with a function call) in a case statement. Basically, I am trying to do this:
begin
case
when (condition1 and condition 2 and ncolumn_num between 6 and 9) then
while@i<10
begin
if(hsip.Is_numeric( hsip.getTempResponseById(@cregion, @cState_code, @nFY, @nReport_id, @nsection_id, @nquestion_number, @ndisplay_number, @nquestion_number, @nquestion_part_number, @suser_id, @nrow_number, i, @suser_id)))
@nrunningtotal = @nrunningtotal + hsip.getTempResponseById(@cRegion, @cState_code, @nFY, @nReport_id, @nsection_id, @nquestion_number, @ndisplay_number, @nquestion_number,
@nquestion_part_number, @suser_id, @nrow_number, @ncolumn_number, @suser_id)
end if
end
Can I implement while loop and if condition within case statement. My business logic requires me to total the columns from 6 to 9 and have final total in column:10 (I'm in the process of implementing). I have 2 functions: gettempresponsebyid: which returns response string and isnumeric function which converts to number.
Can someone please suggest a better way of doing this. I get this error: INCORRECT SYNTAX NEAR IF AND INCORRECT SYNTAX NEAR RUNNINGTOTAL.
thanks
September 26, 2016 at 3:03 pm
If the reason for your loop is to implement a running total, why not just use a Window function and do something like
SUM( <column to be summed> ) OVER PARTITION BY (<grouping column(s)> ORDER BY <column>)
Could you post some representative data and some output so we could test it out?
Thanks!
Pieter
September 26, 2016 at 3:25 pm
dimpythewimpy (9/26/2016)
I am trying to implement a while loop and if condition (with a function call) in a case statement. Basically, I am trying to do this:begin
case
when (condition1 and condition 2 and ncolumn_num between 6 and 9) then
while@i<10
begin
if(hsip.Is_numeric( hsip.getTempResponseById(@cregion, @cState_code, @nFY, @nReport_id, @nsection_id, @nquestion_number, @ndisplay_number, @nquestion_number, @nquestion_part_number, @suser_id, @nrow_number, i, @suser_id)))
@nrunningtotal = @nrunningtotal + hsip.getTempResponseById(@cRegion, @cState_code, @nFY, @nReport_id, @nsection_id, @nquestion_number, @ndisplay_number, @nquestion_number,
@nquestion_part_number, @suser_id, @nrow_number, @ncolumn_number, @suser_id)
end if
end
Can I implement while loop and if condition within case statement. My business logic requires me to total the columns from 6 to 9 and have final total in column:10 (I'm in the process of implementing). I have 2 functions: gettempresponsebyid: which returns response string and isnumeric function which converts to number.
Can someone please suggest a better way of doing this. I get this error: INCORRECT SYNTAX NEAR IF AND INCORRECT SYNTAX NEAR RUNNINGTOTAL.
thanks
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).
A CASE expression (not statement) modifies a rowset, which is why it can only be used inside of a DML statement (INSERT/UPDATE/DELETE/MERGE). DML statements are atomic, so you cannot use control-of-flow statements inside of a DML statement. Since CASE expressions can only be used inside of DML, and IF/WHILE statements cannot be used inside of them, you cannot use IF/WHILE statements inside of CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 26, 2016 at 3:27 pm
pietlinden (9/26/2016)
If the reason for your loop is to implement a running total, why not just use a Window function and do something likeSUM( <column to be summed> ) OVER PARTITION BY (<grouping column(s)> ORDER BY <column>)
Could you post some representative data and some output so we could test it out?
Thanks!
Pieter
If you use this approach, you should specify the window frame (generally ROWS UNBOUNDED PRECEDING), because the default is RANGE UNBOUNDED PRECEDING, and which is much slower than specifying the range using the ROWS keyword.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 26, 2016 at 8:21 pm
you should specify the window frame (generally ROWS UNBOUNDED PRECEDING), because the default is RANGE UNBOUNDED PRECEDING, and which is much slower than specifying the range using the ROWS keyword.
Drew
Do you know why that is, Drew?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 27, 2016 at 8:20 am
You are right, the expected output for this procedure is runningtotal which would be runningtotal= runningtotal+numericof(response_string), ( from columns 6 through 9) the response string is the output of the "gettempresponsebyid" function. So, if the user accidentally enters anything other than a numeric value then it returns 0 and calculates the running total. Then, this runningtotal output has to be stored in total_correct column (colno. 10). I think I have to stick to this method because of the way the database is setup.
I am stuck and not sure which approach to follow, I read about cursors, temp-tables etc. But, I am not sure if my scenario fits into those. Please suggest the workaround for this.
Here's the pseudo code:
when(report_id=1 and questionnumber=32 and columnnumber between 6 and 9) then
if(isnumeric(abc.function_toreturn_responsestring(region, state, fy, reportid, questionno) then
runningtotal=runningtotal+to_number(isnull(abc.function_toreturn_responsestring(region, state, fy, reportid, questionno),0);
Thanks,
September 27, 2016 at 8:28 am
The Dixie Flatline (9/26/2016)
you should specify the window frame (generally ROWS UNBOUNDED PRECEDING), because the default is RANGE UNBOUNDED PRECEDING, and which is much slower than specifying the range using the ROWS keyword.
Drew
Do you know why that is, Drew?
It's a matter of the worktable for the spool being in-memory (ROWS) or on-disk (RANGE, or very large frames).
See https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-3-questions-of-performance/ for an investigation of this.
Cheers!
September 27, 2016 at 9:10 am
Jacob Wilkins (9/27/2016)
The Dixie Flatline (9/26/2016)
you should specify the window frame (generally ROWS UNBOUNDED PRECEDING), because the default is RANGE UNBOUNDED PRECEDING, and which is much slower than specifying the range using the ROWS keyword.
Drew
Do you know why that is, Drew?
It's a matter of the worktable for the spool being in-memory (ROWS) or on-disk (RANGE, or very large frames).
See https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-3-questions-of-performance/ for an investigation of this.
Cheers!
Jacob is right, but he left out a detail. To calculate a running sum using ROWS, you only need the previous total and the current amount, so the frame is only ever two records. With RANGE, you're not sure if any other records tie with the current record on the sort, so you don't know how many values you need at each point until you've read all of the records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 27, 2016 at 10:37 am
Thanks Jacob for the link on window functions. However, I was not sure on implementing it in my scenario. I came up with this for runningtotal implementation. The query runs fine, but am I allowed to do this in TSql. Will this loop through between 6 and 9 and put the final total in @ntotalcorrect at column 10. Here's what I came up with. Query runs ok. Thank you!!
if (@nREPORT_ID=1 and @nQUESTION_NUMBER=32 and @nQUESTION_PART_NUMBER=0 and @i between 6 and 10)
begin
set @nrunningtotal = @nrunningtotal + convert(int, hsip.getTempResponseById(@cRegion,@cState_Code,@nFY,@nREPORT_ID, @nSECTION_ID, @nSUBSECTION_ID, @nQUESTION_NUMBER, @nDISPLAY_NUMBER, @nQUESTION_PART_NUMBER, @sUSER_ID, @nROW_NUMBER, @i))
end
if(@i=10)
begin
set @ntotalcorrect = isnull (convert(int, hsip.getTempResponseById(@cRegion,@cState_Code,@nFY,@nREPORT_ID, @nSECTION_ID, @nSUBSECTION_ID, @nQUESTION_NUMBER, @nDISPLAY_NUMBER, @nQUESTION_PART_NUMBER, @sUSER_ID, @nROW_NUMBER, 10)), 0)
end
else
begin
set @ntotalcorrect= 0;
September 27, 2016 at 10:43 am
Also, the reason I was unable to window function from the link above is, it does this:
SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) "Rows" FROM #TMP
basically adding just 2 columns but in my case i have multiple columns atleast 4 columns to add and a separate column to store result.
Do you know if I can implement the same using window function approach. If so, can you please provide an example. Thanks
September 27, 2016 at 11:37 am
dimpythewimpy (9/27/2016)
Also, the reason I was unable to window function from the link above is, it does this:SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) "Rows" FROM #TMP
basically adding just 2 columns but in my case i have multiple columns atleast 4 columns to add and a separate column to store result.
Do you know if I can implement the same using window function approach. If so, can you please provide an example. Thanks
You're confusing keywords and names. Col2 does not mean two columns, because Col2 is not a keyword. Col2 is the name for a single column. This would have been obvious if you had bothered to actually run the sample code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2016 at 4:00 pm
Hi Drew,
Sorry, for the delay in response. Back to working on this. I ran the sample code using windows functions, but I can't use that approach because I am not adding the range with the column and updating the result.
My result set looks like this:
column_name: column_number: 6 7 8 9 10
column_name: Response string: 1 2 3 5 11
If you observe the response string column 1 2 3 4 has to be added and the result 11 has to stored for column_number =10.
I have developed this code so far: it runs fine, but not updating the column:10 with the sum of col(6, 7, 8, 9)
Code:
if (@nREPORT_ID=1 and @nQUESTION_NUMBER=32 and @nQUESTION_PART_NUMBER=0 and @ncolumn_number between 6 and 10)
begin
while @i<10
set @nrunningtotal = @nrunningtotal + convert(int, hsip.getTempResponseById(@cRegion,@cState_Code,@nFY,@nREPORT_ID, @nSECTION_ID, @nSUBSECTION_ID, @nQUESTION_NUMBER, @nDISPLAY_NUMBER, @nQUESTION_PART_NUMBER, @sUSER_ID, @nROW_NUMBER, @i))
set @i=@i +1;
end
If the above total is correct then I am saving it in save_response procedure which updates the above value.
IF(@ntotalcorrect =1)
BEGIN
IF(hsip.getTempResponseById(@cRegion, @cState_Code, @nFY, @nReport_Id, @nSection_id, @nSubsection_Id, @nQUESTION_NUMBER, @nDISPLAY_NUMBER, @nQUESTION_PART_NUMBER, @suser_id, @nrow_number, @nErrorColumnNumber) is not null)
BEGIN
EXEC hsip.SAVE_RESPONSE
@nReport_id,
@nsection_id,
@nsubsection_id,
@nquestion_number,
@ndisplay_number,
@nquestion_part_number,
@cregion,
@cstate_code,
@nFY,
@nRow_number,
@nErrorColumnNumber,
@suser_id,
NULL;
END
Can you please let me know if this is the right approach. Hope I am clear this time.
Thanks,
Dimpy
October 3, 2016 at 8:10 am
You're overengineering this. Simply ADD the columns
SET Col10 = Col6 + Col7 + Col8 + Col 9
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2016 at 8:21 am
Thanks Drew. I will try and let you know.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply