On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, March 10, 2010

Writing Generated Code with Q-Quotes

Q-quotes have just made generating code too easy. It used to take several tries for me to get it right, but no more! I'm usually generating code because I want to pull some different values from tables (i.e., table_name).

Here are the steps:

1) Write out a sample of the code you want generated. Obviously, you'll want to run it first to make sure you're getting the final results needed.
Select 'Table Name: '||'SALGRADE', 'Row Count: '||Count(*) From SALGRADE;
2) Break down the query into literal, and variable parts. In this example, in two different places, I want the actual table name from User_Tables to be inserted into the code.
Select 'Table Name: '||'-- the ending quote prefaces the following table_name
SALGRADE
', 'Row Count: '||Count(*) From -- the beginning quote closes the table_name
SALGRADE
;
3) Change the sample table name (SALGRADE) to the actual column name (Table_Name), and add the concatenation bars on the front, and back.
Select 'Table Name: '||'
||table_name||
', 'Row Count: '||Count(*) From
||table_name||
;
4) Add q'[ and ]' to the front and end of each literal line. (As you may know, it can be other things than square brackets [].) On the last literal of a semicolon, I just stuck with the regular single quotes since there were no embedded quotes on that line.
q'[Select 'Table Name: '||']'
||table_name||
q'[', 'Row Count: '||Count(*) From ]'
||table_name||
';'
5) Now add a 'SELECT' at the beginning, and a 'FROM' at the end (and any other options you want ORDER BY or WHERE).
Select q'[Select 'Table Name: '||']' 
||table_name||
q'[', 'Row Count: '||Count(*) From ]'
||table_name||
';'
From User_Tables
Where Table_Name in ('EMP','DEPT','SALGRADE');
6) Add a SPOOL command, and use a 'SQL' extension. Spool Off at the end. If you're brave, you can run the queries immediately. I usually test a line or two before I do that. In this example, I had previously SET PAGESIZE=0 to kill the headers.
Spool TestQ.SQL

Select q'[Select 'Table Name: '||']'
||table_name||
q'[', 'Row Count: '||Count(*) From ]'
||table_name||
';'
From User_Tables
Where Table_Name in ('EMP','DEPT','SALGRADE');

Spool Off

/* Run the spooled query */
@TestQ
Go forth and enjoy!

No comments:

Post a Comment