Dynamic SQL within stored procedure

[eloiselle]

Hello everyone,

I've been trying to execute a dynamic SQL statement within a stored procedure for some time now, and this is what I've got so far:

CREATE OR REPLACE PROCEDURE TSTLBEL/COMPARETBL(IN TABLE1 CHAR(10),
                                                 IN TABLE2 CHAR(10),
                                                 IN LIBL CHAR(10))
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN

        DECLARE REQUEST VARCHAR(200);
        DECLARE NBTBLCOMP INTEGER;

        SET TABLE1 = CAST(TRIM(TABLE1) AS CHAR(10));
        SET TABLE2 = CAST(TRIM(TABLE2) AS CHAR(10));
        SET LIBL = CAST(TRIM(LIBL) AS CHAR(10));

        SET REQUEST = 'SELECT COUNT(*) FROM
        (SELECT * FROM ' CONCAT LIBL CONCAT '/' CONCAT TABLE1 CONCAT '
         UNION ALL
        SELECT * FROM ' CONCAT LIBL CONCAT '/' CONCAT TABLE2 CONCAT ')';

       --Stored values
        CREATE OR REPLACE TABLE QTEMP/CMPTBL(NB_ROWS INT, AFFECTED_TABLE CHAR(10));

        --Dynamic SQL Statement
        EXECUTE IMMEDIATE REQUEST;

        --Need to store the result into NBTBLCOMP to display here
        INSERT INTO QTEMP/CMPTBL(NB_ROWS, AFFECTED_TABLE)
            VALUES (NBTBLCOMP, 'Union');
    END;

I want to be able to call it using :

CALL TSTLBEL/COMPARETBL('TABLE1', 'TABLE2', 'TSTLBEL');

I'm also storing other data in the resulting table, and it's mainly for data verification.
I was wondering if anyone has had experience with Dynamic SQL within a stored procedure on an IBM i since I've been looking through documentation for quite some time, but I can't seem to find the answer I'm looking for.

Thanks!

[B.Hauser]

we have the same rules as in embedded SQL.
1. A SELECT statement cannot be executed with EXECUTE IMMEDIATE, because you cannot specify where to return the result
2. If you want to execute a SELECT Statement dynamically, you need either a CURSOR (DECLARE, PREPARE, OPEN, FETCH, CLOSE) or
3. if you only want to return a single row, a VALUES ... INTO statement in composition with PREPARE and EXECUTE (note: SELECT ... INTO is not supported with dynamic SQL).

Here your example with VALUES INTO:

SET REQUEST = 'Values( ' concat
    'SELECT COUNT(*) FROM
        (SELECT * FROM ' CONCAT LIBL CONCAT '/' CONCAT TABLE1 CONCAT '
         UNION ALL
        SELECT * FROM ' CONCAT LIBL CONCAT '/' CONCAT TABLE2 CONCAT ')'
        concat ') into ?';

Prepare DynSQL From Request;
Execute DynSQL Using NBTBLCOMP

BTW if you only want to calculate how many rows are currently in your tables ... it can be done with static SQL!

Select sum(Number_Rows) into YourResult
  from SysTableStat
  Where    (System_Table_Name = YourTable1 and System_Table_Schema = YourSchema1)
        or (System_Table_Name = YourTable2 and System_Table_Schema = YourSchema2);

Birgitta

[eloiselle]

Thank you very much for your interest. I'll be trying this out tomorrow.

So if I understand correctly, VALUES...INTO using an aggregate will work in dynamic SQL because the result can be predicted (in this case, COUNT returns an Integer I believe).

I'm specifically looking for a match using this method (UNION ALL) and the row count of the other 2 tables (which should be a clear indicator if the 2 tables have exactly the same data within them)

Now what data isn't matching would be interesting eventually, but that would probably require a cursor to know exactly which row isn't matching.

Anyhow, thanks for your help.

[B.Hauser]

If you want to check whether 2 tables are completely identical (same columns, same rows, same contents), you need the following statement (if no row is returned, both tables are an exact match)

   (   Select * from Table1
    Except
       Select * from Table2)
Union
   (   Select * from Table2
    Except
       Select * from Table1);

Extract filters all row from the first table that are not or different in the second table.

Birgitta

[eloiselle]

I'm assuming you meant to say EXCEPT as in

[INDENT](   Select * from Table1    [/INDENT][INDENT]Except        [/INDENT][INDENT]Select * from Table2)[/INDENT]
 Union[INDENT](   Select * from Table2 [/INDENT][INDENT]Except        [/INDENT][INDENT]Select * from Table1);[/INDENT]

Thank you for your time.

Emile

[B.Hauser]

I'm assuming you meant to say EXCEPT as in

[INDENT]( Select * from Table1 [/INDENT][INDENT]Except [/INDENT][INDENT]Select * from Table2)[/INDENT]
Union[INDENT]( Select * from Table2 [/INDENT][INDENT]Except [/INDENT][INDENT]Select * from Table1);[/INDENT]

Thank you for your time.

Emile

You are right! This happens if you write an answer before the first cup of coffee in the morning

Birgitta

[Vectorspace]

One limitation of the Except/Union method, is because it only checks if an identical record exists in the other table, it does not handle duplicate rows.
E.g. if Table1 contains 5 copies of the same row (100% identical, all fields) and Table2 contains one copy of that row, then the Except/Union method will show no differences.

[EmmanuelW1]

Vectorspace, thanks for pointing that out - I've used this method and wasn't aware of this limitation. Will remember to compare record counts as well when using this method.

Cheers,

Emmanuel

[Vectorspace]

Overall row count would not help if, for example:

• fileA contains 5x of record A and 1x of record B,
• fileB contains 1x of record A and 5x of record B

Both files contain 6 rows, and both contain at least 1 of each type of record.
I think to achieve a true comparison, you would have to calculate a count per distinct row value:

(select <all fields, individually specified>, count(*) from fileA group by <all fields, individually specified>
union
select <all fields, individually specified>, count(*) from fileB group by <all fields, individually specified>)
except...

I checked, group by must be an explicit column list, it cannot be *

Of course if the table has a unique key, then there cannot be duplicate rows so the standard compare will work fine.

[eloiselle]

If you're trying to compare 2 tables by distinct row value, couldn't you just use

SELECT DISTINCT COUNT(*)
FROM FileA
UNION
SELECT DISTINCT COUNT(*)
FROM FileB
EXCEPT...

to achieve the same effect?

My bad, that wouldn't fix the comparison on the number of duplicate rows.