| Dynamic SQL in SQR | KEVIN RESCHENBERG
05-16-2005 |
In the past I've occasionally referred to dynamic SQL in SQR.
What is it and how is it used?
Normally we would code a BEGIN-SELECT section that might
include variables:
begin-select
EMPLID
EMPL_RCD
EFFDT
EMPL_STATUS
do Something
FROM PS_JOB
WHERE EMPL_STATUS IN ('A','L','P','S')
AND COMPANY = $Company
AND ...
end-select
In this example, SQR constucts the WHERE clause
by inserting the value of $Company and including the quotes.
The actual clause, which we don't see, looks like this:
AND COMPANY = '123'
Now suppose that we want to include
the AND clause for COMPANY in only certain situations.
SQR gives us the ability to write the entire AND clause
ourselves:
if ...
let $AndCompany = 'AND COMPANY = ''' || $Company || ''''
else
let $AndCompany = ' '
end-if
WHERE EMPL_STATUS IN ('A','L','P','S')
[$AndCompany]
AND ...
Here $AndCompany is either a complete AND clause
(such as AND COMPANY = '123', including the quotes around
the company ID) or it's a blank. The brackets around $AndCompany
tell SQR to include the exact value of the variable as part
of the SQL.
At run time, the value
of $AndCompany is inserted as-is into the SQL
and passed to the database. This is an important point.
It means that we are responsible
for adding the quotes around string values (such as '123')
and we cannot include variable names or SQR functions.
The following example will not work:
let $AndCompany = 'AND COMPANY = $Company' WRONG
...
WHERE EMPL_STATUS IN ('A','L','P','S')
[$AndCompany]
This won't work because the AND clause passed to the
database will be "AND COMPANY = $Company". The
database won't recognize "$Company" as a valid SQL element.
I've used dynamic SQL frequently in dealing with dates.
Suppose that we are trying to INSERT a row containing a
date, and we don't know whether the date will be NULL.
We can insert a NULL date, but it must be coded as the
word NULL. An actual date value must be supplied within
quotes. So we can insert $MyDate like this:
INSERT INTO PS_MY_TABLE VALUES($Emplid, $MyDate)
But if the date value is empty, this would be telling the
database to insert an empty string (''), which is not the
same as NULL. To insert a NULL date we must code:
INSERT INTO PS_MY_TABLE VALUES($Emplid, NULL)
This is handled easily with dynamic SQL:
if ...
let $Date = 'NULL'
else
let $Date = '''' || $MyDate || ''''
end-if
begin-sql
INSERT INTO PS_MY_TABLE VALUES($Emplid, [$Date]);
end-sql
Now the SQL will include either a valid date string
such as '01-JAN-2005' or '2005-01-01' (depending on your
database), including the quotes, or the SQL
will include the word NULL.
There are many other uses for dynamic SQL. You could switch the
ORDER clause of your SELECT using a variable. You can use it
to change the names of the fields being selected (but check the
SQR documentation on the syntax for this). You could code an
entire SQL statement within a variable and pass it to a procedure
to be executed. You can also use it as a trick to prevent SQR from
checking your SQL syntax in advance. Just set a variable to a
blank space and include it somewhere within the SQL:
let $Nothing = ' '
begin-select
...
WHERE COMPANY = $Company
[$Nothing]
AND ...
This SQL will be ignored until run time. Why prevent SQR from
checking your SQL at compile time? You might have referred to
a table that won't exist until run time. Also, I've seen reports
on one discussion board that variables (such as $Company in this
example) that are buried deep within subselects sometimes cause
errors. This trick may help with that.
|
