|
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 :( |
|
well try this IF 'Safari' IN (SELECT rest_name FROM dbo.rest_def)THEN ('AWS') ELSE ('SCT') END IF ENDIF for compatibility with any SA version.
(19 Mar '14, 05:44)
Dmitri
|
|
try this if answer 2 doesn't work |
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.
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!
Thanks again guys. You guys are really helpful :)
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...
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.
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...
The IF expression does not support an ELSEIF branch - however, you can nest several IF expressions, such as
Aside: Several tests on the same variable/column would usually be expressed with a CASE expression but I guess you'll get the direction...
Oh my god, you are a genius! Thank you for the clue. Really appreciate it :)
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.
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.