Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: Roll Up Causing some Problems

    Hi friends,
    i've a simple query like this that gives me the total of the currencies i.e(CAD,USD) of all the users.
    select f.currency,sum(cast(f.balance as bigint)) as amount,f.id from Finance as f where f.currency in ('CAD','USD') group by f.currency,rollup(f.user_id) order by f.currency ASC.

    I want no changes to my above query except I want to add one more field "name" from table "Names" and the join condition should be f.id = names.id.

    Can someone please tell me how to accomplish this???Thanking you in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How about posting the DDL for both tables? Thanks bud.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2005
    Posts
    18
    Hi Marcus,
    Unfortunately i don't have access to the DDL's for both tables.All I know is the "id" links both these tables,and i need to add the "name" field to my existing Query.As,I am using roll up(first time),when i try to add the "name" field,all my data gets messed up.Right now I've something like this:

    CURRENCY AMOUNT ID
    CAD--------1000------
    CAD---------500------1
    CAD---------100------2
    CAD---------400------3

    USD---------500-------
    USD---------100------1
    USD---------200------2
    USD---------200------3

    This is exactly what i need,just want to add the "name" field after "id",can i???

    CURRENCY AMOUNT ID NAME
    CAD--------1000---------------
    CAD---------500------1---XXX
    CAD---------100------2---YYY
    CAD---------400------3---ZZZ

    USD---------500---------------
    USD---------100------1---XXX
    USD---------200------2---YYY
    USD---------200------3---ZZZ

    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    OK, let's first format the SQL so we can read it (http://www.sqlinform.com/)

    SELECT
    f.currency,
    sum(cast(f.balance AS bigint)) AS amount,
    f.id
    FROM Finance AS f
    WHERE f.currency in ('CAD','USD')
    GROUP BY f.currency,
    rollup(f.user_id)
    ORDER BY f.currency ASC.

    The ORDER BY is probably not necessary since it is the first column in the GROUP BY. DB2 sorts the rows in order of the GROUP BY.

    I am not an expert on ROLLUP, but did you try this:

    SELECT
    f.currency,
    sum(cast(f.balance AS bigint)) AS amount,
    f.id,
    n.name
    FROM Finance AS f,
    Names AS n
    WHERE f.currency in ('CAD','USD')
    AND f.id = n.id
    GROUP BY f.currency,
    rollup(f.user_id, n.name)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2005
    Posts
    18
    Thanks Marcus,
    Again Unfortunately i've tried that too and it didn't worked.I think i've to use some programming logic to get that "name" field.Anyways Thanks for your help.

  6. #6
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Untested so it might need some tweaking:

    select f.currency,
    sum(cast(f.balance as bigint)) as amount,
    f.id,
    case when grouping(f.user_id) = 0
    then (select name from names as n
    where n.id = f.id)
    else char(null)
    end
    from Finance as f
    where f.currency in ('CAD','USD')
    group by f.currency,
    rollup(f.user_id)
    order by f.currency ASC

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You could try to concatenate the id and name column together with a space or dash between them:

    Select SELECT
    f.currency,
    sum(cast(f.balance AS bigint)) AS amount,
    f.id ||' '|| n.name
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •