%SYS.PTools.SQLUtilResults

------------------------------------------------------------------------------- Method: exportSQLUtilStmtRslts [SQL: PT_exportSQLUtilStmtRslts] Replaced By: exportUtilSQLAnalysisDB [SQL: PT_exportUtilSQLAnalysisDB] (%SYS.PTools.UtilSQLAnalysis) Purpose: Create a file containing all the data from the %SYS.PTools.SQLUtilResults class & return the output location... Status: New Functionality Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilResults).exportSQLUtilStmtRslts(...) SQL: CALL %SYS_PTools.PT_exportSQLUtilStmtRslts(...) SELECT %SYS_PTools.PT_exportSQLUtilStmtRslts(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Display this export file to the screen via a InterSystems IRIS Terminal: set tSC=##class(%SYS.PTools.SQLUtilResults).exportSQLUtilStmtRslts($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLUtilStmtRslts('$IO','H') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface NOTE: This method has been added to this DEPRECATED class to correspond with the newly created 'export' method defined for the new & improved interface in the '%SYS.PTools.UtilSQLAnalysis' class Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLUtilResults class: (e.g. C:\exportFile.txt) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {Current-Directory}\PT_exportSQLUtilStmtRslts_YYYYMMDD_HHMMSS.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) NOTE 2: If Passed By Reference, then 'file' will be returned as the canonical name of the Export/Reporting file format - The output format of the Export/Reporting file This parameter has the following structure of constituent elements: format=[:$LB(,,)] Where the constituent elements have the following values: - The format of the Export/Reporting file: - P = Printable/Viewable report file (.txt file, no pagination) - D = Comma-delimited data file (.csv file) which can be read into a spreadsheet - X = Microsoft Excel XML markup file suitable for import into Excel (.xml file) - H = HTML page file (.html file) - Z = User-defined delimiter "P" formatted file (.dlm file) NOTE: The element is required or defaulted to $C(9) (TAB) Optional Elements: - User-defined delimiter [DEFAULT: $C(9) (TAB)] NOTE: This is generally used for ="Z" - .csv file header for ="D": - 0 = Don't add non-standard information header to file [DEFAULT] - 1 = Add non-standard information header to file - .csv file footer for ="D": - 0 = Don't add non-standard information footer to file [DEFAULT] - 1 = Add non-standard information footer to file EXAMPLES: Valid 'format' values: - "H" // HTML file - "D" // CSV file with No information header or footer - "D:"_$LB(,1,1) // CSV file with information header & footer - "Z:"_$LB("^") // User-defined delimiter file (delim="^") silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method option - A single option, or a comma-delimited list of {OptionName} values to restrict the output of rows from the '%SYS.PTools.SQLUtilResults' class. The {OptionName} specifies the Index Usage Analysis method used to collect the data: {OptionName} values: { IU - getAllIndices & indexUsage | TS - tableScans | TI - tempIndices | JI - joinIndices | OI - outlierIndices } conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(variable)=value ptInfo("outputFile")=The canonical name of the Export/Reporting file RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------