How to make a join with the stored procedure result?

Question

I want to make a join between a query and the result of stored procedure (for some reason).

my query is:

SELECT COUNT(a.values_key) AS docscount,b.main_code,b.year  
FROM SP_name_should_be_here a  
INNER JOIN  dmr23req  b 
  ON a.values_key = b.req_year || ',' || b.req_ser  
  AND a.newstateserial = 17  
  AND a.taskcode = 35  
GROUP BY b.main_code,b.year 

---

My stored procedure dmr_get_full_in takes two param

---

Answer 1

The answer is that you should use an 'iterator function' in the FROM clause:

FROM TABLE(FUNCTION stored_procedure(arg1, arg2)) AS alias(col1, col2, ....)

The 'alias' is not always necessary, but look hard at the column names in the output of executing the procedure directly:

EXECUTE stored_procedure(arg1, arg2)

If that contains unique column names, you'll be OK; if not, you won't and you'll need the AS alias(col1, col2, ...) notation.

---

Here's an analogue (more or less) to your query.

Trivial function that could be written differently, but works:

CREATE FUNCTION SimpleElements(lo INTEGER, hi INTEGER) RETURNING INTEGER AS Atomic_Number;
    DEFINE an INTEGER;
    FOREACH SELECT Atomic_Number
              INTO an
              FROM Elements
             WHERE Atomic_Number BETWEEN lo AND hi
             ORDER BY Atomic_Number
        RETURN an WITH RESUME;
    END FOREACH;
END FUNCTION;

Query using TABLE(FUNCTION stored_procedure(arg1, arg2)):

The MOD function is used as simple way of generating some groups to count, but it illustrates the notation which is the main point of the exercise.

SELECT COUNT(*) AS group_count, MOD(b.atomic_number, 3) AS mod_num
  FROM TABLE(FUNCTION SimpleElements(1, 10)) AS a(Atomic_Number)
  JOIN Elements AS b
    ON a.atomic_number = b.atomic_number
 GROUP BY mod_num
 ORDER BY mod_num;

Output:

3       0
4       1
3       2

Table of Elements

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 'Y');
INSERT INTO elements VALUES(  2, 'He',  'Helium',          4.0026, 'Y');
INSERT INTO elements VALUES(  3, 'Li',  'Lithium',         6.9410, 'Y');
INSERT INTO elements VALUES(  4, 'Be',  'Beryllium',       9.0122, 'Y');
INSERT INTO elements VALUES(  5, 'B',   'Boron',          10.8110, 'Y');
INSERT INTO elements VALUES(  6, 'C',   'Carbon',         12.0110, 'Y');
INSERT INTO elements VALUES(  7, 'N',   'Nitrogen',       14.0070, 'Y');
INSERT INTO elements VALUES(  8, 'O',   'Oxygen',         15.9990, 'Y');
INSERT INTO elements VALUES(  9, 'F',   'Fluorine',       18.9980, 'Y');
INSERT INTO elements VALUES( 10, 'Ne',  'Neon',           20.1800, 'Y');
INSERT INTO elements VALUES( 11, 'Na',  'Sodium',         22.9900, 'Y');
INSERT INTO elements VALUES( 12, 'Mg',  'Magnesium',      24.3050, 'Y');
INSERT INTO elements VALUES( 13, 'Al',  'Aluminium',      26.9820, 'Y');
INSERT INTO elements VALUES( 14, 'Si',  'Silicon',        28.0860, 'Y');
INSERT INTO elements VALUES( 15, 'P',   'Phosphorus',     30.9740, 'Y');
INSERT INTO elements VALUES( 16, 'S',   'Sulphur',        32.0650, 'Y');
INSERT INTO elements VALUES( 17, 'Cl',  'Chlorine',       35.4530, 'Y');
INSERT INTO elements VALUES( 18, 'Ar',  'Argon',          39.9480, 'Y');
…