Oninit cdi / Archive
Talk to an expert →

RE: SQL Question

Posted in 2003

Topics: Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL, Data Types & Schema Design, Jobs, Consulting & Announcements

Kapur, Rajesh — — source: Usenet: comp.databases.informix

Mark: Thank you very much.... I was able to find your SPL snippets and customize your code to my needs.... I include the details of what I have done, in case someone else can benefit from it... * The first stored procedure get_hierarchy() extracts the nodes with no parent. (I had to write a separate procedure because I had nulls in the parent_id field for the nodes with no parent. With a little work, we can probably get the same results with a single stored procedure.) * get_hierarchy calls get_child for each node with no parent. get_child recursively prints out all lower levels. * I included the description of each level. * I added a depth indicator and a check not to exceed 10 levels of recursion. * I included indentation of each level, based upon the depth level. * Table description, SPL code and sample output are included. Thanks for your help. - Rajesh +++++++++++++++++++++++++++++++ Table: create table field_type ( field_type_id integer not null , parent_id integer, display_name varchar(255) not null ,++++++++++++++++++++++++++++++ CREATE PROCEDURE get_hierarchy() RETURNING INTEGER, INTEGER, CHAR(30);DEFINE l_level INTEGER; DEFINE l_field_type_id INTEGER; DEFINE l_display_name CHAR(30); DEFINE l_curr_level INTEGER; let l_curr_level = 1; FOREACH SELECT f.field_type_id, f.display_name INTO l_field_type_id, l_display_name FROM field_type f WHERE f.parent_id = 0 OR f.parent_id IS NULL ORDER BY f.field_type_id IF l_field_type_id != 0 THEN RETURN l_curr_level, l_field_type_id, l_display_name WITH RESUME; FOREACH EXECUTE PROCEDURE get_child(l_curr_level, l_field_type_id) INTO l_level, l_field_type_id, l_display_name RETURN l_level, l_field_type_id, l_display_name WITH RESUME; END FOREACH; END IF; END FOREACH; END PROCEDURE; CREATE PROCEDURE get_child(p_level INTEGER, p_parent_id INTEGER) RETURNING INTEGER, INTEGER, CHAR(30);DEFINE l_level INTEGER; DEFINE l_field_type_id INTEGER; DEFINE l_display_name CHAR(30); DEFINE l_curr_level INTEGER; DEFINE l_ctr INTEGER; LET l_field_type_id = 0; LET l_curr_level = p_level + 1; IF l_curr_level > 10 THEN RETURN 999999, 999999, "10 LEVELS EXCEEDED"; END IF; FOREACH SELECT f.field_type_id, f.display_name INTO l_field_type_id, l_display_name FROM field_type f WHERE f.parent_id = p_parent_id ORDER BY f.field_type_id IF l_field_type_id != 0 THEN FOR l_ctr = 1 TO l_curr_level LET l_display_name = ' ' || l_display_name; END FOR; RETURN l_curr_level, l_field_type_id, l_display_name WITH RESUME; FOREACH EXECUTE PROCEDURE get_child(l_curr_level, l_field_type_id) INTO l_level, l_field_type_id, l_display_name RETURN l_level, l_field_type_id, l_display_name WITH RESUME; END FOREACH; END IF; END FOREACH; END PROCEDURE; ++++++++++++++++++++++++++++++++ Sample Output -- (expression) (expression) (expression) 1 157839 Type 2 176991 Resource 2 187911 Form 2 219668 Genre 1 188985 Annotation 2 188982 Record Revision 2 188983 Record Creation -----Original Message----- From: Mark D. Stock [mailto:mdstock@MydasSolutions.com] Sent: Thursday, November 06, 2003 7:39 AM To: Kapur, Rajesh Cc: informix-list@iiug.org Subject: Re: SQL Question Rajesh Kapur wrote: > I have a table where the hierarchy is built by storing the parent_id in the > same table.... > > id, > parent_id > .... other fields > > Can someone suggest an SQL statement that will list the entire hierarchy as > follows > > id1 > id1.1 > id1.2 > id1.2.1 > id1.2.2 > id1.2.3 > id1.2.3.1 > id1.3 > id1.4 > id2 > .... Check the archives. I've posted snippets of SPL code in 1998 & 2000 and possibly before that in 4GL. You can write recursive code in SPL or 4GL, just be careful that you don't descend too many levels. Cheers, -- Mark. sending to informix-list

↪ replying to Kapur, Rajesh
Paul Watson — — source: Usenet: comp.databases.informix

You could merge get_hierachy and get_child into a single SPL, just call the top level get_child with your starting point and a depth of zero. Only one to maintain/document (??) "Kapur, Rajesh" wrote: > > Mark: Thank you very much.... I was able to find your SPL snippets and customize your code to my needs.... I include the details of what I have done, in case someone else can benefit from it... > > * The first stored procedure get_hierarchy() extracts the nodes with no parent. (I had to write a separate procedure because I had nulls in the parent_id field for the nodes with no parent. With a little work, we can probably get the same results with a single stored procedure.) > * get_hierarchy calls get_child for each node with no parent. get_child recursively prints out all lower levels. > * I included the description of each level. > * I added a depth indicator and a check not to exceed 10 levels of recursion. > * I included indentation of each level, based upon the depth level. > * Table description, SPL code and sample output are included. > > Thanks for your help. > - Rajesh > > +++++++++++++++++++++++++++++++ > Table: > create table field_type > ( > field_type_id integer not null , > parent_id integer, > display_name varchar(255) not null ,> ++++++++++++++++++++++++++++++ > CREATE PROCEDURE get_hierarchy() > RETURNING INTEGER, INTEGER, CHAR(30);> DEFINE l_level INTEGER; > DEFINE l_field_type_id INTEGER; > DEFINE l_display_name CHAR(30); > DEFINE l_curr_level INTEGER; > > let l_curr_level = 1; > > FOREACH SELECT f.field_type_id, > f.display_name > INTO l_field_type_id, > l_display_name > FROM field_type f > WHERE f.parent_id = 0 > OR f.parent_id IS NULL > ORDER BY f.field_type_id > > IF l_field_type_id != 0 > THEN > RETURN l_curr_level, > l_field_type_id, > l_display_name WITH RESUME; > > FOREACH EXECUTE PROCEDURE get_child(l_curr_level, l_field_type_id) > INTO l_level, l_field_type_id, l_display_name > RETURN l_level, l_field_type_id, l_display_name > WITH RESUME; > END FOREACH; > END IF; > END FOREACH; > > END PROCEDURE; > CREATE PROCEDURE get_child(p_level INTEGER, p_parent_id INTEGER) > RETURNING INTEGER, INTEGER, CHAR(30);> DEFINE l_level INTEGER; > DEFINE l_field_type_id INTEGER; > DEFINE l_display_name CHAR(30); > DEFINE l_curr_level INTEGER; > > DEFINE l_ctr INTEGER; > > LET l_field_type_id = 0; > LET l_curr_level = p_level + 1; > > IF l_curr_level > 10 > THEN > RETURN 999999, > 999999, > "10 LEVELS EXCEEDED"; > END IF; > > FOREACH SELECT f.field_type_id, > f.display_name > INTO l_field_type_id, > l_display_name > FROM field_type f > WHERE f.parent_id = p_parent_id > ORDER BY f.field_type_id > > IF l_field_type_id != 0 > THEN > FOR l_ctr = 1 TO l_curr_level > LET l_display_name = ' ' || l_display_name; > END FOR; > RETURN l_curr_level, > l_field_type_id, > l_display_name WITH RESUME; > FOREACH EXECUTE PROCEDURE get_child(l_curr_level, l_field_type_id) > INTO l_level, l_field_type_id, l_display_name > RETURN l_level, > l_field_type_id, > l_display_name WITH RESUME; > END FOREACH; > END IF; > END FOREACH; > > END PROCEDURE; > ++++++++++++++++++++++++++++++++ > Sample Output -- > > (expression) (expression) (expression) > > 1 157839 Type > 2 176991 Resource > 2 187911 Form > 2 219668 Genre > 1 188985 Annotation > 2 188982 Record Revision > 2 188983 Record Creation > > -----Original Message----- > From: Mark D. Stock [mailto:mdstock@MydasSolutions.com] > Sent: Thursday, November 06, 2003 7:39 AM > To: Kapur, Rajesh > Cc: informix-list@iiug.org > Subject: Re: SQL Question > > Rajesh Kapur wrote: > > > I have a table where the hierarchy is built by storing the parent_id in the > > same table.... > > > > id, > > parent_id > > .... other fields > > > > Can someone suggest an SQL statement that will list the entire hierarchy as > > follows > > > > id1 > > id1.1 > > id1.2 > > id1.2.1 > > id1.2.2 > > id1.2.3 > > id1.2.3.1 > > id1.3 > > id1.4 > > id2 > > .... > > Check the archives. I've posted snippets of SPL code in 1998 & 2000 and > possibly before that in 4GL. > > You can write recursive code in SPL or 4GL, just be careful that you don't > descend too many levels. > > Cheers, > -- > Mark. > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Glossary