April 23, 2012 at 2:03 pm
riya_dave (4/23/2012)
ok.this is simple question but nee dhelp.i need something like untill there is value the loop should go.
i cant give any condition
suppose files
item1
item2
item3
the loop should work untill there is value
how to do that
I'm sorry, I don't understand the question at all.
You want a WHILE loop, but you can't provide a condition? That's the definition of an infinite loop. There has to be a conditional check of some kind.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2012 at 2:09 pm
ok.
but i like something like this,for each row return by query the loop should run.while there is row in the table
that is the condition i need ,i dont know what to put
example
item
item1
item2
item3
condition(while there is item)
how to do that
April 23, 2012 at 2:14 pm
Sounds like what you want is a cursor. Read up on those here.
But, please note, that's a very problematic way of dealing with data within SQL Server. Performance is notoriously bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2012 at 2:18 pm
I AM ALREADY PUTTING IT INSIDE CURSOR BUT ITS REPEATING AGAIN AND AGAIN
April 23, 2012 at 2:23 pm
riya_dave (4/23/2012)
I AM ALREADY PUTTING IT INSIDE CURSOR BUT ITS REPEATING AGAIN AND AGAIN
those of us who know the threads know you are putting what ever you are trying to do in a cursor. so no reason to yell at us. the reason its repeating is somewhere in your code you have an infinite loop or malformed cursor syntax not getting the next item in the cursor. instead of yelling how about taking the time to post useful code and DDL according to the link in my signature to Jeff Moden's article on how we like to see things here. if you would have started with that we most likely would have had this solved by now.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 2:29 pm
HERE ISMY CODE
declare @Portfolio nvarchar(32)
declare @mtd float
DECLARE @qtd float
declare @Portfoliobaseid int
declare @ytd float
declare c1 CURSOR FOR
select account, Portfoliobaseid from #temp1
open c1
FETCH NEXT FROM c1 INTO @Portfolio,@Portfoliobaseid
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd is null)
begin
declare @mtd1 float
EXEC ..pPerformance
@ReportData = @ReportData4 out,
@Portfolios = @Portfolio,
@FromDate = @fromMTD,
@ToDate = @ToDate,
@ClassificationID = -9
select @FromDate as maxmonthdate
select @mtd = IRR from fPerformance(@ReportData4)
select @mtd1 = @mtd
-- declare @ReportData1 varbinary(max)
END
insert into #temp3
select @Portfoliobaseid , @mtd1
if(@ytd is null)
begin
declare @ytd1 float
declare @maxdate datetime
select @maxdate = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@ReportData1)
select @maxdate as maxdate
EXEC ..pPerformance
@ReportData = @ReportData4 out,
@Portfolios = @Portfolio,
@FromDate = @maxdate,
@ToDate = @ToDate,
@ClassificationID = -9
select @ytd = IRR from ..fPerformance(@ReportData4)
select @ytd1 = @ytd
insert into #temp4
select portfoliobaseid,IRRReal from fPerformanceHistoryDetail(@ReportData1)ytd
where PeriodFromDate between @FromDate and @ToDate
end
if(@qtd is null)
begin
---------------------------------------------------------
------quatertodate---------------
declare @qtd1 float
declare @maxdate1 datetime
select @maxdate1 = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@ReportData1)
-- select @FromQTD as quatertodate
if(@maxdate1 >= @fromQTD)
begin
select @FromQTD = @maxdate1
end
EXEC ..pPerformance
@ReportData = @ReportData4 out,
@Portfolios = @Portfolio,
@FromDate = @fromQTD,
@ToDate = @ToDate,
@ClassificationID = -9
select @qtd = IRR from ..fPerformance(@ReportData4)
select @qtd1 = @qtd
end
insert into #temp5
select portfoliobaseid, IRRReal from fPerformanceHistoryDetail(@ReportData1)Qtd where Qtd .PeriodFromDate between @QTD4 and @ToDate
group by PortfolioBaseID,IRRReal
union all
select @Portfoliobaseid , @qtd1
FETCH NEXT FROM c1 INTO @Portfolio,@Portfoliobaseid
end
close c1
DEALLOCATE c1
TEMP table is not giving me exact row,its repeating 5 times
April 23, 2012 at 2:31 pm
riya_dave (4/23/2012)
I AM ALREADY PUTTING IT INSIDE CURSOR BUT ITS REPEATING AGAIN AND AGAIN
I've no doubt you know this, but I'll point it out, just in case, all caps like that is considered yelling.
I've tried to be helpful and respectful in our communications, but with this, I'm done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2012 at 2:35 pm
riya_dave (4/23/2012)
ok.but i like something like this,for each row return by query the loop should run.while there is row in the table
that is the condition i need ,i dont know what to put
example
item
item1
item2
item3
condition(while there is item)
how to do that
Okay, first off - WHAT are item1, item2, item3, etc? Are these tables? Files? If files, what kind of files? Are they something else?
If they are files, then what you really need is Integration Services. There is a fairly good introduction on how to do this here: but basically what it amounts to is setting up a source that loops through all of the files in a folder that you define - it'll stop when it's done all of them. If you want to make it more complete, you can have it move the files to another folder as it's done with each one.
If they are tables, it's actually a lot easier - you write a query in which you UNION the results together (assuming the columns are the same) and INSERT the results into a table.
If they are "something else" then you need to provide more information.
April 23, 2012 at 2:42 pm
Just from a cursory look at the code you posted (since we can't test it since you refuse to provide us with any useful information that could really benefit you), here is what I see.
The first time through the cursor loop, @mtd, @ytd, and @qtd are null. These get set to some values during the first loop. Subsequesnt loops bypass much of your code as these variables are no longer null.
I hope this helps you figure out your problem.
April 24, 2012 at 1:56 am
Which temp table?
#temp3 will contain one row for each row in #temp1
#temp4 will contain one row
#temp5 will contain one row for each row in #temp1 plus whatever fPerformanceHistoryDetail retrieves
So if it is #temp3 (as I suspect it is) then move the #temp3 insert above the preceding END
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2012 at 2:20 am
I love threads from this OP, they make my day!
They are example of horror, drama and comedy in one!
a la Alfred Hitchcock
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply