While Statement

[Baron]

Why I can't write an IF STATEMENT / TRIGGER EVENT STATEMENT within a WHILE LOOP?

My Script looks like this:

while (1=1)

begin

trigger event MYEVENT;

waitfor delay 50;

load into table TABLE1 from 'C:\\Temp\\2020.CSV' DELIMITED BY ';' quote '"' skip 1

if exists (select * from TABLE1 except select * from TABLE2) then

  insert into exporterrorlogs (exportstatus) values ('NOK');

else

  insert into exporterrorlogs (exportstatus) values ('OK');

end if;

end

The same applies if I try to use LOOP statement instead of WHILE END.

Which alternative do I have in order to write a conditional statement within an endless loop?

Any Ideas? Thanks in advance

[VolkerBarth]

3 downvotes? Why?

[VolkerBarth · Accepted solution]

WHILE you are mixing Watcom-SQL and Transact-SQL syntax LOOP
   errors may show up;
END LOOP;

[Baron · Accepted solution]

But is there a way to realize this bulk of watcom-sql statements in an endless loop without doing this mixing?

[VolkerBarth · Accepted solution]

Have you tried to use the Watcom-SQL WHILE/LOOP/END LOOP syntax? - For an overview, see here.

Aside: You have not told what the error is, so I don't know whether this is due to dialects - but mixing both dialects surely is error-prone and sometimes leads to rather meaningless resp. irritating error messages.

[Breck_Carter · Accepted solution]

LOL! ...very funny 🙂

[Baron · Accepted solution]

The error is syntax error

[Baron · Accepted solution]

but how can I distinguish between those 2 dialects (watcom / Transact)?

Why I cant find the "Watcom-SQL WHILE/LOOP/END Loop Syntax" in the PDF documentation of SQL Anywhere Server SQL Anywhere - SQL Reference?

[chris_keating · Accepted solution]

See http://dcx.sap.com/index.html#sqla170/en/html/3bec29c76c5f1014a91fa4cd3ffedef7.html*loio3bec29c76c5f...

The issue is that Watcom dialect, WHILE is a clause of the LOOP statement.

If a statement is TSQL, it is identified as such. See http://dcx.sap.com/index.html#sqla170/en/html/817adec96ce21014abeedc732b220dbe.html

In the case of the WHILE statement that you used, the statement is titled as WHILE statement [T-SQL].

[VolkerBarth · Accepted solution]

Just to add: Watcom-SQL is the name of SQL Anywhere's native SQL dialect, so unless otherwise noted, all documented SQL statements belong to this dialect.

In contrast, SQL Anywhere also supports a subset of the Transact-SQL dialect (T-SQL), as used by ASE and MS SQL Server. Those statements are generally marked as such, see Chris's comment.

And while (pun intended) you can use both dialects in the same database connection, you cannot do so within one SQL code block, such as a stored procedure.