May 16, 2005

Dynamic SQL in SQR

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.

Until next time...