Informix

 View Only
  • 1.  DB connection

    Posted Mon August 02, 2021 06:59 AM
    Dears,

     I need to write few SQL statement which where I can use temp table in joins. But it is giving syntax error. As I know Unix command can not be run under informix session.  Is there any other way to do it? 
    Table names are in loop, so can't use hard coded.



    CNT1="SELECT COUNT(*) FROM optim_cs_blcs cb WHERE exists \
    SELECT t.cust_numb from temp1 t \
    WHERE cb.cust_numb = t.cust_numb "

    UNL1="UNLOAD TO optim_cs_blcs.unl SELECT COUNT* FROM optim_cs_blcs cb WHERE exists \
    SELECT t.cust_numb from temp1 t \
    WHERE cb.cust_numb = t.cust_numb "

    CNT_STM=" ""$CNT1"" "
    UNL_STM=" ""$UNL1"" "

    ${INFORMIXDIR}/bin/dbaccess ${DBNAME} <<-EOF
    #output to "${DIR}/count"
    select cust_numb from optim_test_2013_1 into temp temp1;
    echo "set isolation to dirty read; ""$CNT_STM"" " | dbaccess ${DBNAME}
    echo "set isolation to dirty read; ""$UNL_STM"" " | dbaccess ${DBNAME}
    EOF

    Thanks
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------

    #Informix


  • 2.  RE: DB connection

    IBM Champion
    Posted Mon August 02, 2021 07:07 AM

    Something like this will work

     

    CNT1="SELECT COUNT(*) FROM optim_cs_blcs cb WHERE exists SELECT t.cust_numb from temp1 t WHERE cb.cust_numb = t.cust_numb "

    UNL1="UNLOAD TO optim_cs_blcs.unl SELECT COUNT* FROM optim_cs_blcs cb WHERE exists SELECT t.cust_numb from temp1 t WHERE cb.cust_numb = t.cust_numb "

    CNT_STM=" ""$CNT1"" "
    UNL_STM=" ""$UNL1"" "

    ${INFORMIXDIR}/bin/dbaccess ${DBNAME} <<-EOF

    #output to "${DIR}/count"

    select cust_numb from optim_test_2013_1 into temp temp1;

    set isolation to dirty read;

     

    $CNT1;

     

    $UNL1;

    EOF


     






  • 3.  RE: DB connection

    Posted Thu August 05, 2021 12:11 PM
    Amit,
    You did mention "the table names are in a loop", which means this repeats over and over until all the tables have been processed. The problem is that the unload file IS HARDCODED, and will be overwritten over and over until all tables are processed, and I'm not sure that's what you want. Perhaps you could describe what is it that you are trying to accomplish with this?
    Regards,

    Ramon

    ------------------------------
    Ramon Rey
    ------------------------------



  • 4.  RE: DB connection

    IBM Champion
    Posted Thu August 05, 2021 12:28 PM
    Amit:

    You made it too complicated. KISS! Is this what you need?

    for TAB in ( <list of tables> ); do

    ${INFORMIXDIR}/bin/dbaccess ${DBNAME} <<-EOF
    #output to "${DIR}/count"
    select cust_numb from optim_test_2013_1 into temp temp1;
    set isolation dirty read;
    UNLOAD TO $TAB.cnt DELIMITER ' '
    SELECT COUNT(*) FROM $TAB cb WHERE exists (
        SELECT t.cust_numb from temp1 t 
        WHERE cb.cust_numb = t.cust_numb )
    ;
    UNLOAD TO $TAB.unl DELIMITER ' ' 
    SELECT COUNT* FROM $TAB cb WHERE exists (
        SELECT t.cust_numb from temp1 t 
        WHERE cb.cust_numb = t.cust_numb )
    ;

    EOF
    done

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------