Sample 26142: Validating SQL Query Syntax with the VALIDATE Statement
Wouldn’t it be handy to check your SQL queries for syntax errors before the execution of any code or data being processed? Here’s a tip that will show you how to make your job of troubleshooting and debugging your SQL queries easier.
As a normal processing sequence the SQL procedure automatically turns on syntax checking each time a query is submitted. But to enable syntax checking without the automatic execution of a query, a VALIDATE statement may be specified prior to a SELECT statement to control what is checked. Since the VALIDATE statement is designed to be specified in conjunction with a SELECT statement, users are better able to control what code is checked for troubleshooting and debugging purposes. The appropriate message is displayed on the SAS log to indicate whether coding problems exist. As illustrated in the example below, a VALIDATE statement is specified at the beginning of a SELECT statement to enable syntax checking. The SAS log displays the validity of the query syntax as is the case in the example below.
SQL Code
PROC SQL;
VALIDATE
SELECT *
FROM MOVIES
WHERE RATING = ‘G’;
QUIT;
SAS Log Results
PROC SQL;
VALIDATE
SELECT *
FROM MOVIES
WHERE RATING = 'G';
NOTE: PROC SQL statement has valid syntax.
QUIT;
The next VALIDATE statement example illustrates what happens when the syntax checker discovers an error in an SQL query. The resulting error message is the direct result of the missing FROM clause. Since the FROM clause is the only required parameter in a SELECT statement, the step was automatically stopped with a significant error. Also notice that the incorrect spelling of the WHERE clause was ignored because of the missing FROM clause.
SQL Code
PROC SQL;
VALIDATE
SELECT *
MOVIES
WERE RATING = ‘G’;
QUIT;
SAS Log Results
PROC SQL;
VALIDATE
SELECT *
MOVIES
------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, INTO.
ERROR 76-322: Syntax error, statement will be ignored.
WERE RATING = 'G';
QUIT;
NOTE: The SAS System stopped processing this step because of errors.
About the Author
Kirk Paul Lafler is the author of PROC SQL: Beyond the Basics Using SAS, published by SAS Press. He also writes the
popular SAS tips column "Kirk's Korner," which appears regularly in several SAS users group newsletters, and is a frequent speaker at
SAS users group meetings.
His book is available from the online bookstore.
Here’s a tip that will show you how to make your job of troubleshooting and debugging your SQL queries easier. This tip provided by Kirk Lafler.
| Type: | Sample |
| Topic: | Internal Administration ==> BBU
Non SAS Authors ==> Kirk Paul Lafler
SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2007-03-13 03:03:48 |
| Date Created: | 2007-03-13 03:03:48 |
Operating System and Release Information
| SAS System | Base SAS | All | n/a | n/a |
