Question Re: IF statement in SELECT statement in Technology Q&A

IF statement in SELECT statement

Former Member — Fri, 14 Mar 2014 05:19:35 GMT

Hi Guys,

I need some help here. I'm trying to put a logical statement into my output statement but apparently it doesn't work. Script as below:

UNLOAD
    SELECT 
        REPLACE((SUBSTRING(dbo.dly_srv_prd_trk_ttl.business_date,1,7)),'-','0'),
        DATEFORMAT(dbo.dly_srv_prd_trk_ttl.business_date,'dd/mm/yyyy'),
        dbo.dly_srv_prd_trk_ttl.trk_ttl_01,
        DATEFORMAT(dbo.dly_srv_prd_trk_ttl.business_date,'dd'),
        dbo.dly_srv_prd_trk_ttl.rvc_seq,
        IF (SELECT rest_name FROM dbo.rest_def)='Safari' THEN SELECT STRING('AWS') ELSE    SELECT STRING('SCT') END IF,
    FROM dbo.dly_srv_prd_trk_ttl

TO 'D:\\SUN\\DATA.txt' FORMAT FIXED

I have tested each of the statement inside and they are working when running alone. Error prompts "Syntax error near 'SELECT' on line 7".

Please help 😞

Re: IF statement in SELECT statement

Former Member — Fri, 14 Mar 2014 05:36:31 GMT

well try this

IF 'Safari' IN (SELECT rest_name FROM dbo.rest_def)THEN ('AWS') ELSE ('SCT') END IF

Re: IF statement in SELECT statement

Breck_Carter — Fri, 14 Mar 2014 08:25:59 GMT

In addition to what M G said, the IF you have coded is an "IF expression" rather than an "IF statement". They look very similar BUT an IF expression returns a single value, whereas an IF statement chooses between blocks of code (other statements) to execute. A SELECT list consists of a list of expressions, and you can code expressions inside expressions, but you cannot code a statement inside an expression. For the record, there is also a CASE expression as well as a CASE statement, both are very valuable.

Re: IF statement in SELECT statement

t1950 — Sat, 15 Mar 2014 17:43:38 GMT

try this if answer 2 doesn't work
IF (SELECT rest_name FROM dbo.rest_def)='Safari' THEN 'AWS' ELSE 'SCT' END IF
BTW your original SQL also has a comma after the end if

Re: IF statement in SELECT statement

Former Member — Tue, 18 Mar 2014 03:06:26 GMT

Hi M G and Tom, very appreciate your effort. However they did not work and comes out with syntax error on 'END'.

I've tried to search in detail in other where and found out this: http://forums.devshed.com/ms-sql-development-95/statement-select-help-135322.html

Therefore I change the expression a little bit as below and it works!

CASE WHEN (SELECT rest_name FROM dbo.rest_def)='SAFARI' THEN 'AWS' ELSE 'SCT' END

Thanks again guys. You guys are really helpful 🙂

Re: IF statement in SELECT statement

VolkerBarth — Tue, 18 Mar 2014 12:31:22 GMT

What version of SQL Anywhere are you using? Note: Versions before v11 would require a final "ENDIF" instead of "END IF" - with v11 and above, you can use either one.

Note that the docs you have cited is for MS SQL Server - which does not allow IF expressions at all, so you have to use CASE expresssions there...

Re: IF statement in SELECT statement

Former Member — Tue, 18 Mar 2014 22:07:31 GMT

Hi Volker,

It's V.9.0. My bad. And yes, once I change it to "ENDIF", it's running fine.

Thanks again for the clue.

Re: IF statement in SELECT statement

former_SQLA_member1694880 — Wed, 19 Mar 2014 05:44:40 GMT

ENDIF for compatibility with any SA version.

Re: IF statement in SELECT statement

Former Member — Wed, 09 Apr 2014 05:26:55 GMT

Guys, I am trying to add ELSEIF expression into the IF expression like above but I get an error "Syntax error near 'ELSEIF'.

I've read and refer to Sybooks Online and it seemed to be correct. I have totally out of idea which part is the culprit.

Please help...

Re: IF statement in SELECT statement

VolkerBarth — Wed, 09 Apr 2014 05:39:42 GMT

The IF expression does not support an ELSEIF branch - however, you can nest several IF expressions, such as

begin
  declare n int;
  set n = null;
  select if n > 0 then 'positive' else
            if n < 0 then 'negative' else
               if n = 0 then 'zero' else 'null' endif
            endif
         endif;
end;


Aside: Several tests on the same variable/column would usually be expressed with a CASE expression but I guess you'll get the direction...

Re: IF statement in SELECT statement

Former Member — Wed, 09 Apr 2014 05:54:55 GMT

Oh my god, you are a genius! Thank you for the clue. Really appreciate it 🙂

Re: IF statement in SELECT statement

Former Member — Thu, 10 Apr 2014 05:16:53 GMT

Hi Volker,

Is it possible that I create a procedure and call the procedure in the UNLOAD statement and nested among the functions?

I found that I'd need to write a couple of times for the long list of IF...ELSE...ENDIF in order to grab a value.

When I was trying to created one, I've got error on syntax "BEGIN" which is just right at the second line beneath of CREATE PROCEDURE xxx.

Re: IF statement in SELECT statement

VolkerBarth — Thu, 10 Apr 2014 08:30:52 GMT

That should be better asked as a separate question, and with the code you try to run.

FWIW: You can certainly call stored procs in the UNLOAD SELECT statement.