All Forums Database
san_marco 3 posts Joined 10/07
22 Oct 2007
Help with ERRORCODE.

HELLO, am trying to validate a showtable in a sql embed into a shellscript code, the issue is that i wish to find out how to get if the show table works or not, and then go on in one way or another, export TABLA1= originaltableexport TABLA2= originaltable_old##################################### ############################ EJECUCION DE SQL QUE BORRA LA TABLA2################################################ ################# Validamos que exista TABLA2file_sql=${DATA_DIR}/${CADENA}.sqlcat /dev/null > ${file_sql}echo "DATABASE ${BASEDATOS}" >> ${file_sql}echo ";" >> ${file_sql}echo "SHOW TABLE ${TABLA2};" >> ${file_sql}echo ".IF ERRORCODE = 0 THEN .GOTO SIGUIENTE0;" >> ${file_sql} echo ".QUIT 0;" >> ${file_sql}echo ".IF ERRORCODE <> 0 THEN .GOTO SIGUIENTE;" >> ${file_sql} echo ".LABEL SIGUIENTE0;" >> ${file_sql}echo "DROP TABLE ${TABLA2};" >> ${file_sql}echo ".QUIT 0;" >> ${file_sql}echo ".LABEL SIGUIENTE;" >> ${file_sql}echo "SHOW TABLE ${TABLA1};" >> ${file_sql}echo ".IF ERRORCODE = 0 THEN .GOTO SIGUIENTE2;" >> ${file_sql}echo ".IF ERRORCODE <> 0 THEN .GOTO SIGUIENTE3;" >> ${file_sql}echo ".LABEL SIGUIENTE2;" >> ${file_sql}echo "RENAME TABLE ${TABLA1} TO ${TABLA2};">> ${file_sql}the purpose of this code is to validate if originaltable_old, exist.. then if it does drop it, if not go to SIGUIENTE0, and ask again for another show table...please help to anyone.

joedsilva 505 posts Joined 07/05
23 Oct 2007

You can better use some bteq tricks to do this sort of work#/usr/bin/kshPRODDB=prod1db; export PRODDBMYTBL=acctstbl; export MYTBLbteq <<- END.run file mylogon;SELECT TABLENAMEFROM DBC.TABLESWHERE DATABASENAME = ${PRODDB} AND TABLENAME = ${MYTBL};.IF ACTIVITYCOUNT = 0 THEN .GOTO NODROP;DROP TABLE ${PRODDB}.${MYTBL};.LABEL NODROP;.LOGOFF;.QUIT;END

ravi_ltce 12 posts Joined 10/07
24 Oct 2007

Well yes u should use BTEQ tricks..No need to do show table...Directly use delete table....Delete TBL_Name.IF ERRORCODE = 3807 THEN .GOTO SomeLBL; /*if table does not exist fair enuf*/.IF ERRORCODE = 0 THEN .GOTO NextLBL; /*table existed so got errorcode zero*/

san_marco 3 posts Joined 10/07
24 Oct 2007

well, thanks for the answer, but the problem is kind of different than that. lets see.echo "DATABASE mydatabase;" >> ${file_sql}echo "SHOW TABLE ${TABLA2};" >> ${file_sql}echo ".IF ERRORCODE = 0 THEN .GOTO step0;" >> ${file_sql} #echo ".QUIT 0;" >> ${file_sql} echo ".IF ERRORCODE <> 0 THEN .GOTO step;" >> ${file_sql} echo ".LABEL step0;" >> ${file_sql}echo "DROP TABLE ${TABLA2};" >> ${file_sql}echo ".QUIT 0;" >> ${file_sql}echo ".LABEL step;" >> ${file_sql}echo "SHOW TABLE ${TABLA1};" >> ${file_sql}echo ".IF ERRORCODE = 0 THEN .GOTO step2;" >> ${file_sql}echo ".IF ERRORCODE <> 0 THEN .GOTO step3;" >> ${file_sql}echo ".LABEL step2;" >> ${file_sql}echo "RENAME TABLE ${TABLA1} TO ${TABLA2};">> ${file_sql}the purpose of the sql, is first ask if tabla2 exist (in the first run it should not exist, because this will be a backup table that i will create in the last line of the code), then the show table should send some answer like 0 or not 0 values, instead of that the show table says "error 3807 table doesnt exist statement=1 info=0;" and that error just damage the all process because i can't get the condition i want.i just want to get a value after i validate if the table exist.. but I don't know how to do that.. that's why I use the "".IF ERRORCODE = 0 " and the "".IF ERRORCODE <> 0 " question to get the values.. but it doesnt work just how I want.please contact me at any time.. i will be connected.

nithyanandam 65 posts Joined 10/04
24 Oct 2007

You can handle the errors in Unix by entering the below command:> echo $?This would return 0 if successful and some other number > 0 if unsuccessful.The above piece of code looks like you are trying to append to file_sql. Guess, you would have to use bteq and pass file_sql and then check for the return status using the above command.

You must sign in to leave a comment.