April 18, 2012 at 3:56 pm
riya_dave (4/18/2012)
declare @P1 nvarchar(32)declare @mtd2 float
declare c1 CURSOR FOR
select account, MTD from #temp2
open c1
FETCH NEXT FROM c1
INTO @P1,@mtd2
WHILE @@FETCH_STATUS = 0
begin
if(@mtd2 IS NULL )
BEGIN
declare @IRR2 float
EXEC pdashboard
select @IRR2 = IRR from pdashboard
SELECT @IRR2 as Last1month
END
ELSE
BEGIN
select @mtd2 as last2month
END
fetch next from c1 into @P1,@MTD2
END
this is fine ???
Rant time....
Hbkpt and riya Dave are asking the same questions as in the aborted forum post from earlier , same stored proc names etc, as in the insert into udf, It is to big or smart to annoy people so much they give up and then change your login. Earlier it was not a case of hbkpt hijacking the forum so much as forgetting to use the correct login.
Either post what is required, either of you, or stop posting as this has gone far enough.
Rant over...sorry everyone else
Fitz
April 19, 2012 at 12:10 pm
this is my complete code,why it snot working for so many rows,if there is only 1 row its working.
otherwise its just getting me last values.everything else is getting null.
declare @P1 nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @ytd float
declare c1 CURSOR FOR
select account,MTDTWR ,QTDTWR,YTDTWR from #temp1
open c1
FETCH NEXT FROM c1 INTO @P1,@mtd2,@qtd,@ytd
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
declare @IRR1 float,
EXEC d1
@P1s = @P1s,
select @IRR1 = IRR from account
SELECT @mtd2 = @IRR1
-------------------------
------last1 year----------
if(@ytd is null)
begin
declare @maxdate datetime
declare @IRR float
select @maxdate = MAX(PeriodThrudate) from account2
EXEC d1
select @IRR = IRR from account
select @ytd = @IRR
SELECT @ytd AS L1y
--fetch next from c1 into @P1,@mtd2,@qtd,@ytd
end
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) VALUES(@P1,@mtd2,@qtd,@ytd)
close c1
DEALLOCATE c1
plz help
April 19, 2012 at 12:15 pm
Honestly I think it is not working because you are in so far over your head you can't even figure out which way is up.
Look at the basic cursor syntax I posted previously, then find a fetch next in the code you posted...oh wait there isn't one is there?
I was sort of hoping that you changing to a new account would have changed your approach to requesting help. Apparently it has not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 12:25 pm
ok.
i change my fetch statement in this code.
still not working
declare @P1 nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @ytd float
declare c1 CURSOR FOR
select account,MTDTWR ,QTDTWR,YTDTWR from #temp1
open c1
FETCH NEXT FROM c1 INTO @P1,@mtd2,@qtd,@ytd
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
declare @IRR1 float,
EXEC d1
@P1s = @P1s,
select @IRR1 = IRR from account
SELECT @mtd2 = @IRR1
-------------------------
------last1 year----------
if(@ytd is null)
begin
declare @maxdate datetime
declare @IRR float
select @maxdate = MAX(PeriodThrudate) from account2
EXEC d1
select @IRR = IRR from account
select @ytd = @IRR
SELECT @ytd AS L1y
end
fetch next from c1 into @P1,@mtd2,@qtd,@ytd
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) VALUES(@P1,@mtd2,@qtd,@ytd)
close c1
DEALLOCATE c1
April 19, 2012 at 12:29 pm
Actually, you have the first (initial) fetch right after you open the cursor. Your next FETCH (which is commented out) does not appear to be in the correct position. It should be just before the end that marks the end of your while loop.
Now, if you have any more questions, please be sure to read that article you have been asked to read until we are all blue in the face. Then follow the instructions in that article regarding what information to post and how to do it. If you can't follow simple instructions like that, then I'm sorry but you aren't going to get very good answers to your poorly worded and documented questions.
April 19, 2012 at 12:31 pm
I see you made the change needed while I was writing my response.
However, everything else stands. If you want better answers, you have to give us more, and that more is detailed in that article that for some reason you refuse to read and/or follow.
April 19, 2012 at 12:32 pm
Really? That won't work? No kidding?
There are more lines with syntax errors in there than there are lines that will actually work. That can't possibly come close to compiling. Your fetch next is still inside a conditional block. There is no end to the original begin.
Try posting ddl and the ACTUAL code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 12:33 pm
riya_dave (4/19/2012)
ok.i change my fetch statement in this code.
still not working
...
Also, none of us knows what "still not working" means as you don't tell us if it errors out (and if so what the error(s) are) or if it simply isn't returning what you expect (and there is nothing we can do there since we have nothing to test what little and incomplete code you have provided).
April 19, 2012 at 12:34 pm
Sean Lange (4/19/2012)
Really? That won't work? No kidding?There are more lines with syntax errors in there than there are lines that will actually work. That can't possibly come close to compiling. Your fetch next is still inside a conditional block. There is no end to the original begin.
Try posting ddl and the ACTUAL code.
Darn, you mean I miscounted BEGIN/END pairs? Oh, my what will I do. :w00t:
April 19, 2012 at 12:34 pm
Here is "code" you posted with formatting so it can be read.
declare @P1 nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @ytd float
declare c1 CURSOR FOR
select account,MTDTWR ,QTDTWR,YTDTWR from #temp1
open c1
FETCH NEXT FROM c1 INTO @P1,@mtd2,@qtd,@ytd
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
--a bunch a gibberish garbage that can't possibly work.
--declare @IRR1 float,
--EXEC d1 @P1s = @P1s,
--select @IRR1 = IRR from account
--SELECT @mtd2 = @IRR1
-------------------------
------last1 year----------
if(@ytd is null)
begin
declare @maxdate datetime
declare @IRR float
select @maxdate = MAX(PeriodThrudate) from account2
EXEC d1
select @IRR = IRR from account
select @ytd = @IRR
SELECT @ytd AS L1y
end
fetch next from c1 into @P1,@mtd2,@qtd,@ytd
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) VALUES(@P1,@mtd2,@qtd,@ytd)
close c1
DEALLOCATE c1
See any issues in there? It seems that you don't want to understand what you are doing. You want to throw enough code at this to make it work for awhile.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 12:35 pm
Lynn Pettis (4/19/2012)
Sean Lange (4/19/2012)
Really? That won't work? No kidding?There are more lines with syntax errors in there than there are lines that will actually work. That can't possibly come close to compiling. Your fetch next is still inside a conditional block. There is no end to the original begin.
Try posting ddl and the ACTUAL code.
Darn, you mean I miscounted BEGIN/END pairs? Oh, my what will I do. :w00t:
LOL!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 12:37 pm
this code should work ,but its not ,
its getting me same row in all the rows and repeating last row 2 times.
declare @P1 nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @ytd float
declare c1 CURSOR FOR
select account,MTDTWR ,QTDTWR,YTDTWR from #temp1
open c1
FETCH NEXT FROM c1 INTO @P1,@mtd2,@qtd,@ytd
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
declare @IRR1 float,
EXEC d1
@P1s = @P1s,
select @IRR1 = IRR from account
SELECT @mtd2 = @IRR1
end
-------------------------
------last1 year----------
if(@ytd is null)
begin
declare @maxdate datetime
declare @IRR float
select @maxdate = MAX(PeriodThrudate) from account2
EXEC d1
select @IRR = IRR from account
select @ytd = @IRR
SELECT @ytd AS L1y
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) VALUES(@P1,@mtd2,@qtd,@ytd)
fetch next from c1 into @P1,@mtd2,@qtd,@ytd
end
close c1
DEALLOCATE c1
April 19, 2012 at 12:43 pm
riya_dave (4/19/2012)
this code should work ,but its not ,its getting me same row in all the rows and repeating last row 2 times.
...
We understand that your code isn't doing what you expect. What you don't seem to understand is there is nothing we can do to help you unless you provide us with more than you have so far. Until we have all the code you are trying to run, the DDL (CREATE TABLE statements) for all the tables involved, sample data to populate those tables, and the expected results based on the sample data, there is nothing more that we can do for you.
We don't have access to anything that would help us help you unless you provide it. You know that article (I know, I'm beating a dead horse), it shows you step by step what and how to post the info we need to help you.
April 19, 2012 at 12:43 pm
'EXEC d1' didn't work for me. whats this d1 supposed to be?
April 19, 2012 at 12:43 pm
--HINT--
You are declaring variables inside your cursor.
Your entire approach to the problem is 100% wrong. When you finally post ddl and sample data along with the desired results in a format that is in line with the article in the first link in my signature you will get the help you so desperately are seeking.
In the meantime keep pedaling, your boss may not yet have figured out the mistake they made.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply