Home » SQL & PL/SQL » SQL & PL/SQL » problem with having clause (Oracle 11.2.0.1 + SQL-Developer)
problem with having clause [message #590407] Wed, 17 July 2013 15:06 Go to next message
positron
Messages: 3
Registered: July 2013
Junior Member
Dear pro's,

Please see if you can put me in the right direction.
Sorry for the Dutch variable names, i thought i could pull this one off alone.

Given is a table with codes of different bars and the beers they serve. (biercode, kroegcode).
The code below should just list different beers and count how many bars are serving that particular beer.
At the end a string must be returned with the pairs.

When the query is run as SQL statement, it works perfectly without any problems.
However inside the script the query fails. It keeps returning no_data_found.

If i change the last line of the query to :

having count(kroegcode) >= 1


there is no problem and the output is produced, which exists of the beercode, a comma, followed by the number of bars.
(The output looks like this btw :
303,3@670,3@1387,2@399,1@650,1@787,1@729,2@1403,1@498,1@30,2@1586,1@982,1@1159,1@485,1@658,3@54,1@1299,etc etc
)
This means that the first record is beer number 303, and is served in three bars.

When i change the last line of the query to (remove the equal operator):

having count(kroegcode) > 1


it returns no_data_found.

Here is the complete script:

set serveroutput on buffer 2500000;
declare 
   v_biercode number(5);
   v_aantalKroegenTarget number(8,4);
   v_targetlijst varchar2(3990);
   v_target number(5);
   CURSOR biercursor
  IS
    select distinct(biercode) from schenkt;
begin
  open biercursor;
    loop
      fetch biercursor into v_biercode;            
       exit when biercursor%NOTFOUND;             
    
    
    select
    distinct(biercode),
    count(kroegcode)
    into
      v_target,
       v_aantalKroegenTarget
    from schenkt   
    where biercode = v_biercode
    group by biercode
    having count(kroegcode) >1; <-- troublemaker right here. Doesnt work with >1, but does work with >=1.  
    
    
    if(v_biercode is not null) then  
    v_targetlijst := v_targetlijst||to_char(v_biercode)||','||to_char(trunc(v_aantalkroegentarget,4))||'@';  
    end if;         
    end loop;
    dbms_output.put_line(v_targetlijst);
  close biercursor;
  exception 
    when no_data_found then dbms_output.put_line('query is leeg');
end;



The loop and cursor seem to be working fine, it's just the having statement that causes problems.

It's becoming a threat to my sanity why this is happening.

I suspect it has something to do with the query not meeting the having-condition, causing the into-statement to insert nulls.
However i'm not a PL/SQL expert and i'm a bit stuck with the problem.

If someone can please point me in the right direction, i'd really appreciate it.
Thank you in advance for looking into it.
Re: problem with having clause [message #590408 is a reply to message #590407] Wed, 17 July 2013 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 19350
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess that it could be rewritten in a more simple manner, using a cursor FOR loop. Here's an example (check how simpler it is - I didn't have to declare a cursor, variables, open a cursor, fetch from it, take care about exiting the loop and close the cursor).
declare
  v_targetlijst varchar2(4000);
begin
  for cur_r in (select biercode, count(kroegcode) cnt
                from schenkt
                group by biercode
                having count(*) > 0
               )
  loop
    v_targetlijst := v_targetlijst || to_char(cur_r.biercode) ||','||
                                      to_char(cur_r.cnt) || '@';
  end loop;
  dbms_output.put_line(v_tartetlijst);
end;  
Re: problem with having clause [message #590409 is a reply to message #590408] Wed, 17 July 2013 15:29 Go to previous messageGo to next message
positron
Messages: 3
Registered: July 2013
Junior Member
Thank you so much.
It works perfectly!

Yet the original question still bothers me, but i'll drink it off somehow this weekend.
Cheers Wink.
Re: problem with having clause [message #590410 is a reply to message #590409] Wed, 17 July 2013 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 19350
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is your SELECT statement that returns NO-DATA-FOUND, occasionally:
1  select
2    distinct(biercode),
3    count(kroegcode)
4  into
5    v_target,
6    v_aantalKroegenTarget
7  from schenkt   
8  where biercode = v_biercode
9  group by biercode
10 having count(kroegcode) >1;

Line 2: there's no use in selecting BIERCODE (especially not DISTINCT) because you already have that value - it is selected from a cursor and stored into V_BIERCODE.

Lines 2 & 9: DISTINCT and GROUP BY are mutually exclusive. GROUP BY returns distinct values anyway, so it is either DISTINCT or GROUP BY, but not both. As you decided to use HAVING clause as well, it is obvious that you have to remove DISTINCT and leave GROUP BY.

Line 10: when cursor returns BIERCODE from a table, the above SELECT statement tries to get count, but only if that count is greater than 1. That very BIERCODE doesn't have count greater than 1 (but has it equal to 1), therefore ">" fails but ">=" works OK.

How to debug such problems? Enclose offending statement(s) into its own BEGIN-EXCEPTION-END code. Exception handler section will ... well, handle the exception and let your code proceed with next BIERCODE.

Here's how (simplified):
declare 
  ...
begin
  open cursor;
  loop
    fetch ... into l_variable
    exit when ...

    begin                --> beginning of the inner block
      select ... into ...
    exception
      when no_data_found then
        dbms_output.put_line(l_variable);     --> this will display bier code which is responsible for no_data_found
    end;                 --> end of the inner block

    ... some more processing
  end loop;
  close cursor;
end;

[Updated on: Wed, 17 July 2013 15:47]

Report message to a moderator

Re: problem with having clause [message #590411 is a reply to message #590410] Wed, 17 July 2013 16:05 Go to previous message
positron
Messages: 3
Registered: July 2013
Junior Member
I was hoping that when count() turns out to be 1 that the record would simply be skipped, instead of raising an error.

Probably i took a wrong turn when i got errors about returning multiple rows, decided to go with distinct, and got lost even further in the dark pl/sql woods.
Lessons learned however.
Thanks for the tips about implicit declaration and debugging. They will come in very handy.
Previous Topic: How to retain special characters while extracting from DB in UNIX
Next Topic: Partition Exchange - Performance
Goto Forum:
  


Current Time: Sat Aug 02 00:28:10 CDT 2014

Total time taken to generate the page: 0.31743 seconds