Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design
Creating An SQL Generating Front End

if XADDR ^= " " then
do
queue COMMA " NAME_LINE_2"
COMMA = ','
queue COMMA " NAME_LINE_3"
queue COMMA " NAME_LINE_4"
queue COMMA " NAME_LINE_5"
end

if XCITY ^= ' ' then
do
queue COMMA " CITY_NAME"
COMMA = ','
end

if XSTATE ^= ' ' then
do
queue COMMA " STATE_ABBR"
COMMA = ','
end

if XZIP ^= ' ' then
do
queue COMMA " ZIP"
COMMA = ','
end

if XTYPE ^= ' ' then
do
queue COMMA " CUST_TYPE_DESC"
COMMA = ','
end

if DETAIL ^= ' ' then queue "FROM FACT_TABLE"
if SUMMARY ^= ' ' then queue "FROM FACT_TABLE_SUMMARY"

SEE CODE DEPOT FOR FULL SCRIPT

/* do the number of active users ************ */
if SUSER ^= ' ' & EUSER ^= ' ' then
do
temp = " ACTIVE_USER_SITES > " SUSER
temp = temp " AND ACTIVE_USER_SITES < " EUSER
temp = AND WHERE temp
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT
do
temp = " ACTIVE_USER_SITES > " SUSER
temp = WHERE AND temp
SEE CODE DEPOT FOR FULL SCRIPT
if EUSER ^= ' ' then
do
temp = " ACTIVE_USER_SITES < " EUSER
temp = WHERE AND temp
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT
/* add a "like" parm here */
if CNAME ^= ' ' then
do
temp = WHERE AND " NAME_LINE_1 LIKE '%" CNAME "%'"
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT

if STATE ^= ' ' then
do
temp = WHERE AND " STATE_ABBR = '" STATE "'"
SEE CODE DEPOT FOR FULL SCRIPT

if METRO ^= ' ' then
do
temp = WHERE AND " CITY_NAME = '" METRO "'"
SEE CODE DEPOT FOR FULL SCRIPT

if CTYPE ^= ' ' then
do
temp = WHERE AND " CUST_TYPE_DESC = '" CTYPE "'"
SEE CODE DEPOT FOR FULL SCRIPT

/* do the dates ******************************* */

if SDATE ^= ' ' & EDATE ^= ' ' then
do
temp1 = " (DATE_YYMM >= " SDATE
temp2 = " AND DATE_YYMM <= " EDATE ")"

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm