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

• dt128

  SSC Enthusiast

  Points: 187

  More actions

  October 17, 2020 at 11:46 pm

  #3798448

  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 PROCEDURE2 (var1 type1)
  
  INSERT INTO TEMP
  
  FOR i = 1 TO 3
  select * from table1 where ${condH} AND condition1
  select * from table1 where ${condH} AND condition2
  select * from table1 where ${condH} 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?

  • This topic was modified 4 years, 5 months ago by  dt128.

• irgp

  SSC Journeyman

  Points: 80

  More actions

  October 18, 2020 at 11:36 am

  #3798545

  As this is a SQL server forum, and as the subject line indicates you are using an Informix database, you may have more luck on an Informix users forum, for example https://members.iiug.org/forums/