SQL question

[Jonathan Leffler]

Mark Townsend wrote:

>> Unfortunately no other SQL implementation has added that feature
>> so you'll have to implement it in code in you app or in an SPL.

>
> Hmm - well, IBM DB2 HAS implemented recursive common table expressions
> (which IS in the standard and IS potentially a better implementation
> that what we implemented in Oracle many,many years ago). Of course, I
> don't believe that Informix has implemented this yet.

Correct, though as Paul Watson mentioned, both SPL and the Node
Datablade can be used to achieve the same result.

> How's that for truth in advertising ?

Not bad!

My understanding (based on second-hand knowledge and hence subject to
correction) is that the limitations on CONNECT BY PRIOR etc are quite
severe - and the result isn't a relation since the order of the data
is critical. However, even that is arguably better than nothing.

> Recursive CTE's are actually pretty cool - see
> [url]http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1[/url]
> for a discussion of them, and how they relate to Oracle's CONNECT BY
> capabilities.

Or [url]http://tinyurl.com/tux0[/url]


--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

[Paul Watson]

I should have added with the SPL approach you must add a depth gauge,
AFAIK the engine should stop recursion at a depth of 32, wouldn't
know for sure as the instance always goes before that. We hardcode
a depth of 10 in our recursive SPL.

Jonathan Leffler wrote:

>
> Mark Townsend wrote:

> >> Unfortunately no other SQL implementation has added that feature
> >> so you'll have to implement it in code in you app or in an SPL.

> >
> > Hmm - well, IBM DB2 HAS implemented recursive common table expressions
> > (which IS in the standard and IS potentially a better implementation
> > that what we implemented in Oracle many,many years ago). Of course, I
> > don't believe that Informix has implemented this yet.

>
> Correct, though as Paul Watson mentioned, both SPL and the Node
> Datablade can be used to achieve the same result.
>

> > How's that for truth in advertising ?

>
> Not bad!
>
> My understanding (based on second-hand knowledge and hence subject to
> correction) is that the limitations on CONNECT BY PRIOR etc are quite
> severe - and the result isn't a relation since the order of the data
> is critical. However, even that is arguably better than nothing.
>

> > Recursive CTE's are actually pretty cool - see
> > [url]http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1[/url]
> > for a discussion of them, and how they relate to Oracle's CONNECT BY
> > capabilities.

>
> Or [url]http://tinyurl.com/tux0[/url]
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
> Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #

[Colin Bull]

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
> ....

At the risk of making myself look silly...
This is the common Bill of Materials, or Foreign Key constraint problem.
I am certain it can only be done with temp tables or perhaps Stored
Procedures.

The way I have tackled this in the past is ...

select from tableX id, parent_id , 0 rank_no into temp ranking ;

update ranking set rank_no = 1 where parent_id is null or = "" ;

select id from ranking where rank_no = 0 and parent_id in (
select id from ranking where rank_no <> 0 )
into temp temp2 ;
update ranking set rank_no = 2 where id in ( select id from temp2 ) ;

select id from ranking where rank_no = 0 and parent_id in (
select id from ranking where rank_no <> 0 )
into temp temp3 ;
update ranking set rank_no = 3 where id in ( select id from temp3 ) ;

..
..
..
and repeat for the maximum number of hierarchies .....

You now have a table with the level of each ID in so...

select spaces ( b.rank_no * 4 ), a.id from tableX a, ranking b
where a.id = b.id
order by rank_no, a.id

( doubt if this syntax is correct as I have not tested it )


Colin Bull
[email]c.bull@videonetworks.com[/email]

sending to informix-list

[Mark D. Stock]

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.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

[Obnoxio The Clown]

Mark Townsend wrote:

>

>> Unfortunately no other SQL implementation has added that feature so
>> you'll have
>> to implement it in code in you app or in an SPL.

>
> Hmm - well, IBM DB2 HAS implemented recursive common table expressions
> (which IS in the standard and IS potentially a better implementation
> that what we implemented in Oracle many,many years ago). Of course, I
> don't believe that Informix has implemented this yet.
>
> How's that for truth in advertising ?

Apart from the Node DataBlade, not too bad.

> Recursive CTE's are actually pretty cool - see
>

[url]http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1[/url]

> for a discussion of them, and how they relate to Oracle's CONNECT BY
> capabilities.

--
Ciao,
The Obnoxious One

"Ogni uomo mi guarda come se fossi una testa di cazzo"

[Kapur, Rajesh]

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: [email]informix-list@iiug.org[/email]
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]

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: [email]informix-list@iiug.org[/email]
> 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 #
[url]www.oninit.com[/url] #

[Mark D. Stock]

Paul Watson wrote:

> 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 (??)

Yup. Plus you could raise an exception on exceeding your levels limit,
which would return a 'real' error to your app. I think you should be able
to process more levels, but YMMV.

> "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.

Excellent!

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

[Paul Watson]

The only depth testing I've ever done was back on 7.12 and the limit
was 12 (ish) and the decision was made to set the 'standard' max
at 10. Never got round to testing again. The only time it looked
like I might need to retest was when we setup our cdi search, but we
used the node blade instead

Paul

"Mark D. Stock" wrote:

>
> Paul Watson wrote:

> > 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 (??)

>
> Yup. Plus you could raise an exception on exceeding your levels limit,
> which would return a 'real' error to your app. I think you should be able
> to process more levels, but YMMV.
>

> > "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.

>
> Excellent!
>
> Cheers,
> --
> Mark.
>
> +----------------------------------------------------------+-----------+
> | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
> | Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
> | +-----------------------------------+//// / ///|
> | |We value your comments, which have |/// / ////|
> | |been recorded and automatically |// / /////|
> | |emailed back to us for our records.|/ ////////|
> +----------------------+-----------------------------------+-----------+
>
> 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 #
[url]www.oninit.com[/url] #

[Mike Greider]

If the column type is varchar, you could try something like this ...

SELECT test2
FROM test
ORDER BY CAST(LEFT(test2,3) AS FLOAT)

Not the most graceful, but that might work.

[talamenne]

thanks so much. thank you

[Mike Greider]

No problem. As I said, it's not the most graceful solution, nor does it scale very well (once you start getting in the hundreds or thousands before the first decimal). But if it works, great!

[talamenne]

1.2.36
11.2.9
2.36
22
22.23.3
3.6.8
3.ii
33.0
33.3.2.a.1.i
33.65.3
44.2
3b

if my data is these. Then it wont work? do you know why?
thanks

[philh]

talamenne, SQL is correctly sorting the field that houses this data, because
it's character data. That's why you see '10' come before '2' in a sort;
they're characters, not numbers. There's no single data type that can house
the information you want to sort. You're going to have to redesign the way that
you store chapter/section/subsection information. I don't understand the
heirarchy, anyway; you have 33.0 and 3.ii, which really don't fit into the
same notation scheme. Good luck.

[lallous]

Hello list,

I have three tables:

mailinglist(listid, name)
subscribers(userid, listid)
users(userid, username)

A given user can belong to multiple mailing lists and his membership is
defined by an entry in subscribers table.

How can I write a 3.23 compatible MySQL statement to list all mailing lists
names and a field that designates whether a user with a given ID is
subscribed to it or not?

Currently, I am doing two queries then glueing them through a PHP script.

Regards,
Elias

[Bill Karwin]

"lallous" <lallous@lgwm.org> wrote in message
news:444lnsFcecpU1@individual.net...

> How can I write a 3.23 compatible MySQL statement to list all mailing
> lists names and a field that designates whether a user with a given ID is
> subscribed to it or not?

I think something like this should work:

SELECT L.name, U.username, IF(S.userid IS NULL, 'NO', 'YES') AS
is_subscribed
FROM mailinglist AS L
LEFT OUTER JOIN subscribers AS S ON (L.listid = S.listid)
RIGHT OUTER JOIN users AS U ON (U.userid = S.userid);

Regards,
Bill K.

[Simon]

mailinglist(listid, name)
subscribers(userid, listid)
users(userid, username)

SELECT ml.name from mailinglist ml,subscribers s, users u
WHERE u.userid=s.userid
AND ml.listid = s.listid
AND u.userid = $user_id

pass the $user_id for the given userid and you will get all mailing list
names that the user has subscribed to

"lallous" <lallous@lgwm.org> wrote in message
news:444lnsFcecpU1@individual.net...

> Hello list,
>
> I have three tables:
>
> mailinglist(listid, name)
> subscribers(userid, listid)
> users(userid, username)
>
> A given user can belong to multiple mailing lists and his membership is
> defined by an entry in subscribers table.
>
> How can I write a 3.23 compatible MySQL statement to list all mailing

lists

> names and a field that designates whether a user with a given ID is
> subscribed to it or not?
>
> Currently, I am doing two queries then glueing them through a PHP script.
>
> Regards,
> Elias
>
>