CODEWITHSUNDEEP
bashstored-proceduresinformix
MD128
MD128

Reputation: 491

informix 12.10 in a procedure, putting multiple queries on conditions in the loop

I have a procedure like that:

CREATE PROCEDURE   PROCEDURE1 (var1 type1)
INSERT INTO TEMP
select * from table1 where Huge_Expression1 AND  condition1 
select * from table1 where Huge_Expression1 AND  condition2  
select * from table1 where Huge_Expression1 AND  condition3
.. 
select * from table1 where Huge_Expression2 AND  condition1 
select * from table1 where Huge_Expression2 AND  condition2  
select * from table1 where Huge_Expression2 AND  condition3
..
select * from table1 where Huge_Expression3 AND  condition1 
select * from table1 where Huge_Expression3 AND  condition2  
select * from table1 where Huge_Expression3 AND  condition3
END   PROCEDURE

The problem is that the procedure is very bulky and difficult to debug.

My effort was as follows:

#!/bin/bash
# file name is script1

condH[1]="Huge_Expression1"
condH[2]="Huge_Expression2"
condH[3]="Huge_Expression3"

Run_Proc() {
    dbaccess $dbname << EOF

   CREATE PROCEDURE   PROCEDURE (var1 type1)

   INSERT INTO TEMP

   FOR i = 1 TO 3
    select * from table1 where ${condH[i]} AND  condition1
    select * from table1 where ${condH[i]} AND  condition2
    select * from table1 where ${condH[i]} AND  condition3
    ..
   END fOR

   END PROCEDURE
   --
  execute procedure PROCEDURE1(var1)
   EOF
   }

   Run_Proc $dbname
   #
   #Which is executed in the shell in this way
   ./script1 database1

Unfortunately, the term read from the array i.e. ${} is not interpreted inside the procedure

I also tried to solve the problem by making two procedures and calling one of them and arguing the condition, but the condition was not in the form of a boolean but in the form of a string ,Like defining an array inside a procedure.

can anybody help?

Upvotes: 1

Views: 271

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

Do you have INSERT INTO TEMP before each of the SELECT statements, or do you use UNION between SELECT statements, or what? To some extent, it doesn't matter what the answer is.

You can combine the first three queries easily enough:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE (Huge_Expression1) AND ((condition1) OR (condition2) OR (condition3))

You could combine all three sequences, using a notation like:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE ((Huge_Expression1) AND ((condition1) OR (condition2) OR (condition3))
        OR ((Huge_Expression2) AND ((condition1) OR (condition2) OR (condition3))
        OR ((Huge_Expression3) AND ((condition1) OR (condition2) OR (condition3))

You might be able to reduce that to:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE ((Huge_Expression1) OR (Huge_Expression2) OR (Huge_Expression3))
       AND ((condition1) OR (condition2) OR (condition3))

You might need to worry about duplicate rows.

If the huge conditions are stable, you might be able to use views:

CREATE VIEW table1_hc1
    AS (SELECT *
          FROM table1
         WHERE Huge_Expression1
       );

You can then write the first triplet of queries as:

INSERT INTO temp_table
    SELECT *
      FROM table1_hc1
     WHERE ((condition1) OR (condition2) OR (condition3))

You can do much the same job with the other two 'huge conditions'.

Alternatively, if you're using Informix 14.10 or later, you could use CTEs (common table expressions):

WITH
   table1_hc1 AS (SELECT * FROM table1 WHERE (Huge_Expression1)),
   table1_hc2 AS (SELECT * FROM table1 WHERE (Huge_Expression2)),
   table3_hc3 AS (SELECT * FROM table1 WHERE (Huge_Expression3))
SELECT *
  FROM table1_hc1 WHERE ((condition1) OR (condition2) OR (condition3))
 UNION
SELECT *
  FROM table1_hc2 WHERE ((condition1) OR (condition2) OR (condition3))
 UNION
SELECT *
  FROM table1_hc3 WHERE ((condition1) OR (condition2) OR (condition3))

Or as:

WITH
   table1_hc1 AS (SELECT * FROM table1 WHERE (Huge_Expression1)),
   table1_hc2 AS (SELECT * FROM table1 WHERE (Huge_Expression2)),
   table3_hc3 AS (SELECT * FROM table1 WHERE (Huge_Expression3))
SELECT *
  FROM (SELECT * FROM table1_hc1
         UNION
        SELECT * FROM table1_hc2
         UNION
        SELECT * FROM table1_hc3
       )
 WHERE ((condition1) OR (condition2) OR (condition3))

There may be other ways to rewrite it all if the three huge expressions have a lot in common.

I observe that your outline code does not show how the procedure parameter is used in the queries. That could affect the queries used. In particular, you can't embed the variable into the view definitions. It needn't affect the other rewrite significantly.

Just in case it isn't clear, none of the suggested SQL has been anywhere near a DBMS. There could be problems in the syntax that I've not spotted, but the idea should be clear enough, I hope. One key point is that the huge expressions are only written once each — that is important.

Upvotes: 2

Related Questions