How to retrieve the default Ingres user group?

Good morning

I have a script which was supposed to retrieve a list of an Ingres
user's groups from iidbdb (which it does), with the default group
first (which it now doesn't). The list of Ingres user groups comes
back in alphabetic order and I can't see how the script would ever
have worked as I had previously understood that it did!

I'm currently using unsupported IngresII 2.5, to which we upgraded 2
years ago.

Does anyone have a clever bit of SQL that retrieves the -defaul_t
Ingres group from iidbdb for a given user, or at least the full list
of groups with default group first?

Thanks, L Jackson.

Here you go

users jim and bob are in both jimgroup and bobgroup. jim has a default group
of jimgroup. All other users, including bob, have no default group.

This query excludes the line from iiusergroup where the user is '' which
would exclude empty groups. You could add that back in with another union
clause or sub-select.

HTH
Paul

SELECT user_name, default_group AS group, 'Y' AS is_default
FROM iiusers
UNION
SELECT groupmem AS user_name,groupid AS group, 'N' AS is_default
FROM iiusergroup
WHERE groupmem != ''
AND groupid NOT IN (SELECT default_group FROM iiusers
WHERE user_name=groupmem)
ORDER BY 1,3 DESC

Executing . . .


+--------------------------------+--------------------------------+------+
|user_name |group |is_def|
+--------------------------------+--------------------------------+------+
|$ingres | |Y |
|aucos | |Y |
|bob | |Y |
|bob |bobgroup |N |
|bob |jimgroup |N |
|decision | |Y |
|ingres | |Y |
|jim |jimgroup |Y |
|jim |bobgroup |N |
|root | |Y |
+--------------------------------+--------------------------------+------+
(10 rows)


2008/8/15

> Good morning
>
> I have a script which was supposed to retrieve a list of an Ingres
> user's groups from iidbdb (which it does), with the default group
> first (which it now doesn't). The list of Ingres user groups comes
> back in alphabetic order and I can't see how the script would ever
> have worked as I had previously understood that it did!
>
> I'm currently using unsupported IngresII 2.5, to which we upgraded 2
> years ago.
>
> Does anyone have a clever bit of SQL that retrieves the -defaul_t
> Ingres group from iidbdb for a given user, or at least the full list
> of groups with default group first?
>
> Thanks, L Jackson.
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres-at-kettleriverconsulting.com
> http://www.kettleriverconsulting.com...fo/info-ingres
>



--
Paul Mason