Sybase IQ error Syntax error near 'SET' on line

Many times you may get Sybase IQ error like Syntax error near 'SET'. Suppose if I am trying to execute below statements

SET TEMPORARY OPTION Temp_Extract_Column_Delimiter =',';

SET TEMPORARY OPTION Temp_Extract_Row_Delimiter = '';

SET TEMPORARY OPTION Temp_Extract_Name1='\\\\XYZServer\\Backup\\test.txt';

SELECT column1, column2, column3 FROM tblTest

SET TEMPORARY OPTION Temp_Extract_Name1='';

commit;

Then Sybase IQ display below error

ERROR[42000][Sybase][ODBC Driver][Sybase IQ]Syntax error near 'SET' on line 5

Solution:

It simply means end SELECT query with semicolon “;”.

Every statement in Sybase IQ should be end with semicolon.

SELECT column1, column2, column3 FROM tblTest;

SET TEMPORARY OPTION Temp_Extract_Name1='';

Please leave your comments or share this tip if it’s useful for you.

How to create sp_executesql in Sybase IQ

In SQL Server, sp_executesql stored procedure is used to execute a dynamic Transact-SQL statement or batch statements.

But in Sybase IQ, there is no such stored procedure. However you can create it as below

CREATE OR REPLACE PROCEDURE usp_executesql(

    IN cSQL VARCHAR(32767))

BEGIN

    EXECUTE IMMEDIATE WITH RESULT SET ON cSQL;

END;

Here cSQL parameter is used as varchar to support ASCII code page supporting 32767 characters in SQL statement.

Please leave your comments or share this tip if it’s useful for you.

How to fix error ‘need a correlation name’ in Sybase IQ

Suppose you have two tables TableA and TableB. Both these table contains ID column. Now if you are trying to access ID column as below

SELECT ID FROM TableA INNER JOIN TableB ON TableA.ID = TableB.ID 

You will get Sybase IQ error Column 'Column Name' found in more than one table -- need a correlation name.

Whenever you are using JOIN clause with more than one table and trying to access same field name from the table then you will get this error.

How to fix it?

Solution:-

Always make sure to use table name as prefix with column name before accessing it as below

SELECT TableA.ID FROM TableA INNER JOIN TableB ON TableA.ID = TableB.ID 

SQL SERVER generates error Ambiguous column name 'ID' for similar condition as discussed in my another article How to fix error Ambiguous column name 'Column Name' in SQL Server 

Please leave your comments or share this tip if it’s useful for you.

“Alias is not unique error” in Sybase IQ

If you are executing SELECT statement in Sybase IQ with having same alias name of two columns as below

SELECT

      School.cCreatedBy AS CreatedBy,

      Student.cCreatedBy AS CreatedBy,

      Student.FullName AS FullName

FROM

      School INNER JOIN Student

ON

      School.ID = Student.SchoolID

then you will get error “Alias 'CreatedBy' is not unique”.

However same query run in SQL Server without any problem.

To fix this just remove alias or change alias name of any one column as below

SELECT

      School.cCreatedBy,

      Student.cCreatedBy,

      Student.FullName AS FullName

FROM

      School INNER JOIN Student

ON

      School.ID = Student.SchoolID

OR

SELECT

      School.cCreatedBy AS SchoolCreatedBy,

      Student.cCreatedBy AS CreatedBy,

      Student.FullName AS FullName

FROM

      School INNER JOIN Student

ON

      School.ID = Student.SchoolID

Please leave your comments or share this tip if it’s useful for you.