Re: Re: Informix 4gl Query

Very similar to the informix solutino posted. -----Original Message----- From: "Doug Lawry" <lawry@nildram.co.uk> To: informix-list@iiug.org Date: Mon, 13 Sep 2004 14:30:45 +0100 Subject: Re: Informix 4gl Query "Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:cht6tk$c7k$1@news.xmission.com... > How do you acomplish this in SQL Server? You asked for it, Jean :-) CREATE PROCEDURE consolidate_text AS BEGIN DECLARE @last_id INTEGER DECLARE @this_id INTEGER DECLARE @line_no INTEGER DECLARE @old_text VARCHAR(255) DECLARE @new_text VARCHAR(255) SET @last_id = NULL SET @new_text = NULL SET NOCOUNT ON DECLARE get_data CURSOR LOCAL FORWARD_ONLY FOR SELECT customer_id, text_line_number, LTRIM(RTRIM(text_info)) FROM old_table WHERE LEN(LTRIM(RTRIM(text_info))) > 0 ORDER BY 1, 2 OPEN get_data WHILE 1 = 1 BEGIN FETCH NEXT FROM get_data INTO @this_id, @line_no, @old_text IF @@FETCH_STATUS <> 0 BREAK IF @this_id <> @last_id BEGIN INSERT INTO new_table VALUES (@last_id, @new_text) SET @new_text = NULL END IF @new_text IS NULL SET @new_text = @old_text ELSE SET @new_text = @new_text + ' ' + @old_text SET @last_id = @this_id END CLOSE get_data IF @last_id IS NOT NULL INSERT INTO new_table VALUES (@last_id, @new_text) END I think we can all agree that Informix SPL is neater (see below). -- Regards, Doug Lawry www.douglawry.webhop.org > "Doug Lawry" <lawry@nildram.co.uk> wrote in message > news:chsknb$a9j$1@nntp0.reith.bbc.co.uk... > > > Gary, > > > > The following SQL (or similar 4GL) should do it: > > > > CREATE PROCEDURE consolidate_text()> > > > DEFINE last_id INTEGER; > > DEFINE this_id INTEGER; > > DEFINE line_no INTEGER; > > DEFINE old_text VARCHAR(255); > > DEFINE new_text VARCHAR(255); > > > > LET last_id = NULL; > > LET new_text = NULL; > > > > FOREACH > > > > SELECT customer_id, text_line_number, TRIM(text_info) > > INTO this_id, line_no, old_text > > FROM old_table > > WHERE LENGTH(TRIM(text_info)) > 0 > > ORDER BY 1, 2> > > > IF this_id != last_id THEN > > INSERT INTO new_table VALUES (last_id, new_text);> > LET new_text = NULL; > > END IF > > > > IF new_text IS NULL THEN > > LET new_text = old_text; > > ELSE > > LET new_text = new_text || ' ' || old_text; > > END IF > > > > LET last_id = this_id; > > > > END FOREACH > > > > IF last_id IS NOT NULL THEN > > INSERT INTO new_table VALUES (last_id, new_text);> > END IF > > > > END PROCEDURE; > > > > EXECUTE PROCEDURE consolidate_text();> > > > Use LVARCHAR if you are using version 9 and need more than 255 bytes. > > > > -- > > Regards, > > Doug Lawry > > www.douglawry.webhop.org > > > > > > "Gary Cobden" <gary.cobden@nhs.net> wrote in message > > news:4a40e4ac.0409100657.1ca9a1d6@posting.google.com... > > > > > Whilst I have a MS SQL background, I need assistance with the > > > following please:- > > > > > > I have a table with the following structure:- > > > customer_id; text_line_number; text_info > > > > > > Each customer_id can have several records, each with a different > > > text_line_number > > > > > > I need to consolidate all the records in a new table so that there is > > > one record for each customer_id and the text_info fields are > > > consolidated > > > eg:- > > > customer_id 7777; > > > text_line_number 1; > > > text_info abc; > > > > > > customer_id 7777; > > > text_line_number 2; > > > text_info def; > > > > > > becomes - in the consolidated record > > > customer_id 7777; > > > text_info abc def; > > > > > > Any assistance would be appreciated > > > > > > Thanks > > > > > > Gary Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list