| Embedded SELECTs in SQR | KEVIN RESCHENBERG
01-31-2014 |
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.
A 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,
implied 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 FROM, the
situation becomes simpler. For example, you can't put a regular SQR line anywhere between the FROM and the END-SELECT. Once the FROM
is reached, SQR can relax a little and just send most of that off to the database.
Consider this example:
begin-select ! THIS CODE IS WRONG 1
FIELD1 2
(SELECT FIELD2 3
FROM TABLE2 4
WHERE ...) &field2 5
6
do Something 7
8
FROM TABLE1 9
WHERE KEYFIELD = (SELECT MAX(KEYFIELD) 10
FROM TABLE1 11
WHERE ...) 12
end-select 13
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
first 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 FROM
clauses. If you wanted to push it, you could do this:
begin-select
FIELD1
subtract 1
from #X
FROM TABLE1
end-select
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!
|
