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 :(

asked 14 Mar '14, 05:19

ctlavender's gravatar image

ctlavender
1094410
accept rate: 0%

2

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.

(14 Mar '14, 08:25) Breck Carter

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 :)

(18 Mar '14, 03:06) ctlavender
Replies hidden
1

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

(18 Mar '14, 12:31) Volker Barth

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.

(18 Mar '14, 22:07) ctlavender

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

(09 Apr '14, 05:26) ctlavender
Replies hidden
1

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

(09 Apr '14, 05:39) Volker Barth

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

(09 Apr '14, 05:54) ctlavender

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.

(10 Apr '14, 05:16) ctlavender

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.

(10 Apr '14, 08:30) Volker Barth
showing 3 of 9 show all flat view

well try this

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

permanent link

answered 14 Mar '14, 05:36

M%20G's gravatar image

M G
629253044
accept rate: 42%

edited 14 Mar '14, 05:37

ENDIF for compatibility with any SA version.

(19 Mar '14, 05:44) Dmitri

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

permanent link

answered 15 Mar '14, 17:43

Tom%20Mangano's gravatar image

Tom Mangano
617182435
accept rate: 6%

edited 15 Mar '14, 18:07

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×48
×8

question asked: 14 Mar '14, 05:19

question was seen: 1,091 times

last updated: 10 Apr '14, 08:30