DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Rollup and Views - STRANGE

  1. #1
    Join Date
    Feb 2001
    Posts
    28

    Angry

    Hi,

    we migrated recently from Oracle 8.1.6 to 8.1.7. Since then I am having troubles with subtotals in rollup when using WHERE clause for views:

    select
    p.projectType projectType,
    p.regionid regionId,
    count(c.clientId) NoOfClient
    from
    project p, client c
    where
    p.projectId=c.projectId AND p.regionId=2
    group by
    rollup (
    projectType,
    p.regionId)

    rerurns:

    PROJECTTYPE REGIONID NOOFCLIENTS
    ------------ -------- -----------
    AEDO 2 147
    AEDO 147
    NCAP 2 375
    NCAP 375
    522

    Which is correct, but when I move query into view TEST_01 without p.region=2 in WHERE clause and running:

    SELECT * FROM TEST_01 WHERE p.region=2

    I got just:

    PROJECTTYPE REGIONID NOOFCLIENT
    ----------- -------- ----------
    AEDO 2 147
    NCAP 2 375

    Grand total is missing ! It wans't doing in Oracle 8.1.6. it was Ok there. Any idea please ? Scratching my head as filtering in munerous existing applications doesn't work any longer!

    Thanks, Jan





  2. #2
    Join Date
    Sep 2002
    Posts
    5

    Thumbs down oracle rubbish

    This oracle is real rubbish. we move to 8.1.7 and no filter is works on rollup views. so I have views but i cannot use where clause or if doesnt give same rows as before in 8.1.6.

    Just crazey, small update with oracle, and it all stop working.

    This was develop with 8.1.6 now more then 300 reports didn't work and I still cant find way for it to work. my boss is furious an say must be fault mine.

    I have ask at oracle but nobody does reply for me. I hope some kind person can help me this, I am in big trouble now.

  3. #3
    Join Date
    Sep 2002
    Posts
    4

    Unhappy

    I think I might have the same problem...

    I have a regional sales report, which rolls up by region. This is fine for head office, but now they want to distribute individual reports to the regions.

    When I add a WHERE clause, it sort of works, but I am missing some sub-tal rows that I get when I don't set any criteria.

    If anyone could suggest a work around, I would be real grateful.

  4. #4
    Join Date
    Feb 2001
    Posts
    28
    Yep, no one can say why this works correctly in 8.1.6 but not in 8.1.7.

    Jan

  5. #5
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    This may not help at all... but have you tried wrapping the rollup view in another view first?

    eg:

    1. create the rollup view

    2. create second view which gets all fields from first view

    3. try filtering against this second view.

    Sorry, I've no idea if this works, just a wild guess!
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You are probably hitting Oracle bug no.2207566 ("PREDICATE IS NOT PUSHED DOWN INTO VIEW WITH GROUP BY ROLLUP CLAUSE "). It affects 8.1.7, fixed in 9.0.1. If you have Oracle support contract you can check details yourself on Metalink.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2002
    Posts
    4
    As migrating to v9.0.1 is not an option for us presently, can you suggest any way we could circumvent this bug please?

  8. #8
    Join Date
    Feb 2001
    Posts
    28
    Thanks Jurij,

    just wonder if there is some option without upgrading to v 9.

    Jan

    Ps: If you need to upgrade over and over
    ... skydiving without parachute is just the right sport for you.


  9. #9
    Join Date
    Sep 2002
    Posts
    5
    Sound just like Micro$soft. we dont give bug fix for this version, you must upgrade upgrade upgrade. and next upgrade have new bugs.

    Now how to tell my boss we have pay oracle dba for upgrade again? And we spend such money with Oracle just to play upgrade game? Most bad for us company not so big to have ful time oracle experts all the time.

    Most developer I know sick to spend such time and money allways to upgrade. Must be better we look at open sorce, maybe has bugs too, but why pay big money for bugs allways????

  10. #10
    Join Date
    Sep 2002
    Posts
    5
    jan maybe I come skydiving with you and no parashute, better that to tell my boss bad news again.... :(

Posting Permissions

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


Click Here to Expand Forum to Full Width