IF statement in SELECT statement

[Former Member]

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 😞

[Breck_Carter]

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.

[Former Member]

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 🙂

[VolkerBarth]

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

[Former Member]

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.

[Former Member]

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

[VolkerBarth]

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

[Former Member]

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

[Former Member]

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.

[VolkerBarth]

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.

[Former Member · Accepted solution]

well try this

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

[former_SQLA_member1694880 · Accepted solution]

ENDIF for compatibility with any SA version.