Loop Help
-
Please look at my code and table. The code is perfect but the problem I harcoded lineNumber 1 2 3 and 4. What I need is after every 4th record in info.txt i need to start a newline, that is 1234, then next line 5678, then 9,10,11,12. i don't want to hardcode this because i have up to 200000 records. The other thing is account for e.g is 33743 has 20 records and 33744 has 15 records, that is not a train smash because i will insert nulls. My little thinking is i need to use and array or some kind of Loop to do this. Please help
TableA
StoryID LineNumber Text
----------- ----------- --------------------------------------------------------------------------------
33743 1 GRT
33743 2 Growthpoint - Audited Results For The Year Ended 30 June 2004
33743 3 GROWTHPOINT PROPERTIES LIMITED
33743 4 (Registration number 1987/004988/06)
33743 5 Share code GRT ISIN: ZAE000037669
33743 6 ("Growthpoint" or "the company")
33743 7 * 3,7% increase in distribution * market capitalisation
33743 8 to 69,0 cents in excess of R3,7 billion
33743 9 * property assets exceed * largest SA company listed in
33743 10 R6,6 billion Real Estate sector of the JSE
33743 11 * improved liquidity and tradeability * vacancies down to 4,7%
33743 12 AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004
33743 13 CONDENSED CONSOLIDATED INCOME STATEMENT
33743 14 2004 2003
33743 15 R"000 R"000
33743 16 Revenue 920 457 452 982
33743 17 Property expenses (314 141) (158 775)
33743 18 Net property income 606 316 294 207
33743 19 Other operating expenses (34 887) (13 533)
33743 20 Net property income after other operating expenses 571 429 280 674
33744 1 GRT
33744 2 Growthpoint - Audited Results For The Year Ended 30 June 2004
33744 3 GROWTHPOINT PROPERTIES LIMITED
33744 4 (Registration number 1987/004988/06)
33744 5 Share code GRT ISIN: ZAE000037669
33744 6 ("Growthpoint" or "the company")
33744 7 * 3,7% increase in distribution * market capitalisation
33744 8 to 69,0 cents in excess of R3,7 billion
33744 9 * property assets exceed * largest SA company listed in
33744 10 R6,6 billion Real Estate sector of the JSE
33744 11 * improved liquidity and tradeability * vacancies down to 4,7%
33744 12 AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004
33744 13 CONDENSED CONSOLIDATED INCOME STATEMENT
33744 14 2004 2003
33744 15 R"000 R"000
DECLARE @storyid int
DECLARE test_cursor CURSOR FOR
SELECT distinct storyid FROM TableA
OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @storyid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Maggy(id,AnnouncementMessageText1, AnnouncementMessageText2, AnnouncementMessageText3, AnnouncementMessageText4)
select @storyid,'AnnouncementMessageText1' = (SELECT Text FROM TableA WHERE StoryID = @storyid AND LineNumber = 1),
'AnnouncementMessageText2'=(SELECT Text FROM TableA WHERE StoryID = @storyid AND LineNumber = 2),
'AnnouncementMessageText3'=(SELECT Text FROM TableA WHERE StoryID = @storyid AND LineNumber = 3),
'AnnouncementMessageText4'=(SELECT Text FROM TableA WHERE StoryID = @storyid AND LineNumber = 4)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM test_cursor
INTO @storyid
END
CLOSE test_cursor
DEALLOCATE test_cursor
GO
-
This code might help you get close to what you want. All you will need to do is INSERT new line after every account is done
DECLARE @iCnt INTEGER
DECLARE @Loop INTEGER
DECLARE @iNew INTEGER
SET @iCnt = 15
SET @Loop = 1
SET @iNew = 0
WHILE @Loop <= @iCnt
BEGIN
PRINT @Loop
SET @iNew = @iNew + 1
IF @iNew = 4 AND @Loop < @iCnt
BEGIN
PRINT 'New Line here'
SET @iNew = 0
END
SET @Loop = @Loop + 1
END
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Here is a set based solution.
Using derived tables and full join.
/rockmoose
create table #stories(
StoryID int not null,
LineNumber int not null,
Text varchar(8000),
primary key(StoryID,LineNumber) )
insert #stories( StoryId, LineNumber, Text )
select 33743, 1, 'GRT'
union all select 33743, 2, 'Growthpoint - Audited Results For The Year Ended 30 June 2004'
union all select 33743, 3, 'GROWTHPOINT PROPERTIES LIMITED'
union all select 33743, 4, '(Registration number 1987/004988/06)'
union all select 33743, 5, 'Share code GRT ISIN: ZAE000037669'
union all select 33743, 6, '("Growthpoint" or "the company")'
union all select 33743, 7, '* 3,7% increase in distribution * market capitalisation'
union all select 33743, 8, ' to 69,0 cents in excess of R3,7 billion'
union all select 33743, 9, '* property assets exceed * largest SA company listed in'
union all select 33743, 10, ' R6,6 billion Real Estate sector of the JSE'
union all select 33743, 11, '* improved liquidity and tradeability * vacancies down to 4,7%'
union all select 33743, 12, ' AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004'
union all select 33743, 13, 'CONDENSED CONSOLIDATED INCOME STATEMENT'
union all select 33743, 14, ' 2004 2003'
union all select 33743, 15, ' R"000 R"000'
union all select 33743, 16, 'Revenue 920 457 452 982'
union all select 33743, 17, 'Property expenses (314 141) (158 775)'
union all select 33743, 18, 'Net property income 606 316 294 207'
union all select 33743, 19, 'Other operating expenses (34 887) (13 533)'
union all select 33743, 20, 'Net property income after other operating expenses 571 429 280 674'
union all select 33744, 1, 'GRT'
union all select 33744, 2, 'Growthpoint - Audited Results For The Year Ended 30 June 2004'
union all select 33744, 3, 'GROWTHPOINT PROPERTIES LIMITED'
union all select 33744, 4, '(Registration number 1987/004988/06)'
union all select 33744, 5, 'Share code GRT ISIN: ZAE000037669'
union all select 33744, 6, '("Growthpoint" or "the company")'
union all select 33744, 7, '* 3,7% increase in distribution * market capitalisation'
union all select 33744, 8, ' to 69,0 cents in excess of R3,7 billion'
union all select 33744, 9, '* property assets exceed * largest SA company listed in'
union all select 33744, 10, ' R6,6 billion Real Estate sector of the JSE'
union all select 33744, 11, '* improved liquidity and tradeability * vacancies down to 4,7%'
union all select 33744, 12, 'AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004'
union all select 33744, 13, 'CONDENSED CONSOLIDATED INCOME STATEMENT'
union all select 33744, 14, ' 2004 2003'
union all select 33744, 15, ' R"000 R"000'
select
coalesce( a.StoryID, b.StoryID ) as StoryID,
coalesce( a.NewLineNumber, b.LineNumber ) as NewLineNumber,
coalesce( a.Text, b.Text ) as Text
from
( select StoryID, LineNumber + (LineNumber-1)/4 as NewLineNumber, Text from #stories ) a
full join
( select StoryId, LineNumber, null as Text from #stories ) b
on a.StoryID = b.StoryID
and a.NewLineNumber = b.LineNumber
order by 1, 2
drop table #stories
/* Resultset */
StoryID NewLineNumber Text
----------- ------------- ----------------------------------------------------------------------------------------------------
33743 1 GRT
33743 2 Growthpoint - Audited Results For The Year Ended 30 June 2004
33743 3 GROWTHPOINT PROPERTIES LIMITED
33743 4 (Registration number 1987/004988/06)
33743 5 NULL
33743 6 Share code GRT ISIN: ZAE000037669
33743 7 ("Growthpoint" or "the company")
33743 8 * 3,7% increase in distribution * market capitalisation
33743 9 to 69,0 cents in excess of R3,7 billion
33743 10 NULL
33743 11 * property assets exceed * largest SA company listed in
33743 12 R6,6 billion Real Estate sector of the JSE
33743 13 * improved liquidity and tradeability * vacancies down to 4,7%
33743 14 AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004
33743 15 NULL
33743 16 CONDENSED CONSOLIDATED INCOME STATEMENT
33743 17 2004 2003
33743 18 R"000 R"000
33743 19 Revenue 920 457 452 982
33743 20 NULL
33743 21 Property expenses (314 141) (158 775)
33743 22 Net property income 606 316 294 207
33743 23 Other operating expenses (34 887) (13 533)
33743 24 Net property income after other operating expenses 571 429 280 674
33744 1 GRT
33744 2 Growthpoint - Audited Results For The Year Ended 30 June 2004
33744 3 GROWTHPOINT PROPERTIES LIMITED
33744 4 (Registration number 1987/004988/06)
33744 5 NULL
33744 6 Share code GRT ISIN: ZAE000037669
33744 7 ("Growthpoint" or "the company")
33744 8 * 3,7% increase in distribution * market capitalisation
33744 9 to 69,0 cents in excess of R3,7 billion
33744 10 NULL
33744 11 * property assets exceed * largest SA company listed in
33744 12 R6,6 billion Real Estate sector of the JSE
33744 13 * improved liquidity and tradeability * vacancies down to 4,7%
33744 14 AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004
33744 15 NULL
33744 16 CONDENSED CONSOLIDATED INCOME STATEMENT
33744 17 2004 2003
33744 18 R"000 R"000
You must unlearn what You have learnt -
How do you format code in this forum !?
Not a html programmer 😉
/rockmoose
You must unlearn what You have learnt -
rockmoose,
Your guess is as good as mine... I do all my code in QA, other places than paste...
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply