DB connection
Amit Patel's shell script using a temp table across several dbaccess calls failed with syntax errors. Paul Watson showed running the SET ISOLATION, count and UNLOAD statements in one dbaccess session with the temp table, without backslashes. Ramon Rey noted the unload file was hardcoded and would be overwritten. Art Kagel gave a simpler loop over table names with a per-table unload file.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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
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
------------------------------