When writing a complex SQL statement, SET TEXT TO PASTEBOARD and Get text from pasteboard can be a great debugging aid.

For dynamic SQL the typical solution is to build an SQL statement into a string and then use:

Begin SQL     EXECUTE IMMEDIATE :$SQL_T; End SQL

to execute the statement. If you get an SQL parsing error it can be very difficult to tell just where the statement is in error.

For easy debugging try the following:

1. Place the SQL statement on the pasteboard: SET TEXT TO PASTEBOARD($SQL_T)
   
   This would also be a good place to use the utility from Tech Tip "Using the 4D v11 SQL Pasteboard as a Debugging aid".


2. Paste the SQL statement into a text editor that recognizes SQL code, such as BBEdit, Coda, or TextMate on the Mac and TextPad or Notepad++ on Windows.


3. Format the SQL statement for easy understanding, do the necessary editing, and copy the text the system clipboard.


4. Use the following code to test the SQL:
   
   

   $SQL_T:= Get text from pasteboard Begin SQL     EXECUTE IMMEDIATE :$SQL_T; End SQL

Once the query performs as expected, use the template created in the text editor to edit the code in 4D to produce the same statement.