Parsing SQLServer SQL/ENDSQL on v6.2, Fails if AND or OR at end of line Options

[Marlon]

There's a bug in the parser for SQL/ENDSQL in 6.2.41 running on Windows against SQLServer.

This will fall over with an SQL error

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR
:$ABC = 'X'
ENDSQL

whereas this won't:

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR
(:$ABC = 'X')
ENDSQL

and neither will this:

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

The problem is the parser effectively produces
OR:$ABC = 'X'
instead of
OR<space>:$ABC = 'X'
when the OR (same for an AND) ends the line and the next line doesn't start with a bracket.

The version that fails on SQLServer works fine on Oracle.

ProIV accept it's a bug and hopefully it will be fixed.

[CurtisZ]

FYI - The following would probably be a good work-around

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

Could be done as:

$X_STRING = ' OR ' + $ABC + ' = ' + CHAR(39) + 'X' + CHAR(39) + CHAR(32)

SQL
SELECT *
FROM <TABLE>
WHERE :$ABC = 'Y'
:$X_STRING
ENDSQL

[Marlon]

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.

FYI - The following would probably be a good work-around

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

Could be done as:

$X_STRING = ' OR ' + $ABC + ' = ' + CHAR(39) + 'X' + CHAR(39) + CHAR(32)

SQL
SELECT *
FROM <TABLE>
WHERE :$ABC = 'Y'
:$X_STRING
ENDSQL

[CurtisZ]

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.

Ah, okay, I slightly misunderstood the post then...

[DARREN]

You also do not need to define the * in the SQL statement as the parser will work out the columns that you need. Defining the * tells PROIV to return all the columns for the rows, even if you are not using them in your function which can make it less efficient.

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.

Ah, okay, I slightly misunderstood the post then...