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

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

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