Results 1 to 12 of 12

Thread: Indoubt

  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: Indoubt

    Hi
    I have been trying to find the total of revenue for a certain province and using the sum function. The actual code written is

    Select ANAME,sum(REVENUE) as Income
    from V_CALLDISPERSION_NEW
    WHERE FIXED_2_FIXED='Y' and to_char(CALLDATE,'MM/YYYY') IN ('02/2011')
    Group by ANAME
    but the error I got was this

    The report query needs a unique key to identify each row. The supplied key cannot be used for this query. Please edit the report attributes to define a unique key column. ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

    How can I solve this?..

    Thanks in advance,
    Delisha

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I solve this?..
    place a UNIQUE index on ANAME
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ANAME can't be a unique column, not if it can be grouped. What tool are you using, that select would work fine in sql*plus
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    May 2011
    Posts
    19

    Indoubt

    Quote Originally Posted by anacedent View Post
    >How can I solve this?..
    place a UNIQUE index on ANAME
    Thank you Anacedent for replying.I could not place a unique key on ANAME since the database is so huge and if I do so it would mean I had to change everything so I change the coding as

    SELECT DISTINCT ANAME,sum(REVENUE) AS REVENUE
    FROM V_CALLDISPERSION_NEW
    WHERE FIXED_2_FIXED ='Y' and to_char(CALLDATE,'MM/YYYY') IN ('02/2011') AND ROWNUM <1000
    GROUP BY DISTINCT ANAME
    and I got this error
    ORA-00936: missing expression..
    What did I miss out?..

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for table V_CALLDISPERSION_NEW
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2011
    Posts
    19
    Quote Originally Posted by beilstwh View Post
    ANAME can't be a unique column, not if it can be grouped. What tool are you using, that select would work fine in sql*plus
    Thanks beilstwh for your reply.I am using Toad for Oracle 10.5.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When you use GROUP BY, you don't need DISTINCT. Besides, you can't GROUP BY DISTINCT. Therefore, your query might look like this:
    Code:
    SELECT ANAME, sum(REVENUE) AS REVENUE
    FROM V_CALLDISPERSION_NEW
    WHERE FIXED_2_FIXED ='Y' 
      and to_char(CALLDATE,'MM/YYYY') = ('02/2011') 
      AND ROWNUM < 1000
    GROUP BY ANAME

  8. #8
    Join Date
    May 2011
    Posts
    19
    Thank you Littlefoot for your reply.I tried out your code but I got this error
    ORA-01446
    so I removed the group by clause and I got this error
    ORA-00937: not a single-group group function) ..I am new to Oracle and PL/sql and I have been working on that coding for almost a week now..I am using the tool Toad for Oracle 10.5 to create views as reports for my company. When I compile this code in Toad it ran smoothly and gave me the correct results;however it could not do the same in Oracle APEX and so it kept on giving me the errors mentioned above..

    Thanks
    Delisha

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Let's take it step by step:
    - I suppose you've created a report in Toad and that report needs (for some reason) a unique key
    - ROWID might be a solution and Toad tries to add it behind the scene thus resulting to ORA-01446
    - ORA-01446 cause is pretty self-explained so it do not insist
    - I do not understand why are you trying to accomplish with ROWNUM < 1000 condition. If you are trying to limit the result set then you have to read some docs on ROWNUM, as its place does not belong in a query with GROUP BY

    Ok. My solution:
    I do not use Toad but for your report you may try to write a SELECT without SUM and GROUP BY, place data fields on group summary, and let Toad make the SUM.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, Apex! Have a look at this OTN thread, perhaps it'll help.

  11. #11
    Join Date
    May 2011
    Posts
    19
    Thank you Littlefoot so much for the thread suggested.It worked great. I did it with success based on Scott suggestion to Corey about uncheck the single row view.I disabled the single-row view and that allowed me to enter the query in interactive mode.I now have the same report as that available in Toad.

    Thanks again,
    Delisha

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Now you probably see that providing as much information as possible narrows the problem and possible solution(s).

Posting Permissions

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