Informix 4gl Query
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
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