Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design
Creating An SQL Generating Front End
temp = WHERE AND temp1 temp2
SEE CODE DEPOT FOR FULL SCRIPT
if EDATE ^= ' ' then
do
temp = WHERE AND " DATE_YYMM <= " EDATE
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT
if SDATE ^= ' ' then
do
temp = WHERE AND " DATE_YYMM >= " SDATE
SEE CODE DEPOT FOR FULL SCRIPT

/* do the zips ************************************ */
if SZIP ^= ' ' & EZIP ^= ' ' then
do
temp = " ZIP > " SZIP " AND ZIP < " EZIP
temp = AND WHERE temp
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT
if SZIP ^= ' ' then
do
temp = WHERE AND " ZIP > " SZIP
SEE CODE DEPOT FOR FULL SCRIPT
if EZIP ^= ' ' then
do
temp = WHERE AND " ZIP < " EZIP
AND = "AND"
SEE CODE DEPOT FOR FULL SCRIPT

/* now, order the fields as specified by the user.... */
ORDER = 'ORDER BY'
COMMA = ' '

DO counter = 1 to 12

if SUMMARY ^= ' ' then
do
/* add summary stuff here */

if TACCT = counter then
do
queue ORDER COMMA " TOT_USER_SITES ASC"
ORDER = ""
COMMA = ','
end

end


if TDATE = counter then
do
queue ORDER COMMA " DATE_YYMM ASC"
ORDER = ""
COMMA = ','
end

if TSITE = counter then
do
queue ORDER COMMA " NAME_LINE_1 ASC"
ORDER = ""
COMMA = ','
end

---

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