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.
Click the link below to see the new game I'm developing!
4 replies to this topic
#2
CurtisZ
-
- Members
-
- 19 posts
Member
- Gender:Male
- Location:Seattle, Washington, USA
-
Interests:Programming
Family
Sports
Auto repair
Posted 18 June 2010 - 04:01 PM
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
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
#3
Marlon
-
- Members
-
- 49 posts
Member
- Gender:Not Telling
- Location:London
Posted 19 June 2010 - 10:42 AM
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.
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
This is your captain speaking. We may experience some slight turbulance and then...explode.
#4
CurtisZ
-
- Members
-
- 19 posts
Member
- Gender:Male
- Location:Seattle, Washington, USA
-
Interests:Programming
Family
Sports
Auto repair
Posted 21 June 2010 - 08:47 PM
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...
#5
DARREN
-
- Members
-
- 396 posts
ProIV Guru
- Gender:Male
- Location:Florida,USA
Posted 21 July 2010 - 02:01 PM
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...
Things should be made as simple as possible, but not simpler
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
