|Embedded SELECTs in SQR||KEVIN RESCHENBERG|
SQR makes it easy to embed SQL within a program. Sometimes that SQL has its own subselects or other embedded
SELECT statements. But it doesn't always
work the way you'd expect and innocent mistakes can trigger error messages.
BEGIN-SELECT section does not contain just SQL. It can include other SQR-related elements such as string and numeric variables, substitution variables,
column variables and aliases (&var), variables containing dynamic SQL, processor directives (such as
#DEFINE), SQR or SQL comments, error-handling calls,
other procedure calls, various switches, type specifiers,
PRINT and positioning parameters, and SQR statement lines, all mixed together. It's really a very complicated construct when you consider all the options.
Therefore, SQR needs to parse the
SELECT statement carefully in order to determine what gets sent to the database and what gets handled separately.
(In developing SP Debugger for SQR I needed to mimic some of the same parsing behavior.)
Most of the complicating factors occur in the portion of the
SELECT related to what you are trying to extract from the database. Once we get to the
situation becomes simpler. For example, you can't put a regular SQR line anywhere between the
FROM and the
END-SELECT. Once the
is reached, SQR can relax a little and just send most of that off to the database.
Consider this example:
If you are not familiar with the construct starting on line 3, it's just a way of extracting one value using a separate
begin-select ! THIS CODE IS WRONG 1
(SELECT FIELD2 3
FROM TABLE2 4
WHERE ...) &field2 5
do Something 7
FROM TABLE1 9
WHERE KEYFIELD = (SELECT MAX(KEYFIELD) 10
FROM TABLE1 11
WHERE ...) 12
SELECT that may or
may not be related in some way to the main one. I find myself using this much more with Oracle than with
SQL Server or Sybase. However, this example results in "(SQR 3727) SQL expression not ended, perhaps parentheses not balanced". What's going on?
As mentioned earlier, SQR needs to find the main
FROM because the situation changes at that point. Unfortunately, it just assumes that the
FROM that begins a separate line is the main one. Note that
FROM is the first thing on line 4. SQR
thinks that is the beginning of the main
FROM/WHERE section, but then it wonders where the closing parenthesis (matching the one on line 3) is.
That's what triggers the error message.
To avoid this, just move line 4 up onto line 3:
(SELECT FIELD2 FROM TABLE2
WHERE ...) &field2
Note that the
FROM on line 11 will still be OK after that change. SQR has already found where the
FROM/WHERE section starts (line 9),
and so any other
FROMs after that are not special and can be anywhere you want to put them.
And now for a little quirk (applying at least to SQR for PeopleSoft, which corresponds to regular SQR version 6.x).
The parsing task is even a little more difficult given the fact that a few SQR statements contain
clauses. If you wanted to push it, you could do this:
In this case, SQR is smart enough to realize that the first
FROM is not SQL. However, it is tricked if you use the array statement
GET in the same way
and don't code the "line continuation" character "-". That is the only case I can think of where you would ever need the line continuation dash.
In other words, it's an old, old language element that you never need, and it makes your code look very out of date, so...don't use it!