+ Reply to Thread
Results 1 to 4 of 4

[Info-ingres] case statement

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


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


  3. Re: case statement

    "--CELKO--" wrote in message
    news:1141828129.551446.224880@j33g2000cwa.googlegroups.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 has
    permit_a status of and permit_b status of and ... and
    permit_d status of ".

    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



  4. 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 to Thread