Login Register Actian.com  

Actian Community Forum


Go Back   Actian Community Forums > Ingres Forums > Comp.Databases.Ingres
 

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2006-03-07   #1 (permalink)
martin.bowes
Guest
 
Posts: n/a
Default [Info-ingres] case statement

Hi Everyone,

I've just come back from OZ (23hours of fun and frivolity in Cattle
Class) and have a serious case of JetLag so my brains are mush at the
moment...

I have a table with two columns: username, permit_name.
A user may have multiple permissions.

I'd like to print this out with a single username and all the permissions
ranged behind them. I thought the simple way would be with a case
statement.,

select username,
case when permit_name='permit_a' then 'Y' end as permit_a,
case when permit_name='permit_b' then 'Y' end as permit_b,
case when permit_name='permit_c' then 'Y' end as permit_c,
case when permit_name='permit_d' then 'Y' end as permit_d
from t_init


Which gives me:
lqqqqqqqqqqqqqqqqqqqqwqqqqqqwqqqqqqwqqqqqqwqqqqqqk
xusername xpermitxpermitxpermitxpermitx
tqqqqqqqqqqqqqqqqqqqqnqqqqqqnqqqqqqnqqqqqqnqqqqqqu
xtom xY x x x x
xtom x xY x x x
xtom x x x xY x
xdick x x xY x x
xdick x x x xY x
xharry x x xY x x
xharry x x x xY x
xharry xY x x x x
mqqqqqqqqqqqqqqqqqqqqvqqqqqqvqqqqqqvqqqqqqvqqqqqqj

Which is pretty close to what I want except that there are multiple rows
per username.

I added a group by username to the above query but this generates an
error:
E_US0B63 line 1, The columns in the SELECT clause must be
contained in the GROUP BY clause.

Anyone got any ideas how to get this down to a single line per
username?

Marty

  Reply With Quote
Old 2006-03-08   #2 (permalink)
--CELKO--
Guest
 
Posts: n/a
Default Re: case statement

>> Anyone got any ideas how to get this down to a single line per username? <<

Do not write code this way. Remember 1NF? Display is the job of the
front end, never the database.

  Reply With Quote
Old 2006-03-08   #3 (permalink)
Roy Hann
Guest
 
Posts: n/a
Default Re: case statement

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1141828129.551446.224880@j33g2000cwa.googlegr oups.com...
> >> Anyone got any ideas how to get this down to a single line per

username? <<
>
> Do not write code this way. Remember 1NF? Display is the job of the
> front end, never the database.


AFAIK 1NF just says the RDBMS will treat the value of each attribute *as if*
it is atomic. Tables are 1NF just by definition.

Marty's result table is in fact 5NF if the permissions are orthogonal and he
says the external predicate is "The user identified by <username> has
permit_a status of <permit_a> and permit_b status of <permit_b> and ... and
permit_d status of <permit_d>".

However, hair-splitting aside, I reluctantly agree that given his original
table design this looks like a presentation problem that--in general--the
front-end should normally handle. :-)

Roy


  Reply With Quote
Old 2006-03-15   #4 (permalink)
gorlanova
Guest
 
Posts: n/a
Default Re: case statement


martin.bowes@ctsu.ox.ac.uk wrote:
> Anyone got any ideas how to get this down to a single line per
> username?
>
> Marty


May be something like this (using several table copies) :

select distinct up.username
,ifnull(up_a.permit_name,'') as permit_a
,ifnull(up_b.permit_name,'') as permit_b
,ifnull(up_c.permit_name,'') as permit_c
,ifnull(up_d.permit_name,'') as permit_d
from t_init up
left join t_init up_a on up.username=up_a.username and
up_a.permit_name='permit_a'
left join t_init up_b on up.username=up_b.username and
up_b.permit_name='permit_b'
left join t_init up_c on up.username=up_c.username and
up_c.permit_name='permit_c'
left join t_init up_d on up.username=up_d.username and
up_d.permit_name='permit_d'

  Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved