Question Re: While Statement in Technology Q&A

While Statement

Baron — Fri, 15 May 2020 07:10:21 GMT

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

Re: While Statement

VolkerBarth — Fri, 15 May 2020 07:17:56 GMT

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

Re: While Statement

Baron — Fri, 15 May 2020 07:27:23 GMT

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

Re: While Statement

VolkerBarth — Fri, 15 May 2020 08:16:03 GMT

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.

Re: While Statement

Breck_Carter — Fri, 15 May 2020 08:22:25 GMT

LOL! ...very funny 🙂

Re: While Statement

Baron — Fri, 15 May 2020 18:35:24 GMT

The error is syntax error

Re: While Statement

Baron — Fri, 15 May 2020 18:52:08 GMT

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?

Re: While Statement

chris_keating — Fri, 15 May 2020 22:35:56 GMT

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

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

Re: While Statement

VolkerBarth — Sat, 16 May 2020 06:17:16 GMT

3 downvotes? Why?

Re: While Statement

VolkerBarth — Sat, 16 May 2020 06:44:00 GMT

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.