Question Re: Need help on 'IF' statement in Technology Q&A

Need help on 'IF' statement

Former Member — Thu, 12 Dec 2013 05:37:27 GMT

Hi,

I have error when running the following script:

IF (select rest_name from table.rest_def) <> 'TESTING' THEN
    RETURN
  ELSE
    SELECT net_sls_ttl 
    FROM table.dly_sys_ttl
    WHERE cast((getDate()) As Date) = cast(dly_sys_ttl.business_date As Date);

    OUTPUT TO 😧\\TimeCard.csv
    FORMAT TEXT;

END IF

The error is

"Could not execute statement. Syntax error near 'OUTPUT' on line 8. SQLCODE=-131. ODBC 3 STATE="42000" Line 1 column 5"

However, there is no error if I remove the IF statement. The text file can be exported if I only run these:

SELECT net_sls_ttl 
FROM table.dly_sys_ttl
WHERE cast((getDate()) As Date) = cast(dly_sys_ttl.business_date As Date);

OUTPUT TO 😧\\TimeCard.csv
FORMAT TEXT;

Please help 😞

Re: Need help on 'IF' statement

VolkerBarth — Thu, 12 Dec 2013 06:12:24 GMT

OUTPUT is an ISQL command, not a SQL statement, and as such, cannot be used within procedures and code blocks - and the IF statement makes your list of statements a code block. (In contrast, your list of statements without the IF statement is not a code block, i.e. each statement is sent to the database engine each on its own (here's only one: the SELECT statement), and then DBISQL processes the OUTPUT command itself.)

Therefore you need to use the UNLOAD statement instead of OUTPUT - cf. this related question:

OUTPUT does not work in procedures/events

Re: Need help on 'IF' statement

Former Member — Thu, 12 Dec 2013 21:04:50 GMT

Oh my god it works!! Thank you so much for helping out. Appreciate it. 😄