Post reply

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

  • 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 3 years, 6 months ago by  dt128.

  • 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/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply