SQL IF statements

IDS 10.00.UC4. Is there programming syntax available in straight SQL (eg: in dbaccess) such as the following? Just in SPL? if exist (select count(*) from tab1 where field2 = 0) then drop table tab2; Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com

Only in SPL. On Wed, May 28, 2008 at 2:13 PM, Robert Roussey(IT) < Robert.Roussey@spiritair.com> wrote: > IDS 10.00.UC4. > > Is there programming syntax available in straight SQL (eg: in dbaccess) > such as the following? Just in SPL? > > if exist (select count(*) from tab1 where field2 = 0) then drop table > tab2; > > Bob Roussey > Unix / Informix Administration > Spirit Airlines > Robert.Roussey@SpiritAir.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitely or implicitely. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

I know you asked if sql could do it, which kind of implies that you know there are other ways, but if that is not the case... A little ksh script will work nicely. There's probably a better way but this my first draft. #!/usr/bin/ksh ############### # drop_tab.sh # ############### dbaccess test_db@test_server - << EOT! unload to '/tmp/tab1.out' delimiter ' ' select count(*) from tab1 where field2 = 0;EOT! NROWS=`cat /tmp/tab1.out` if [[ ${NROWS} -eq 0 ]] then echo "No rows found" else dbaccess test_db@test_server - << EOT! drop table tab2;EOT! fi

NROWS would be NROWS=`wc -l /tmp/tab1.out` Theo Filander Technical Specialist Woolworths (Pty) Ltd * Woolworths House, 93 Longmarket Str, Cape Town, South Africa, 8000 ÿ theofilander@woolworths.co.za ( +27 (0) 21 407 2935 4 +27 (0) 21 407 9308 È +27 767852398 P Please consider the environment before printing this email From: MIKE MAGIE Sent: Thu 29/05/2008 08:12 PM To: ids@iiug.org Subject: Re: SQL IF statements [12244] I know you asked if sql could do it, which kind of implies that you know there are other ways, but if that is not the case... A little ksh script will work nicely. There's probably a better way but this my first draft. #!/usr/bin/ksh ############### # drop_tab.sh # ############### dbaccess test_db@test_server - << EOT! unload to '/tmp/tab1.out' delimiter ' ' select count(*) from tab1 where field2 = 0;EOT! NROWS=`cat /tmp/tab1.out` if [[ ${NROWS} -eq 0 ]] then echo "No rows found" else dbaccess test_db@test_server - << EOT! drop table tab2;EOT! fi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -------------------------------------------------------------------------------- Please note: This e-mail and its contents are subject to a disclaimer which can be viewed at http://www.woolworths.co.za/disclaimer. Should you be unable to access the link please e-mail disclaimer@woolworths.co.za and a copy of the disclaimer will be e-mailed to you.

Personally I would tend to go for something more like NROWS=$(dbaccess test_db@test_server << EOF 2> /dev/null|tail -2|head -1 SELECT count(*) FROM systables WHERE tabname = 'bus_unit'EOF ) Which avoids the need for temporary files -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Theo Filander Sent: 30 May 2008 07:41 AM To: ids@iiug.org Subject: RE: SQL IF statements [12249] NROWS would be NROWS=`wc -l /tmp/tab1.out` Theo Filander Technical Specialist Woolworths (Pty) Ltd * Woolworths House, 93 Longmarket Str, Cape Town, South Africa, 8000 ÿ theofilander@woolworths.co.za ( +27 (0) 21 407 2935 4 +27 (0) 21 407 9308 È +27 767852398 P Please consider the environment before printing this email From: MIKE MAGIE Sent: Thu 29/05/2008 08:12 PM To: ids@iiug.org Subject: Re: SQL IF statements [12244] I know you asked if sql could do it, which kind of implies that you know there are other ways, but if that is not the case... A little ksh script will work nicely. There's probably a better way but this my first draft. #!/usr/bin/ksh ############### # drop_tab.sh # ############### dbaccess test_db@test_server - << EOT! unload to '/tmp/tab1.out' delimiter ' ' select count(*) from tab1 where field2 = 0; EOT! NROWS=`cat /tmp/tab1.out` if [[ ${NROWS} -eq 0 ]] then echo "No rows found" else dbaccess test_db@test_server - << EOT! drop table tab2;EOT! fi **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ---------------------------------------------------------------------------- ---- Please note: This e-mail and its contents are subject to a disclaimer which can be viewed at http://www.woolworths.co.za/disclaimer. Should you be unable to access the link please e-mail disclaimer@woolworths.co.za and a copy of the disclaimer will be e-mailed to you. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Theo - What you wrote: NROWS would be NROWS=`wc -l /tmp/tab1.out` I disagree with this. The unload file wil have a single row, no matter how many rows match the search criteria. I like Mark's way of doing it the best - One of the reasons I posted my small script was to see what others would do. I work in a ksh environment but have only been "developing" with it for a few months. Mike

I don't agree that NROWS would equal the word count of the unload file. There will only be one row in the unload file no matter what. I like Mark's script for the reason he mentions - no unload files - although adding a rm to my script at the end would acomplish the same thing :-) MM