The EZQ Create/Copy window will appear. EZQ Create/ Copy If necessary, you may modify the name at this point. | Make sure the name satisfies the query naming convention at your site. | Press [enter] to place the cursor at the Copy from query prompt. | For this lesson we prefer to start from scratch, so press [enter] to skip over the Copy from query prompt and place the cursor at the Use table prompt. The Copy from query prompt is discussed in the reference section.
We now want to tell EZQ to display this element. | [enter] to indicate that you wish to display the element. EZQ will ask you to confirm that you really do want to display this element. | Accept YES (the default) by pressing [enter]. Display this element
First you will need to create an external element for the manager’s name. Usually an external element is used to access data from another table, but in this case it is used to access a different row in the same table. You will create the external element, MGR_NAME, which lets you obtain the manager’s name by connecting the MANAGER element to the EMP_SSN element. To create the MGR_NAME external element: | Highlight the <Edit> option and press [enter]. | Press [insert] to add a new element. Press [enter]. | Type MGR_NAME for the new element name. | Select EXTERNAL as the element type. Create an external element
Illustration of prefix text the hours element uses 'total =' as the prefix 08/15/96 total = 22.0 LABORATORY 216-56-7593 JONES 8.0 144-36-7056 SMITH 8.0 PHARMACY 102-44-3545 JOHNSON 6.0 the charge date element has no prefix Heading text : characters (30) This prompt will an optional line of text before the sort header, directly above the column headings. This prompt is especially useful when you want a report to display subtotal information and you want the column headings to reflect the data that is in the columns. Output format : characters (30) [list] The name must be a valid SQL_IDENTIFIER. You can press [list] to view the list of output formats. Each data type has its own set of output formats. For example, a character data type may have three formats to choose from: INTERNAL, LOWER, or UPPER. INTERNAL is free text, no limitations, LOWER refers to lowercase, and UPPER is the uppercase.
Function Description Example Count all rows Add 1 for each data row included in result Count employees Count all values Add 1 for element value Count of employees with a value for manager Total all values Add the value for all element values Total employee salaries Average all values The total divided by the count of element values Average employee salaries Maximum value The largest element value Maximum salary Minimum The smallest element value Minimum salary distinct values Add 1 for each different element value Count of different manager values Total distinct values Add the for each different element value Total of different salary amounts Average distinct values The divided by the count of different element values Average of different employee salaries The TOTAL, and AVERAGE DISTINCT VALUES functions will count, total, and average only unique values. For example, to count the number of projects with tasks you could use the COUNT DISTINCT VALUES function for the PROJ_NO element in the TASKS table.
External elements of the TASKS, PROJECTS, and EMPLOYEES tables TASKS ------------ TASK_NO PROJ_NO TASK STATUS PROJECT_LINK PROJECTS ------------------ PROJ_NO PROJECT BUDGET LEADER LEADER_LINK EMPLOYEES ------------------- EMP_SSN NAME SALARY MANAGER MANAGER_LINK Select Link Element In order to define an external element, a special type of element (a link element) must be present in the query table. A link element is essentially a foreign key to another table. By selecting a link element, you will be able to reference data from the linked table.
ratch Pad Option r.============ EZQ Tests ==============a Operator ------------------------------- scratch pad DR BUDGET > 60000 Insert test fro the scratch pad
Using the AN D/ OR Operators By default, the AND operator will be used for all pairs of tests. You can use the <Tests> option to specify that if either one of a pair of tests is true, then the pair of tests is considered true. In the example above, the OR operator is added between the two tests. If either the BUDGET > 35000.00 AND BUDGET > 65000.00 test or the BUDGET > 35000.00 AND LEADER = '621-84-8911' test is true, the row of data is selected. IMPORTANT: KB_SQL evaluates conditions according to the SQL standards. For example, it applies the AND operator before it applies the OR operator. These operator precedence rules may not evaluate your SQL statements in the order that you want. If your query contains more than two tests or if you use both the AND and OR operators, it would be better to insert a calculation element with its Boolean logic. When you use calculation elements you can use parentheses to avoid any ambiguity. Refer to the section “ Using Calculation Elements for Complex Test Logic” for more information.
EZQ element display sort group test name salary emp_ssn manager 1 2 1 desc The EZQ Editor produces valid KB_SQL statements KB_SQL statements are translated into executable routines SQL SELECT name, salary FROM employees BY salary desc M SA="" A SA=$O(^PAT(A)) IA=""GB
IN DEX 1 1st page only prompt, 218 A Add a New Element window, 50, 179 Add Element window, 51, 180 Add Query window, 4 Address prompt, 218 Allow scrolling prompt, 206 AND operator, 84, 212, 213 changing to OR, 209 of evaluation, 213 Arrow keys, 9 Ascending/descending order, 30 Average all values function, 56, 156 example of, 169 Average distinct values function, 156 B Bottom margin prompt, 221 Breaks option, 122, 215 Breaks prompt, 204 C Calculation element, 49, 180 adding, 189 and complex test logic, 192 referencing other elements, 191 Calculation Information window, 189 Character data type formats of, 165 Character values default justification of, 145 Columns of the work area, 15 Compare Element to window, 75, 93, 175 Compare to Element window, 94, 95, 176 Compare to Value window, 76, 175 Control break items, 216 Control breaks example of, 104 Copy from query prompt, 5 advantages of, 131 definition of, 131 Copying text, 210 Cost prompt, 207 Count all rows function, 57, 156, 169, 215 Count all values function, 156 Count distinct values function, 156 Count function, 57 Cursor movement keys, 9, 14 Cutting text, 210 D Dash line prompt, 219 Data type prompt in Calculation Information window, 189 in Prompt Information window, 89, 188 Data types, 141 list of, 89 Date/time prompt, 219 Deleting a query, 130 Demonstration tables, A-1 - A-4 Desc prompt, 206 Device name prompt, 201 Display a Final Footer window, 122 Display a Sort Footer window, 31 Display a Sort Header window, 31, 108, 156, 157 Display column, 15, 136 input functions, 136 Display Element Format window, 39, 41, 116, 139 Display format deleting, 139 editing, 139 Display format parameters, 125 Display if NULL prompt, 166 Display sequence, 136 Display Sequence window, 25, 38, 137 Display text if NULL prompt, 40, 151 Display this Element window, 24, 137, 161 Displaying elements, 137 Download data, 206 Duplicates in a report suppressing them, 150 E Edit option, 15, 135 Edit Tests window, 80, 92 Edit the Display Format window, 139 Element column, 15 Element definition viewing, 194
IN DEX modifying them, 217 sort, 215 Heading text prompt, 165 Headings prompt, 219 Hint line, 12, 16 I If less than lines prompt, 158 Insert a New Element, 178 Insert function, 178 Internal format, 141 J Justification prompt in Display Element Format window, 145 in Format Sort Element window, 166 Justification window, 113, 145 L Layout of report sample, 217 Layout option, 119, 217 Layout Options window, 119, 218 Layout prompt, 204 Left margin prompt, 221 Length prompt in Calculation Information window, 190 in Prompt Information window, 188 of dash line after, 159 of dash line before, 158 Level prompt, 154 Link elements, 63 selecting, 185 Literal value, 75 M Margins, 220, 221 Marking text, 210 Max rows to search prompt, 207 Max rows to select prompt, 207 Maximum value function, 53, 156 example of, 169 Menu bar, 12, 14 options, 14 Message area of EZQ interface, 199 Method prompt, 206 Minimum value function, 56, 156 example of, 169 N Naming conventions for names, 133 New page: Always? prompt, 158 No device(s) found message, 36 NONE option example of, 148 Nothing to message, 82 Null values, 60 Numeric values default justification of, 145 O Operator window, 85 Options, 14 edit, 15 selecting, 9, 14 OR operator, 84, 209, 212, 213 of evaluation, 213 Output format prompt, 141, 165 Output formats, 141 Output to data file prompt, 206 P Page Footer window, 220 Page Header window, 120, 218 Page layout customizing, 119 default, 119 parameters, 217 sample, 217 Page Margins window, 221 Paper size specifying in run message, 208 Paper type specifying in run message, 208 Parentheses using them to group tests, 213 Partial match of table names, 7 Pasting text, 210 Plan prompt, 207 Plan step, 207 Plus sign, 111
IN DEX Prefix text used in a sort header, 165 Prefix text prompt, 112, 164 Primary elements, 178 Print dash line after prompt, 158 Print dash line before prompt, 158 Print on Device window, 36, 201 Print option, 99, 203, 207 Print Options window, 99, 203 page # prompt, 219 Print site name prompt, 218 Processing messages, 201 PROJECTS tables, A-3 Prompt default prompt, 188 Prompt element, 49, 180 adding, 87, 187 entering values for, 97 Prompt Information window, 88, 187 Prompt sequence prompt, 88, 187 Prompt text prompt, 88, 187 Q adding, 3, 4, 130 compiling, 199 creating, 22, 46, 129 deleting, 130 editing, 130 executing, 199 naming, 133 printing, 36 printing the definition of, 99 running, 35 saving, 18, 35, 223 scrolling results, 206 viewing results of, 201 with more than two tests, 213 without a table, 130 Query definition printout of, 100 description prompt, 8 Query Description window, 12 Query information editing it, 205 Query name is in use message, 4 Query name prompt in EZQ Create/Copy window, 5, 131 in Select Query window, 129 in Parameters window, 205 Query results output to file, 206 restricting, 73 scrolling through, 206 sorting, 107 specifications viewing them, 203 Quit option, 18, 223 R Remote server accessing data from, 201 Report customizing it, 33, 139 formatting it, 38, 141 margins and spacing, 220 printing a hard copy, 36 starting a new line in, 166 view on terminal, 36 Report parameters, 33, 205, 206, 207 Right margin prompt, 221 Run message text prompt, 208 Run option, 35, 199, 207 Runtime instructions, 208 S Save Before Exit window, 18, 223 Saving changes to a query, 199 Scale prompt in Calculation Information window, 190 in Prompt Information window, 188 Schema, 6, 131 Scratch pad, 210 Scratch Pad Option window, 211 Select, Insert, Delete Query window, 4, 22, 130 Select, Insert, Delete Test window, 77, 176 Select Display Format window, 141 Select EZQ Function Type window, 53, 181 Select File Format window, 206 Select Link Element window, 64, 185 Select Query window, 3, 22,
IN DEX window, 54, 183 Select Table window, 132 Select Target Element window, 65, 186 Select Test Type window, 74, 174 Select USER OPTIONS window, 2 Selection windows, 11 Sequence number, 136 Setup option, 33, 201, 205 and the Show SQL prompt, 200 Parameters window, 33, 34, 35, 205 Setup prompt, 204 Show EZQ data prompt, 203 Show SQL prompt, 34, 35, 207 Show SQL text prompt, 203 Skip lines after event prompt, 158 Skip lines before event prompt, 158 Skip lines prompt, 112, 166 Sort by Element window, 28, 107, 114, 153 Sort column, 15, 28, 152 input functions, 152 Sort direction, 30 Sort direction prompt, 155 Sort Direction window, 30, 155 Sort element, 215 defining a second one, 114 formatting, 163 selecting for display in sort header, 162 Sort Element window, 162 Sort footer, 215 displaying it, 156 using for summaries, 152, 156 Sort header, 110, 215 displaying elements in, 161 displaying it, 156 formatting, 111 for titles, 152, 156 Sort level, 29, 152 highest, 154 Sort Level window, 29, 154 Sorting as opposed to grouping, 167 query results, 28, 107 Source elements, 54, 183 Spacing prompt, 221 SQL_IDENTIFIER, 141 definition of, 129 SQL Command window, 200 SQL expression as part of calculation element, 192, 193 SQL Expression window, 190 SQL statements displaying, 34, 35 viewing them, 200 Stats prompt, 207 Suppress duplicates prompt, 150 T Tab position, 142, 143 Tab position prompt, 116 in Format Sort Element window, 166 Tables demonstration tables, A-1 - A-4 selecting, 131 Target element, 65 selecting, 186 TASKS table, A-4 Test column, 15, 73, 171 as opposed to the Tests option, 209 input functions, 172 Test Selection window, 77 Test this Element window, 73, 173 Test 74 Tests applied to elements, 171 combining pairs of, 84 creating multiple, 78 example of, 177 handling multiple, 82 modifying, 209 pairs of, 212 removing, 80, 92 specifying, 73 of, 174 using calculation elements, 192 viewing, 209 option, 73, 84, 209 and using the scratch pad, 210 prompt, 204 Text windows, 10 Text wrap options, 147 examples of, 148 Top margin prompt, 221 Total all values function, 156 Total distinct values function, 156 Type prompt of dash line after, 159