FREE ELECTRONIC LIBRARY - Abstract, dissertation, book

Pages:   || 2 |

«Paper BB-03 Leveraging SQL Return Codes When Querying Relational Databases John E. Bentley, Wells Fargo Bank, Charlotte, North Carolina ABSTRACT When ...»

-- [ Page 1 ] --

SESUG 2012

Paper BB-03

Leveraging SQL Return Codes When Querying Relational Databases

John E. Bentley, Wells Fargo Bank, Charlotte, North Carolina


When querying a relational database, each PROC SQL query automatically produces macro variables that contain

return codes and messages from the relational database, and the PROC SQL itself produces two more that can be of

great help. This paper will explain how and when these macro variables are generated, what they represent, and how to use them to make your programs more dynamic, robust, and error-proof. For real-world examples we will look at using SQL return codes to (1) cleanly halt execution when a database query fails and send an email with the error message, and (2) get the number of records written to a table without running SELECT COUNT(*). The presentation ® will be useful to all levels of SAS users not familiar with SQL return codes.


Good news and bad news. The good news is that, generally speaking, the number of coding problems from logic and syntax goes down as a programmer becomes more experienced with the particular language they‟re working with.

We control the level and magnitude of these problems and over time learns how to anticipate and avoid them.

The bad news is that problems beyond our control never go away. Data quality is usually out of a programmer‟s control, and we also have to deal with things like delayed production schedules, failed loads, missing files, and changes to ETL rules that we never hear about.

But there‟s more good news. We can take steps to anticipate and deal with some of the problems out of our control.

We can‟t “fix” these problems, but we can write code that anticipates them and lessens their impact. When querying relational databases, for example, SQL return codes are a powerful tool to help us do that.


In SAS, a return code, also known as an „rc‟, is a numeric code produced by a processing step (PROC or DATA step) that for our purposes provides a quick yes or no answer to the question “was the task successful”. By itself it‟s not much help in figuring out what went wrong but we can use it to identify if there is a problem and as a pointer to the course of action we want to take. As a general rule, for SAS return codes a value of zero means that yes, the task successfully completed.

An error message is a text string that explains the return code. Some messages are more user friendly than others, but an error message almost always does a better job than the return code at pointing you in the direction of where the problem lies. “Bad command or file name” is not much help, but “Lp0 on fire” is unambiguous. This author would argue that compared to others, SAS error messages overall are actually pretty good.

Figure 1. The root cause of many errors

–  –  –


As soon as a SAS starts, the macro processor creates a number of macro variables that capture information about the session and are available to the user. With the exception of one (SYSPBUFF), automatic macro variables are global in scope.

To use an automatic macro variable, reference it in the same way a user-defined macro variable is referenced—with an ampersand followed by the macro variable name. In an example from the SAS on-line documentation, this is

FOOTNOTE statement contains references to the automatic macro variables SYSDAY and SYSDATE:

FOOTNOTE "Report for &sysday, &sysdate";

If the current SAS session was invoked on August 7 th, 2012 the macro variable resolution produces this SAS


FOOTNOTE "Report for Tuesday, 07AUG2012";

SAS procedures and the DATA step also return a couple automatic macro variables.

SYSCC holds the overall session return code to that point.

SYSERR stores the return code of a PROC or DATA step and are reset at each step boundary. Carpenter (2004) notes that the SYSERR values can differ between steps—the value assigned to SYSERR is dependent on both the type of step and the type of error.

To see the return code for a step, use %PUT:

PROC or a DATA step code %put SYSERR= &SYSERR;

The documentation for every version of SAS has a table listing the automatic macro variables and detailed descriptions of what they represent, the data they contain, and whether or not the user can change the value. To learn more, definitely read the documentation. Just search for „automatic macro variables‟.

You can easily see the current values of your session‟s automatic macro variables. %PUT _AUTOMATIC_; dumps a list of all the automatic macro variables and their values to the log. You can also see the system and user-defined macro variables by using %PUT _SYS_; and %PUT _USER_;. The command %PUT _ALL_; writes all macro variable values to the log.


Every query in PROC SQL generates four automatic macro variables: SQLRC, SQLOBS, SQLOOPS, and SQLEXITCODE. For a PROC SQL that runs multiple queries the value of the first three changes with each query. At the end of the PROC, the final values will be those for the last query. The value of SQLEXITCODE is set only at the end of the PROC.

SQLRC contains a numeric value that provides the completion status of the query. These are straight-forward values which show the success or failure of a query, but the UNDO_POLICY= option and the SQLUNDOPOLICY system option can affect the value assigned. If you use either of these two options, read the documentation for the implications on SQLRC.

–  –  –

SQLOBS is the number of rows that were output by the query. The documentation says it “contains the number of rows that were processed by a SQL procedure statement” but this is a bit misleading. It is not always the number of rows „processed‟ to get the results set. This query illustrates the issue.

libname prod oracle path='STAGE' user="&_hemidbuser" pass="&_hemidbpwd" schema='HM_LOAD' readbuff=10000 multi_datasrc_opt=in_clause;

proc sql threads; select count(*) from prod.phone_data; quit;

%put sqlobs= &sqlobs;

The SQLOBS value is assigned after the SELECT statement executes. In this case the number of records read to get the count total is over 9 million but the value of SQLOBS is 1 because only 1 record was output. More on SQLOBS later.

The documentation provides some important considerations about how SQLOBS is affected by the NOPRINT option.

With NOPRINT the value of the SQLOBS macro variable depends on whether an output table, single macro variable,

macro variable list, or macro variable range is created:

If an output table is created, then SQLOBS contains the number of rows in the output table.

If no data set or table, macro variable list, or macro variable range is created, then SQLOBS contains the value 1.

If a single macro variable is created, then SQLOBS contains the value 1.

If a macro variable list or macro variable range is created, then SQLOBS contains the number of rows that are processed to create the macro variable list or range.

If an SQL view is created, then SQLOBS contains the value 0.

SQLOOPS contains the number of iterations that the inner loop of PROC SQL processes. The number of iterations increases proportionally with the complexity of the query. This value can be used to somewhat control inefficient processing when it is used to set a value for the PROC SQL LOOPS= option. Details though are beyond the scope of this paper.

SQLEXITCODE contains the highest return code that occurred from SQL insert operations. This return code is written to the SYSERR macro variable when PROC SQL terminates. The default value is 0 when there are no insert operations.


PROC SQL produces two more automatic macro variables when SAS queries a relational database such as Oracle or Teradata—SQLXRC, and SQLXMSG. It is important to remember that both are available only with the PassThrough Facility—they are not produced when the DMBS is queried via a LIBNAME engine. If you‟re not using a CONNECT TO statement, they‟re not available.

SQLXRC contains a database-specific return code for the query. An Oracle code of 43568 is probably not the same as the Teradata 43568.

SQLXMSG provides descriptive information for the DBMS-specific return code. Again, each DBMS has its own idiosyncrasies and some messages are better than others. But because each DBMS has a „format‟ for their messages you can scan or parse them to find specific information, such as the phrase „invalid identifier‟. This lets your error trapping routines be a bit more robust and database agnostic.

The value of the SQLXMSG macro variable can contain special characters such as &, %, /, *, and ; so SAS

–  –  –

recommends using the %SUPERQ macro function when working with SQLXMSG content.

%let dbErrorMsg=%superq(sqlxmsg);



Return codes and error messages are reset after each query so it‟s important to capture them with %LET to create a user-defined macro variable each time you might want to use the value. In some cases you might want to recycle the user-defined macro variable name to simplify error handling routines or you can create a set of uniquely named variables with each query.

In the example below, we‟re working with a LIBREF that uses the Oracle engine and we have a PROC SQL with two queries and the second query fails. For each query we‟re capturing the SQLRC value it returns and we‟re writing the values to the log so we see the query failed with rc=8, a non-specific error.

115 libname userTabs oracle path='STAGE' schema='JBENT' user="&_hemiDbUser" pass="&_hemiDbPwd" readbuff=5000;

117 proc sql noprint threads;

118 select count(*) as numRecs 119 from userTabs.dqf_results;

121 %let _sql_rc_rec_count=&sqlrc;

123 select avg(col_recrd_ct) as avg 124 from userTabs.dqf_results;

ERROR: The AVG summary function requires a numeric argument.

ERROR: The following columns were not found in the contributing tables:


NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

126 %let _sql_rc_avg_cols=&sqlrc;

127 quit;

NOTE: The SAS System stopped processing this step because of errors. snip 128 %put _sql_rc_rec_count= &_sql_rc_rec_count;

_sql_rc_rec_count= 0 131 %put _sql_rc_avg_cols= &_sql_rc_avg_cols;

_sql_rc_avg_cols= 8 This next example is the same query as above but we‟re using SQL Pass-Through to query an Oracle table. Notice that even though the log shows a message from Oracle the SQLRC remains the same. Why? Because it‟s the SAS return code. We don‟t yet see the Oracle return code, but notice how nice the Oracle message is? So how do we capture the Oracle return code and error message so we can put them to work?

Leveraging SQL Return Codes When Querying Relational Databases, continued SESUG 2012 272 proc sql threads;

273 connect to oracle as orc (path='STAGE' user="&_hemiDbUser" pass="&_hemiDbPwd" readbuff=5000);

275 select * from connection to orc 276 (select count(*) as numRecs from jbent.dqf_results);

277 %let _sqlrc_rec_count=&sqlrc;

279 select * from connection to orc 280 (select avg(col_recrd_ct) as avg from jbent.dqf_results);

ERROR: ORACLE prepare error: ORA-00904: "COL_RECRD_CT": invalid identifier. SQL statement: select avg(col_recrd_ct) as avg from jbent.dqf_results.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

281 %let _sqlrc_avg_cols=&sqlrc;

282 disconnect from orc;

283 quit;

NOTE: The SAS System stopped processing this step because of errors. snip 285 %put _sql_rc_rec_count= &_sqlrc_rec_count;

_sql_rc_rec_count= 0 286 %put _sql_rc_avg_cols= &_sqlrc_avg_cols;

_sql_rc_avg_cols= 8 Here we capture and display the Oracle return code and error message contained in SQLXRC and SQLXMSG.

Notice that the Oracle error message starts with the return code—it always starts in column position 4 and if there are no errors the value is zero. That makes it easy to substring out the error message itself, and if you don‟t mind substringing you don‟t might not need the SQLXRC.

322 proc sql threads;

323 connect to oracle as orc (path='STAGE' user="&_hemiDbUser" pass="&_hemiDbPwd" readbuff=5000);

325 select * from connection to orc 326 (select count(*) as numRecs from jbent.dqf_results);

328 select * from connection to orc 329 (select avg(col_recrd_ct) as avg from jbent.dqf_results);

ERROR: ORACLE prepare error: ORA-00904: "COL_RECRD_CT": invalid identifier. SQL statement: select avg(col_recrd_ct) as avg from jbent.dqf_results.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

331 %let _sqlrc_avg_cols=&sqlrc;

332 %let _sql_xrc_avg_cols=&sqlxrc;

333 %let _sql_xmsg_avg_cols=%nrbquote(&sqlxmsg);

334 quit;

NOTE: The SAS System stopped processing this step because of errors. snip 336 %put _sql_rc_avg_cols= &_sqlrc_avg_cols;

_sql_rc_avg_cols= 8 337 %put _sql_xrc_avg_cols=&_sql_xrc_avg_cols;

_sql_xrc_avg_cols=-904 338 %put _sql_xmsg_avg_cols=&_sql_xmsg_avg_cols;

_sql_xmsg_avg_cols=ORA-00904: "COL_RECRD_CT": invalid identifier


Now that we know how to capture the SQL return code and error message from the database, using them is simply a matter of deciding what to do when an a query fails and then coding it up. It‟s easy to create a dummy table that contains a few good records and a bad record for each condition you want to test.

Pages:   || 2 |

Similar works:

«ZALAI GYŰJTEMÉNY 46.ZALA MEGYE A XVIII–XIX. SZÁZADBAN KÉT KORABELI LEÍRÁS ALAPJÁN ZALAI GYŰJTEMÉNY 46. ZALA MEGYE A XVIII–XIX. SZÁZADBAN KÉT KORABELI LEÍRÁS ALAPJÁN ZALA MEGYEI LEVÉLTÁR ZALAEGERSZEG, 1999 Szerkesztő: KÁLI CSABA Bél Mátyás: Zala vármegye leírása c. munkájának fordítását az eredetivel egybevetette és sajtó alá rendezte: TÓTH PÉTER Zala megye útjai és a mellettük fekvő települések a XVIII–XIX. század fordulóján c. munkát...»

«VAS MEGYE KORHÁZAINAK (ISPOTÁLY) 700 É V E S TÖRTÉNETE írta: Dr. BENCZE JÓZSEF (Szombathely). as megye kórházainak története nagy általánosságban né­ V miképp különbözik hazánk más. vidékeitől és csupán Pozsony és S o p r o n megyék hasonlítanak hozzá. E z t a különb­ séget földrajzi helyzete magyarázza és az ebből folyó gazda­ sági, p o l i t i k a i és kulturális tényezők. Már a honfoglalás után m u t a t k o z o t t, h o g y az István...»

«Int J Educ Vocat Guidance (2011) 11:17–28 DOI 10.1007/s10775-010-9187-7 The development of a lifelong guidance system in Hungary ´ A. G. Watts • Bors Tibor Borbely-Pecze Received: 20 October 2010 / Accepted: 2 December 2010 / Published online: 21 January 2011 Ó Springer Science+Business Media B.V. 2011 Abstract The development of a lifelong guidance system in Hungary Systematic work is currently being undertaken in Hungary to develop a lifelong guidance system, in line with principles...»

«SECTION 6 – AMENITY GREEN SPACE 6. Amenity green space Introduction and definition 6.1 This type of open space is most commonly found in residential areas. It includes informal recreation spaces and green spaces in and around housing, with a primary purpose of providing opportunities for informal activities close to home or work. Amenity green space is also often used for landscaping purposes.6.2 The function of this type of open space overlaps with many others, in particular parks and...»

«Brochure More information from http://www.researchandmarkets.com/reports/3173515/ Slovakia: Prepared Anchovies Market Description: This report presents a comprehensive overview of the prepared anchovies market in Slovakia and a forecast for its development in the next five years. It provides a detailed analysis of the market, its dynamics, structure, characteristics, main players, growth and demand drivers, etc. The purpose of the report is to describe the state of the prepared anchovies market...»

«Diss. ETH No. 17283 Control of Rotor Tip Leakage and Secondary Flow by Casing Air Injection in Unshrouded Axial Turbines A dissertation submitted to the SWISS FEDERAL INSTITUTE OF TECHNOLOGY ZURICH (ETH Zürich) for the degree of Doctor of Sciences presented by THOMAS BEHR Dipl.-Ing., Dresden University of Technology, Germany born January 14, 1978 citizen of the Federal Republic of Germany accepted on the recommendation of Prof. Dr. Reza S. Abhari, examiner Dr. Erik Janke, co-examiner Dr....»

«19. Beiton P., Solomon L., Soskoln C.L. Articular mobility in African population//Ann. Rheum. Dis.1973.vol.32.-P.413.20. Carter C., Wilkinson J. Persistent joint laxity and congenital dislocation of the hip// J. Bone Jt Surg. (Br.). -1964.V.46.P.40-45.21. Kirk J.H., Ansell B.M., Bywaters E.G.L. The hypermobility syndrome//Ann.Rheum.Dis.v.26.-P.419-425. ИНЖЕНЕРНОЕ ПРОФЕССИОНАЛЬНОЕ ОБРАЗОВАНИЕ М.Г. Минин доктор педагогических...»

«Специальное обучение это служба, а не место. Образовательные аспекты аутистических нарушений Сентябрь 2003 г. Офис суперинтенданта народного образования [Office of Superintendent of Public Instruction] Old Capitol Building P.O. Box 47200 Olympia, WA 98504-7200 Чтобы заказать больше экземпляров данного документа,...»

«A qualitative evaluation of non-educational barriers to the elite professions June 2015 Dr Louise Ashley, Royal Holloway University of London Professor Jo Duberley, University of Birmingham Professor Hilary Sommerlad, University of Birmingham Professor Dora Scholarios, University of Strathclyde Social Mobility and Child Poverty Commission Sanctuary Buildings 20 Great Smith Street London SW1P 3BT contact@smcpcommission.gsi.gov.uk About the Commission The Social Mobility and Child Poverty...»


«5091P AUTHENTIC_A_2_rev.qxd 12/3/08 14:17 Page i The Authentic Dissertation The Authentic Dissertation is a road map for students who want to make their dissertation more than a series of hoop-jumping machinations that cause them to lose the vitality and meaningfulness of their research. Students and tutors are presented with practical guidance for the kind of alternative dissertations that many educators believe are needed to move Doctoral and Master’s level work beyond the limitations that...»

«alles liebe zum geburtstag alles liebe zum geburtstag Geburtstagsgrüße 25 schöne Geburtstagswünsche Alles Gute und Liebe zum Geburtstag! Ich wünsche Dir von Herzen alles Gute zum Geburtstag. Lass dich feiern! Genieß deinen Ehrentag! Wir wünschen Dir zu alles-liebe-zum-geburtstag.de Alles Liebe zum Geburtstag ! ich glaube, dass im Aquarium eine Nachricht für Dich hinterlegt ist Geburtstagssprüche Originelle Geburtstagswünsche 2015 Liebes Schwesterchen, ich wünsche Dir alles Gute zum...»

<<  HOME   |    CONTACTS
2016 www.abstract.xlibx.info - Free e-library - Abstract, dissertation, book

Materials of this site are available for review, all rights belong to their respective owners.
If you do not agree with the fact that your material is placed on this site, please, email us, we will within 1-2 business days delete him.