the code problem or something else

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

the code problem or something else

Author

Topic

jim65
Starting Member

35 Posts

Posted - 2006-02-15 : 13:26:18

Declare @Frequency_ID int
DECLARE @CNT INT
DECLARE @RptReq_ID int
Declare @Startdate as smalldatetime
Declare @counter as int

select @counter = count(RptReqID) from adReportingRequirement

SET @CNT = 1

while @CNT <@counter
Begin

SELECT @Frequency_ID= [FrequencyID],@RptReq_ID= [RptReqID], @Startdate =[StartDate]
FROM adReportingRequirement
where RptReqID = @CNT

if @Frequency_ID = 1
print @Startdate 'from 1'
if @Frequency_ID = 2
print @Startdate 'from 2'

SET @CNT = @CNT + 1

end

this code is to get the FrequencyID, and Startdate from the adReportingRequirement table.
then into the while loop. if FrequencyID is 1, print start date. the problem is sometime
print two times or three times for one record.

result looks like

4/20/2005
3/06/2005
3/06/2005
3/06/2005

the 3/06/2005 is only from one record. why is printing three times? is this code problem or something else.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-15 : 13:50:37

Posting some sample data from your table would help. Incidentally I think your while loop should read <= not < otherwise you'll miss the last record.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 13:52:14

Is RptReqID the primary key on adReportingRequirement table? If not, is it unique at least?

Tara Kizer
aka tduggan

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-15 : 13:57:06

This loop assumes that RptReqID is sequential. Check for gaps in your RptReqID, you probably need to restructure your loop.

jim65
Starting Member

35 Posts

Posted - 2006-02-15 : 14:01:09

sample date from the adReportingRequirement table

RptReqID EntityID RptReqTypeID FrequencyID Days startDate EndDate, InactiveDate

3 17508 2 1 4 4/20/2005 12/31/2006
4 34084 10 3 7 12/31/2003
5 34084 7 1 6 3/06/2005
8 18753 4 3 6 5/16/2005 5/20/2005

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-15 : 14:08:24

Your loop is based off an arbitrary int (@CNT). As you go through your loop and assign values to your variables based on RptReqID = @CNT. The problem is there are no records with RptReqID = 6 or 7 to change the values of the variables. The variables retain their previous value. That's why you're getting 3/06/2005 printing 3 times.

Just restructure you loop...

jim65
Starting Member

35 Posts

Posted - 2006-02-15 : 14:10:31

yes, Thanks a lot.

jim65
Starting Member

35 Posts

Posted - 2006-02-16 : 10:13:50

I think that just take off where sentence, then will be fine

like

Declare @Frequency_ID int
DECLARE @CNT INT
DECLARE @RptReq_ID int
Declare @Startdate as smalldatetime
Declare @counter as int

select @counter = count(RptReqID) from adReportingRequirement

SET @CNT = 1

while @CNT <@counter
Begin

SELECT @Frequency_ID= [FrequencyID],@RptReq_ID= [RptReqID], @Startdate =[StartDate]
FROM adReportingRequirement
--where RptReqID = @CNT

if @Frequency_ID = 1
print @Startdate 'from 1'
if @Frequency_ID = 2
print @Startdate 'from 2'

SET @CNT = @CNT + 1

end

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-16 : 10:31:25

No it won't.

quote:
while @CNT <@counter

Your loop will run Number of records - 1 times

quote:
SELECT @Frequency_ID= [FrequencyID],@RptReq_ID= [RptReqID], @Startdate =[StartDate]
FROM adReportingRequirement
--where RptReqID = @CNT

This will assign values based on the last record returned to your variables every time.

Try something like this....

DECLARE @RowCnt INT, @FreqID INT, @RptReqID INT, @StartDate SMALLDATETIME

SELECT TOP 1 @FreqID = FrequencyID,
@RptReqID = RptReqID,
@StartDate = StartDate
FROM adReportingRequirement
ORDER BY RptReqID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

insert work done by loop

SELECT TOP 1 @FreqID = FrequencyID,
@RptReqID = RptReqID,
@StartDate = StartDate
FROM adReportingRequirement
WHERE RptReqID > @RptReqID
ORDER BY RptReqID

SET @RowCnt = @@ROWCOUNT

END

jim65
Starting Member

35 Posts

Posted - 2006-02-16 : 11:13:59

You are right,it works. many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources