StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The query:</p> <pre><code>SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue </code></pre> <p>... would attempt to return one row into <code>cntmismatch</code> for every <code>keyvalue</code>, which would fail with <code>no_data_found</code> if the table was empty and with <code>too_many_rows</code> if there was more than one <code>keyvalue</code>.</p> <p>Adding:</p> <pre><code>HAVING count(keyvalue) > 1 </code></pre> <p>... means it will only return a value for any <code>keyvalue</code> with more than one entry, clearly, so now you'll get <code>no_data_found</code> if the table is empty or if no <code>keyvalue</code> appears twice; and <code>too_many_rows</code> if more than one <code>keyvalue</code> has duplicates. It will only work if you have exactly one <code>keyvalue</code> that has duplicates.</p> <p>You haven't shown the rest of your logic, but presumably you're doing something based on that returned value, in which case - if there was only one <code>keyvalue</code>, which is unlikely - you could leave out the <code>HAVING</code> clause and just test the value:</p> <pre><code>SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue; IF cntmismatch > 1 THEN ... -- some processing END IF: </code></pre> <p>But at best, all it's telling you is whether there is any <code>keyvalue</code> that appears more than once; it doesn't tell you what they <code>keyvalue</code> is, and still can't cope with there being more than one.</p> <p>If you want to retrieve all the values that have more than one matching row and do something with them then you could use a cursor:</p> <pre><code>FOR dup_keyvalues IN (SELECT keyvalue, COUNT(*) AS cnt FROM tbl_temp GROUP BY keyvalue HAVING COUNT(*) > 1) LOOP ... - some processing, e.g. DBMS_OUTPUT.PUT_LINE('keyvalue ' || dup_keyvalues.keyvalue || ' has ' || dup_keyvalues.cnt || ' matches'); ... END LOOP; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload