Archives of the TeradataForum

Message Posted: Thu, 17 Apr 2003 @ 16:24:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I don't have an automated script yet (I need to work on one!) to parse the statement, but I'll share with you what I currently do to extract the WHERE conditions:

1. Run the following SQL statement to parse the SELECT, FROM, and WHERE clauses (*note: I use max(logtime) to get me the distinct SQL statement as I'm currently logging each statement for each user/account to try to match our Business Objects audit log):

Select logstmt.LDate, logstmt.LTime, logstmt.SelStmt, logstmt.FrmStmt,
logstmt.WhrStmt
From
(sel
logdate
,max(logtime)
,substr(statementtext,1,(position('FROM' IN statementtext)-1))
,substr(statementtext,(position('FROM' IN statementtext)),((position
('WHERE' IN statementtext)-1)- (position('FROM' IN statementtext))))
,(CASE WHEN (position('GROUP BY' IN statementtext)=0)
THEN (substr(statementtext,(position('WHERE' IN
statementtext)),char_length(statementtext)))
ELSE (substr(statementtext,(position('WHERE' IN statementtext)),
((position('GROUP BY' IN statementtext)-1)-(position('WHERE' IN
statementtext))))) END)
From dbc.accesslog
Where tvmname = 'acct_trx'
Group By 1,3,4,5) As logstmt (LDate, LTime, SelStmt, FrmStmt, WhrStmt)
;

2. Save the results as a text file and import into Excel

3. Copy and paste each column (SELECT, FROM, and WHERE) separately into MS Word (here's where I could use the power of awk or sed instead of MS Word)

4. Find and replace the following (be sure to select match case):

Find Replace
WHERE^w <>
(^w <>
- for all except IN statements (I have to run this manually, not ReplaceAll)
)^w <>
- for all except IN statements (I have to run this manually, not ReplaceAll)
AND (BETWEEN stmts) and
- have to manually replace as searching for BETWEEN
AND (all others) ^p -
- do this after all others as it will separate each condition with a CR/LF
OR ^p -
- do this after all others as it will separate each condition with a CR/LF
^p^w ^p - cleanup of whitespaces

Key: Code  Value
 ^w   (whitespace)
 ^p  paragraph mark

5. Copy and paste text back into Excel (in a separate tab from the original statement); insert a row at the top with a column name (e.g. Whrstmt)

6. Create a PivotTable from the Whrstmt column and insert the column name as a row as well as a data item (it will put a Count aggregation for each statement)

7. I also add a descending sort on the Count column to give me the top conditions

As I said, I know there is a better way of doing this, but short of figuring out the logic in a program, which I will eventually do, this works for me. I try to follow the KISS (Keep It Simple Stupid) principle as much as possible!

I will post to the list if I develop an automated script (or someone else is welcome to do the same), for those of us not planning on upgrading to R5 for awhile.

Debbie Doran
Data Warehouse Administrator
Texas A&M University System

	<Prev	Next>		<<First	<Prev	Next>	Last>>