SAS SQL Query Window User's Guide

SCL Class Method Examples

This section contains examples for calling the QUERY, WHERE, and QWCLEXPR class methods from SAS Screen Control Language (SCL) along with possible returned error messages. The LOADCLASS and INSTANCE SCL functions only needed to be specified once.

QW_EDIT

The following example invokes the Query Window with a user-defined profile, a preselected query included, and the confirmation end window turned off.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
call send(objectid, 'QW_EDIT', 'SASUSER.PROFILE.ORACLE ',
          ' ','SASUSER.PROFILE.SALES',' ','noend',querymsg);
if ( querymsg not = _blank_ ) then
     _msg_      = querymsg;

Error Messages

Query included does not exist: An included query does not exist.
Profile selected does not exist: The profile does not exist.

QW_EDIT_INCLUDE

The following example invokes the Query Window with the query SASUSER.PROFILE.SALES included.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
call send(objectid, 'QW_EDIT_INCLUDE', 'SASUSER.PROFILE.SALES',
         ' ',querymsg);
if ( querymsg not = _blank_ ) then
   _msg_      = querymsg;

Error Messages

Query included does not exist: An included query does not exist.
INCLUDE parameter is missing: QW_EDIT_INCLUDE has been passed a blank INCLUDE parameter.

QW_EDIT_SELECTED

The following example invokes the SQL Query Window with two tables, SASUSER.FITNESS and SASUSER.CLASS, selected, and begins in the SQL Query Column window.

clasid     = loadclass('sashelp.sql.query.class');
objectid   = instance(clasid);
call send(objectid, 'QW_EDIT_SELECTED', 'SASUSER.FITNESS, SASUSER.CLASS');

QW_LIST_QUERIES

The following example searches the SQL DICTIONARY.CATALOG rows for the SASUSER library and PROFILE catalog to find all catalog types of QUERY (saved SQL Query window queries). The name and description of the saved queries are stored in the SCL list QUERYLST with blanks between the name and the description.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
querylst   = makelist();
call send(objectid, 'QW_LIST_QUERIES', 'SASUSER','PROFILE',
          querylst, querymsg);
if ( querymsg not = _blank_ ) then
   _msg_      = querymsg;

Error Messages

LIBNAME parameter is missing: QW_LIST_QUERIES was passed a blank LIBNAME parameter.
CATALOG parameter is missing: QW_LIST_QUERIES was passed a blank CATALOG parameter.
Libname does not exist: The library specified in the LIBNAME parameter does not exist.
Catalog does not exist: The catalog specified in the CATALOG parameter does not exist.
No queries were found: No QUERY entries were found in the specified library and catalog.

QW_RUN

The following example runs the query stored in SASUSER.PROFILE.SALES and displays results in the SAS Display Manager Output window without invoking the Query window.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
call send(objectid, 'QW_RUN', 'SASUSER.PROFILE.SALES',
         'IMMEDIATE',' ',' ',querymsg);
if ( querymsg not = _blank_ ) then
   _msg_      = querymsg;

The following example runs the query stored in SASUSER.PROFILE.SALES and displays results in the REPORT procedure window without invoking the Query window.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
call send(objectid, 'QW_RUN', 'SASUSER.PROFILE.SALES',
         ' ',' ',' ',querymsg));
if ( querymsg not = _blank_ ) then
   _msg_      = querymsg;

Error Messages

Query included does not exist: An included query does not exist.
Profile selected does not exist: The profile does not exist.
INCLUDE parameter is missing: QW_RUN was passed a blank INCLUDE parameter.
Signon canceled: The user canceled out of a DBMS signon window.

QW_EDIT_INCLUDE_RUN

The following example runs the query stored in SASUSER.PROFILE.SALES, displays results in the REPORT procedure window, and invokes the Query window with this query included.

classid    = loadclass('sashelp.sql.query.class');
objectid   = instance(classid);
querymsg   = _blank_;
call send(objectid, 'QW_EDIT_INCLUDE_RUN', 'SASUSER.PROFILE.SALES');
if ( querymsg not = _blank_ ) then
   _msg_      = querymsg;

Error Messages

Query included does not exist: An included query does not exist.
Profile selected does not exist: The profile does not exist.
INCLUDE parameter is missing: QW_RUN was passed a blank INCLUDE parameter.

WHERE_BLD

The following example builds SCL lists that contain available columns and column information from the data set SASUSER.CRIME. The example invokes the WHERE expression window titled Record Subset. This is independent of the SQL Query Window.

classid    = loadclass('sashelp.sql.qwwhere.class');
objectid   = instance(classid);
wherelst   = makelist();
sqllist    = makelist();
wheremsg   = _blank_;
call send(objectid, 'WHERE_BLD',  sqllist, 'SASUSER.CRIME',
          wherelst, 'Record Subset', ' ', ' ', ' ', ' ', wheremsg);
if ( wheremsg not = _blank_ ) then
   _msg_      = wheremsg;

The following example invokes the WHERE expression window and enables the user to edit the last WHERE expression created and stored in SQLLIST. The WHERE expression window is titled Edit Record Subset. This is independent of the SQL Query Window.

classid    = loadclass('sashelp.sql.qwwhere.class');
objectid   = instance(classid);
wheremsg   = _blank_;
call send(objectid, 'WHERE_BLD',  sqllist, ' ',
          wherelst, 'Edit Record Subset', ' ', ' ', ' ', ' ', wheremsg);
if ( wheremsg not = _blank_ ) then
   _msg_      = wheremsg;

Error Messages

Dataset does not exist: The data set specified in the data set name parameter is not found while building the available columns.
SCL list is empty or missing: The available column SCL lists are empty.
Automatic Lookup Dataset does not exist: A data set name was passed as the AUTOLOOK parameter and the data set is not found.

GET_TEXT

The following example creates a text array that contains an existing WHERE expression character string. The array can extend up to fifty 200-character-length segments. If any prompts were included in the WHERE expression, they are resolved by prompting the user to supply the values.

array textarray(50) $200;
classid     = loadclass('sashelp.sql.qwwhere.class');
objectid    = instance(classid);
wheremsg   = _blank_;
call send(objectid, 'GET_TEXT', sqllist, textarray, wheremsg);
if ( wheremsg not = _blank_ ) then
   _msg_      = wheremsg;

Error Message

SCL list is empty or missing: The SQLLIST is empty.

GET_TEXT_LIST

The following example builds an SCL list that contains the character string of an existing WHERE expression. If any prompts were included in the WHERE expression, they are resolved by prompting the user to supply the values.

classid      = loadclass('sashelp.sql.qwwhere.class');
objectid     = instance(classid);
stringlst   = makelist();
wheremsg   = _blank_;
call send(objectid, 'GET_TEXT_LIST', sqllist, stringlst);
if ( wheremsg not = _blank_ ) then
   _msg_      = wheremsg;

Error Message

SCL list is empty or missing: The SQLLIST or STRINGLST is empty.

WHERE_CLEAN

The following example removes any SCL lists that are created and stored in SQLLIST for the WHERE expression window.

classid    = loadclass('sashelp.sql.qwwhere.class');
objectid   = instance(classid);
call send(objectid, 'WHERE_CLEAN', sqllist);

EDIT_ADD_EXPR

The following example invokes the COLUMN expression window to build the expression that will be named PRICEFT using the available columns from the data set SASUSER.HOUSES. PRICEFT will be added to EXPRMAST (expression master SCL list). This is independent of the SQL Query Window.

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
exprmast   = makelist();
exprmsg    = _blank_;
call send(objectid, 'EDIT_ADD_EXPR', 'SASUSER.HOUSES', exprmast,
          'PRICEFT',' ',' ',exprmsg);
if (  exprmsg  not = _blank_ ) then
    _msg_      =  exprmsg;

The following example invokes the COLUMN expression window and allows the user to edit the expression named PRICEFT that was created and stored in EXPRMAST. This is independent of the SQL Query Window.

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
exprmast   = makelist();
exprmsg    = _blank_;
call send(objectid, 'EDIT_ADD_EXPR', ' ', exprmast,
          'PRICEFT',' ',' ',exprmsg);
if (  exprmsg  not = _blank_ ) then
    _msg_      =  exprmsg;

Error Messages

Dataset does not exist: The data set specified in the data set name parameter is not found while building the available columns.
SCL list is empty or missing: The user attempted to edit a COLUMN expression and EXPRMAST is empty. This message is also generated if the available column SCL lists are empty.

GET_TEXT

The following example creates a text array that contains the text string of the COLUMN expression named PRICEFT including format, label, and expression name. The array can extend up to fifty 200 character length segments.

array textarray(50) $200;
classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
call send(objectid, 'GET_TEXT', exprmast, textarray,'PRICEFT');

GET_TEXT_LIST

The following example creates an SCL list that contains the text string of the COLUMN expression named PRICEFT including format, label and expression name.

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
stringlst  = makelist();
call send(objectid, 'GET_TEXT_LIST', exprmast, stringlst,'PRICEFT');

GET_ATTRIB

The following example extracts the attributes for the COLUMN expression named PRICEFT: format, data type, label and expression name.

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
type       = _blank_;
format     = _blank_;
label      = _blank_;
call send(objectid, 'GET_ATTRIB', exprmast, 'PRICEFT', type,
          format, label);

GET_VALUE_LIST

The following example creates an SCL list that contains only the definition for the COLUMN expression named PRICEFT. The text string does not contain any of these column attributes: format, label and expression name.

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
stringlst  = makelist();
call send(objectid, 'GET_VALUE_LIST', exprmast, stringlist,
          'PRICEFT');

CLEAN_UP

The following example removes the sublists created for the COLUMN expression named PRICEFT that were stored in EXPRMAST (expression master list).

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
call send(objectid, 'CLEAN_UP', exprmast, 'PRICEFT');

The following example removes all COLUMN expression sublists that were stored in EXPRMAST (expression master list).

classid    = loadclass('sashelp.sql.qwclexpr.class');
objectid   = instance(classid);
call send(objectid, 'CLEAN_UP', exprmast, '_ALL_');

Chapter Contents
Previous
Next
Top of Page