Archives of the TeradataForum
Message Posted: Thu, 17 Apr 2003 @ 16:24:28 GMT
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,
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):
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|