only report the rows from a query if the number of rows exceeds a limiting value
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
As Solution Using SQL and a Bind Variable
The business problem is a simple one to state. Don't return a result set unless the number of rows to be returned meets some threshold. For example ... If there are fewer than 15 rows ... return NULL.

First a solution in SQL:
CREATE TABLE t AS
SELECT rownum rn
FROM all_objects
WHERE rownum <= 10;

var limit NUMBER

BEGIN
  :limit := 6;
END;
/

SELECT *
FROM (
  SELECT rn, COUNT(*) OVER () cnt
  FROM t)
WHERE cnt >= :limit;

BEGIN
  :limit := 60;
END;
/
Second a solution in PL/SQL (that isn't just embedding the first one in an anonymous block.
DECLARE
 TYPE ProgID IS TABLE OF airplanes.program_id%TYPE
 INDEX BY BINARY_INTEGER;
 pid_t ProgID;

 TYPE OrdDat IS TABLE OF airplanes.order_date%TYPE
 INDEX BY BINARY_INTEGER;
 ord_t OrdDat;
BEGIN
  SELECT program_id, order_date
  BULK COLLECT INTO pid_t, ord_t
  FROM airplanes
  WHERE program_id = '737'
  AND customer_id = 'DAL'
  AND line_number LIKE '%47%';

  IF pid_t.COUNT < 400 THEN
    FOR i IN 1..pid_t.COUNT LOOP
      dbms_output.put_line(ord_t(i));
    END LOOP;
  ELSE
    NULL;
  END IF;
END;
/
If you have faced a business problem similar to this and need a solution ... send your idea for a new How Can I?
 
Related Topics
SELECT Statements
SQL*Plus
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved