Kovitt Posted June 23, 2008 Share Posted June 23, 2008 Hello, The script I am currently writing, runs certain queries in sqlplus and then outputs the resuts in a text file. The results look similar to: CODE CRT_TS CE_ID --------- ---------- 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 CRT_TS CE_ID --------- ---------- 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 17 rows selected. In this case all of the "ce_id's" are the same, but this is not always the case. I need to parse through these results and have it tell me what the ce_id's are and haw many times each one occur. In this case it would be 13300: 17 times. Any suggestions? Thanks! Link to comment Share on other sites More sharing options...
hhzz Posted June 23, 2008 Share Posted June 23, 2008 Just wondering... why not have the DB Engine count it for you? > Select count(CE_ID) from your_table group by CE_ID; Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 24, 2008 Share Posted June 24, 2008 Hello Kovitt, I changed the output file to include some different CE IDs for testing. Try this out: CRT_TS CE_ID --------- ---------- 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 12300 23-JUN-08 12300 23-JUN-08 12300 CRT_TS CE_ID --------- ---------- 23-JUN-08 13300 23-JUN-08 13301 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 23-JUN-08 13300 17 rows selected. Global $UniqueIDs = ObjCreate( "Scripting.Dictionary" ) Func IsCeId( $line ) Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 ) EndFunc Func ExtractCeIDFromLine( $line ) Local $temp = StringSplit( $line, " " ) Return $temp[2] EndFunc Dim $file = FileOpen( @ScriptDir & "\output.txt", 0 ) While 1 $line = FileReadLine($file) If @error = -1 Then ExitLoop If IsCeId( $line ) then $id = ExtractCeIDFromLine( $line ) If $UniqueIDs.Exists( $id ) Then $UniqueIDs( $id ) = $UniqueIDs( $id ) + 1 Else $UniqueIDs.Add( $id, 1 ) EndIF EndIf Wend ; Write out the CE ID counts $keys = $UniqueIDs.Keys For $i = 1 to UBound( $keys ) $key = $keys[$i - 1] ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF) Next This should produce: 13300 : 13 12300 : 3 13301 : 1 Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Perfect!!!! Thank you soooo much, I really appreciate it!! Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 (edited) Ok, so I added the code above to my script and it keeps saying that my Func(Func IsCeId( $line )) has no matching end func, but it does!! expandcollapse popup;############################################################################ ;AutoIt Version: 3.2.12.1 ; ;Author: Keegan Ovitt ;Date: 6/23/08 ; ;Desc: Perform the processes used to troubleshoot Job Watch errors. ; ; ;############################################################################ ;+++++++++++++++++++++++++Included User-Functions+++++++++++++++++++++++++++++ #Include <File.au3> #Include <Array.au3> #include <GUIConstantsEx.au3> ;+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Opt('MustDeclareVars', 1) JobWatch() Func JobWatch() ;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Variables^^^^^^^^^^^^^^^^^^^^^^^^^ Local $msg, $Jobs, $path, $results, $RollUpJobs, $Button_1, $Button_2, $Button_3, $Button_4, $Button_5, $Button_6, $Button_7, $Button_8 ;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ;---------------------------------------------------------------------- ;Compiles rollupSelect.txt with the script executable ;---------------------------------------------------------------------- FileInstall("C:\Documents and Settings\OvittKe\MyDocuments\scripts\Job_Watch\rollupSelect.txt", @ScriptDir & "\", 0) #cs--------------------------------------------------------------- Will create a dialog box that when displayed is centered #ce--------------------------------------------------------------- GUICreate("JobWatch Exeption Handling",302, 198) #cs--------------------------------------------------------------- Creates buttons, sets button text, and button location #ce--------------------------------------------------------------- ;===========================================Buttons $Button_1 = GUICtrlCreateButton("IFCT to OTPTx Rollups", 1, 2, 300) $Button_2 = GUICtrlCreateButton("Manual Releases Pending", 1, 26, 300) $Button_3 = GUICtrlCreateButton("CE Day Open Messages Posted", 1, 50, 300) $Button_4 = GUICtrlCreateButton("Build A Day Day + 0", 1, 74, 300) $Button_5 = GUICtrlCreateButton("Day Close Past Due", 1, 98, 300) $Button_6 = GUICtrlCreateButton("OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending", 1, 122, 300) $Button_7 = GUICtrlCreateButton("IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending", 1, 146, 300) $Button_8 = GUICtrlCreateButton("OTPT|ST_UDM ST_UDM Rollups Pending", 1, 170, 300) ;===================================================================== GUISetState() While 1 $msg = GUIGetMsg() Select Case $msg = $GUI_EVENT_CLOSE ExitLoop Case $msg = $Button_1 ;*************************************************************************** ;IFCT to OTPTx Rollups ;*************************************************************************** Global $UniqueIDs = ObjCreate( "Scripting.Dictionary" ) RunWait(@ComSpec & " /c Type rollupSelect.txt | sqlplus -s ******/*****@*** > RollupJobs.txt") $Jobs = FileRead("RollupJobs.txt") MsgBox(0, "Rollup Jobs", "Current Jobs: " & @CRLF & $Jobs) ;-------------------------------Functions---------------------------------------- Func IsCeId( $line ) Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 ) EndFunc Func ExtractCeIDFromLine( $line ) Local $temp = StringSplit( $line, " " ) Return $temp[2] EndFunc ;-------------------------------------------------------------------------------- Dim $file = FileOpen( @ScriptDir & "\RollupJobs.txt", 0 ) While 1 $line = FileReadLine($file) If @error = -1 Then ExitLoop If IsCeId( $line ) then $id = ExtractCeIDFromLine( $line ) If $UniqueIDs.Exists( $id ) Then UniqueIDs( $id ) = $UniqueIDs( $id ) + 1 Else $UniqueIDs.Add( $id, 1 ) EndIf EndIf Wend $keys = $UniqueIDs.Keys For $i = 1 to UBound( $keys ) $key = $keys[$i - 1] ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF) Next Case $msg = $Button_2 ;*************************************************************************** ;Manual Releases Pending ;*************************************************************************** Case $msg = $Button_3 ;*************************************************************************** ;CE Day Open Messages Posted ;*************************************************************************** Case $msg = $Button_4 ;*************************************************************************** ;Build A Day Day + 0 ;*************************************************************************** Case $msg = $Button_5 ;*************************************************************************** ;Day Close Past Due ;*************************************************************************** Case $msg = $Button_6 ;*************************************************************************** ;OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending ;*************************************************************************** Case $msg = $Button_7 ;*************************************************************************** ;IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending ;*************************************************************************** Case $msg = $Button_8 ;*************************************************************************** ;OTPT|ST_UDM ST_UDM Rollups Pending ;*************************************************************************** EndSelect WEnd EndFunc Does anyone see the problem?? Thanks Edited June 24, 2008 by Kovitt Link to comment Share on other sites More sharing options...
badzox Posted June 24, 2008 Share Posted June 24, 2008 Hi, just move your hole Functions Block to the End of the Code.. than it works Complete Func JobWatch ;-------------------------------Functions---------------------------------------- ; Your functions-.... ;-------------------------------------------------------------------------------- Link to comment Share on other sites More sharing options...
zorphnog Posted June 24, 2008 Share Posted June 24, 2008 You can't declare functions inside of functions! Move these outside of the JobWatch function or just don't make them functions to begin with: Func IsCeId( $line ) Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 ) EndFunc Func ExtractCeIDFromLine( $line ) Local $temp = StringSplit( $line, " " ) Return $temp[2] EndFunc Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Worked!!! Thanks a ton! Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Now my problem is that it is displaying : 13 instead of 13300 : 13 Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Now my problem is that it is displaying : 13instead of 13300 : 13 I did some further investigation It is only displaying the nuber of rows, not the actual individual ce_ids and their numbers..Any Ideas of why?Thanks, you guys are such a big help! Link to comment Share on other sites More sharing options...
XWave Posted June 24, 2008 Share Posted June 24, 2008 Hmmmm, I can't figure it out.... Sorry Bro Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 bump Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 24, 2008 Share Posted June 24, 2008 Here: expandcollapse popup;############################################################################ ;AutoIt Version: 3.2.12.1 ; ;Author: Keegan Ovitt and members of the AutoIt community ;Date: 6/23/08 ; ;Desc: Perform the processes used to troubleshoot Job Watch errors. ; ; ;############################################################################ ;+++++++++++++++++++++++++Included User-Functions+++++++++++++++++++++++++++++ #Include <File.au3> #Include <Array.au3> #include <GUIConstantsEx.au3> ;+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Opt('MustDeclareVars', 1) ;-------------------------------Functions---------------------------------------- Func IsCeId( $line ) Return 1 = StringRegExp( $line, "\A(\d){1,}\-\D{3}\-(\d){2}", 0 ) EndFunc Func ExtractCeIDFromLine( $line ) Local $temp = StringSplit( $line, " " ) Return $temp[2] EndFunc Func IFCTtoOTPTxRollups() Local $jobs, $key, $id Local $UniqueIDs = ObjCreate( "Scripting.Dictionary" ) $jobs = FileOpen( @ScriptDir & "\RollupJobs.txt", 0 ) While 1 Local $line = FileReadLine($jobs) If @error = -1 Then ExitLoop If IsCeId( $line ) then Local $id = ExtractCeIDFromLine( $line ) If $UniqueIDs.Exists( $id ) Then $UniqueIDs( $id ) = $UniqueIDs( $id ) + 1 Else $UniqueIDs.Add( $id, 1 ) EndIf EndIf Wend Local $keys = $UniqueIDs.Keys For $i = 1 to UBound( $keys ) $key = $keys[$i - 1] ConsoleWrite( $key & " : " & $UniqueIDs( $key ) & @CRLF) Next FileClose( $jobs ) EndFunc ;-------------------------------------------------------------------------------- ;^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Variables^^^^^^^^^^^^^^^^^^^^^^^^^ Global $msg, $path, $results, $RollUpJobs, $Button_1, $Button_2, $Button_3, $Button_4, $Button_5, $Button_6, $Button_7, $Button_8 GUICreate("JobWatch Exeption Handling",302, 198) ;===========================================Buttons $Button_1 = GUICtrlCreateButton("IFCT to OTPTx Rollups", 1, 2, 300) $Button_2 = GUICtrlCreateButton("Manual Releases Pending", 1, 26, 300) $Button_3 = GUICtrlCreateButton("CE Day Open Messages Posted", 1, 50, 300) $Button_4 = GUICtrlCreateButton("Build A Day Day + 0", 1, 74, 300) $Button_5 = GUICtrlCreateButton("Day Close Past Due", 1, 98, 300) $Button_6 = GUICtrlCreateButton("OTPT|OTPT2|ST_OUTp1-OUTPUTDB Rollups Pending", 1, 122, 300) $Button_7 = GUICtrlCreateButton("IFACT to OTPT|OTPT2|ST_OUTp1 Failed Rollups Pending", 1, 146, 300) $Button_8 = GUICtrlCreateButton("OTPT|ST_UDM ST_UDM Rollups Pending", 1, 170, 300) ;===================================================================== GUISetState() While 1 $msg = GUIGetMsg() Select Case $msg = $GUI_EVENT_CLOSE ExitLoop Case $msg = $Button_1 IFCTtoOTPTxRollups() EndSelect WEnd Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Seriously, Thank you sooo much.. I know this takes a long time to read through and figure out where I went wrong. I reallly really appreciate it!!! Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Sooo, I am still having the same issue.. The out put is just : 604 That is how many rows the query pulls up.. I dont know how to get it to dislay the different ce_ids and how often the occur. Link to comment Share on other sites More sharing options...
weaponx Posted June 24, 2008 Share Posted June 24, 2008 (edited) If this data is already in SQL you should use a SQL query to do the heavy lifting: SELECT CE_ID, COUNT(*) as "Instances of CE_ID" FROM tblTable GROUP BY CE_ID; Edited June 24, 2008 by weaponx Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 If this data is already in SQL you should use a SQL query to do the heavy lifting: SELECT CE_ID, COUNT(*) as "Instances of CE_ID" FROM tblTable GROUP BY CE_ID; LOL wow.. If only I knew it was that easy.. As you can tell I am new to SQL.. You just saved me about 70 lines of script.... lol Thank you Link to comment Share on other sites More sharing options...
weaponx Posted June 24, 2008 Share Posted June 24, 2008 Proof of concept (tested in MySQL):Populate:expandcollapse popup-- phpMyAdmin SQL Dump -- version 2.10.2 -- http://www.phpmyadmin.net -- -- Host: localhost:3306 -- Generation Time: Jun 24, 2008 at 03:58 PM -- Server version: 4.1.13 -- PHP Version: 5.2.0 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `misc` -- -- -------------------------------------------------------- -- -- Table structure for table `Test` -- CREATE TABLE `Test` ( `crt_ts` tinytext NOT NULL, `ce_id` tinytext NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `Test` -- INSERT INTO `Test` (`crt_ts`, `ce_id`) VALUES ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '12300'), ('23-JUN-08', '12300'), ('23-JUN-08', '12300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13301'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300'), ('23-JUN-08', '13300');Query:SELECT CE_ID, COUNT(*) as "Instances of CE_ID" FROM misc.Test GROUP BY CE_ID;Result:"CE_ID","Instances of CE_ID" "12300",3 "13300",13 "13301",1 Link to comment Share on other sites More sharing options...
Kovitt Posted June 24, 2008 Author Share Posted June 24, 2008 Nice!!, It indeed works.. Output CODE CE_ID Instances of CE_ID ---------- ------------------ 12311 14 12313 6 12314 8 12315 20 12316 11 12317 10 12319 20 12322 6 12323 6 12325 1 12329 4 CE_ID Instances of CE_ID ---------- ------------------ 12330 3 12331 5 12332 10 12335 3 12336 5 12338 3 12343 3 12344 9 12346 5 12347 4 Thanks to everyone that has helped me with this!!! Link to comment Share on other sites More sharing options...
weaponx Posted June 24, 2008 Share Posted June 24, 2008 I think people tend to overlook the power of SQL/MySQL queries.http://dev.mysql.com/doc/refman/5.0/en/functions.htmlI keep a chm manual on my desktop for MySQL, PHP, and AutoIt at all times. I just wish there was one for C++... Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now