home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
PC Treasures, Inc.
/
pctreasures.mdf
/
WINDOWS
/
adabas
/
f_0001
/
env
/
query.enh
< prev
next >
Wrap
Text File
|
1999-11-01
|
28KB
|
378 lines
ENG|07 | 0 QUERY.enh 10.05.01 1998-04-24
ENG|07 | 1| |
ENG|07 | 3| | O V E R V I E W O N A V A I L A B L E I N F O R M A T I O N
ENG|07 | 6| I| ##0702 QUERY - functions
ENG|07 | 7| I| ##08 REPORT - functions
ENG|07 | 8| I| ##09 SQL - statements
ENG|07 | 9|RI| ##04 EDIT - functions
ENG|0702 | 1| |
ENG|0702 | 3| | Q U E R Y F U N C T I O N S :
ENG|0702 | 6| M| #01 command^history
ENG|0702 | 7| M| #02 stored^commands
ENG|0702 | 8| M| #03 preparing^reports
ENG|0702 | 9| M| #06 CROSSTAB^function
ENG|0702 | 10|RM| #05 other^statements
ENG|070201 | 1| |
ENG|070201 | 3| | C O M M A N D H I S T O R Y :
ENG|070201 | 6| M| #01 NEXT
ENG|070201 | 7| M| #02 PREV
ENG|070201 | 8|RM| #03 SAVE
ENG|07020101 | 1| |
ENG|07020101 | 3| | N E X T command :
ENG|07020101 | 5| | Fetches the next command from the history.
ENG|07020101 | 7| | SYNTAX : NEXT
ENG|07020101 | 9| | NEXT and PREV allow scrolling in the temporary command
ENG|07020101 | 10|R | history.
ENG|07020102 | 1| |
ENG|07020102 | 3| | P R E V command :
ENG|07020102 | 5| | Fetches the previous command from the history.
ENG|07020102 | 7| | SYNTAX : PREV
ENG|07020102 | 9| | NEXT and PREV allow scrolling in the temporary command
ENG|07020102 | 10|R | history.
ENG|07020103 | 1| |
ENG|07020103 | 3| | S A V E command :
ENG|07020103 | 5| | Stores the command currently displayed in the input area in
ENG|07020103 | 6| | the command history.
ENG|07020103 | 8| | SYNTAX : SAVE
ENG|07020103 | 10|RI| The current command is not executed in contrast to ##07020505RUN.
ENG|070202 | 1| |
ENG|070202 | 3| | S T O R E D C O M M A N D S :
ENG|070202 | 6| M| #01 STORE
ENG|070202 | 7| M| #02 DELETE
ENG|070202 | 8| M| #03 COPY
ENG|070202 | 9| M| #04 GRANT
ENG|070202 | 10| M| #05 REVOKE
ENG|070202 | 11| M| #06 EXPORT
ENG|070202 | 12| M| #07 IMPORT
ENG|070202 | 13| M| #08 LIST
ENG|070202 | 14|RM| #09 EDIT
ENG|07020201 | 1| |
ENG|07020201 | 3| | S T O R E command :
ENG|07020201 | 5| | Permanently stores the contents of the input area with a
ENG|07020201 | 6| | command name.
ENG|07020201 | 8| | SYNTAX : STORE <command_name> [REPLACE]
ENG|07020201 | 10| | If the REPLACE option is specified, the new command
ENG|07020201 | 11| | replaces any command which may already exist with this
ENG|07020201 | 12| | name.
ENG|07020201 | 14| | If the command name is to contain lower case letters, it
ENG|07020201 | 15| | must be enclosed in double quotes.
ENG|07020201 | 17|SI| An existing command which was selected from the ##07020208LIST^menu,
ENG|07020201 | 19| | then displayed and modified in the input form by means of
ENG|07020201 | 20| I| ##07020209EDIT, can be stored by means of the command
ENG|07020201 | 21| | STORE = [REPLACE]
ENG|07020201 | 22| | For = the current command name is inserted. REPLACE is
ENG|07020201 | 23| | obvious; therefore it need not be specified.
ENG|07020201 | 25| | &n or %n in the input area are interpreted as command
ENG|07020201 | 26| | parameters and are replaced by current parameters when
ENG|07020201 | 27| | calling (RUN).
ENG|07020201 | 29| | example of a stored command :
ENG|07020201 | 31| | /* comment : customer list ordered according to names
ENG|07020201 | 32| | SELECT cno, name, city, account
ENG|07020201 | 33| | FROM customer
ENG|07020201 | 34| | WHERE city = '&1'
ENG|07020201 | 35|S | ORDER BY name
ENG|07020201 | 37| | REPORT
ENG|07020201 | 38| I| ##0829TTITLE ' customer in &1 '
ENG|07020201 | 39| I| ##0817SEP ' '
ENG|07020201 | 40| I| ##0831PRINT &2
ENG|07020201 | 42| | store with : STORE customer_report
ENG|07020201 | 44|R | call e.g. with : RUN customer_report 'Berlin' 3
ENG|07020202 | 1| |
ENG|07020202 | 3| | D E L E T E command :
ENG|07020202 | 5| | Permanently deletes stored commands.
ENG|07020202 | 7| | SYNTAX : DELETE <command_name>
ENG|07020202 | 9| | Only the owner of a stored command may delete it.
ENG|07020202 | 11| | Call privileges for this command are deleted as well.
ENG|07020202 | 13|R | DELETE * deletes all stored commands of your own.
ENG|07020203 | 1| |
ENG|07020203 | 3| | C O P Y command :
ENG|07020203 | 5| | Copies a stored command.
ENG|07020203 | 7| | SYNTAX : COPY [<owner>.]<command_name> <new_name>
ENG|07020203 | 9| I| A command acquired via ##07020204GRANT can be made a command of your
ENG|07020203 | 10| | own by copying it by means of COPY, providing you are
ENG|07020203 | 11| | RESOURCE or DBA user. COPY safeguards the command against
ENG|07020203 | 12| I| being withdrawn by the owner via ##07020205REVOKE. Also own commands
ENG|07020203 | 13|R | may be copied.
ENG|07020204 | 1| |
ENG|07020204 | 3| | G R A N T command :
ENG|07020204 | 5| | Assigns the call privilege for a stored command to another
ENG|07020204 | 6| | user.
ENG|07020204 | 8| | SYNTAX : GRANT <command_name> [TO] <user_name>
ENG|07020204 | 10| | The privilege can only be granted (and revoked) by the
ENG|07020204 | 11| | owner of the command.
ENG|07020204 | 13| | If a user name is specified, the user concerned gets the
ENG|07020204 | 14|R | explicit call privilege.
ENG|07020205 | 1| |
ENG|07020205 | 3| | R E V O K E command :
ENG|07020205 | 5| | Revokes the call privilege for a stored command from
ENG|07020205 | 6| | another user.
ENG|07020205 | 8| | SYNTAX : REVOKE <command_name> [FROM] <user_name> | PUBLIC
ENG|07020205 | 10| | Only the owner of the command may grant and revoke this
ENG|07020205 | 11| | privilege.
ENG|07020205 | 13| | If a user is specified, this one loses the explicit call
ENG|07020205 | 14|R | privilege.
ENG|07020206 | 1| |
ENG|07020206 | 3| | E X P O R T command :
ENG|07020206 | 5| | Extracts stored commands into a file.
ENG|07020206 | 7| | SYNTAX : EXPORT [<command_name>] <file_identifier>
ENG|07020206 | 9| | If no command name is specified, all stored commands of
ENG|07020206 | 10| | your own are written into the specified file under their
ENG|07020206 | 11| | names.
ENG|07020206 | 13| | If command names are specified which may also contain
ENG|07020206 | 14| | wildcard arguments (*, ?), all commands which match the
ENG|07020206 | 15| | pattern are exported to the file.
ENG|07020206 | 17|S | examples:
ENG|07020206 | 19| | EXPORT command.fil
ENG|07020206 | 20| | EXPORT t* command.fil
ENG|07020206 | 21|R | EXPORT rep??? command.fil
ENG|07020207 | 1| |
ENG|07020207 | 3| | I M P O R T command :
ENG|07020207 | 5| | Loads named commands from a file into the database.
ENG|07020207 | 7| | SYNTAX : IMPORT <file identifier>
ENG|07020207 | 9| | The file to be loaded contains a sequence of named
ENG|07020207 | 10| | commands.
ENG|07020207 | 12| | The first line of each command consists of the keyword
ENG|07020207 | 13| | 'COMMAND' followed by the command name which may be used
ENG|07020207 | 14| | for calling it.
ENG|07020207 | 16| I| ##07020204GRANT commands giving other users call privileges can be
ENG|07020207 | 17|S | specified at the end of the file, separated by the keyword
ENG|07020207 | 19| | 'USERPRIV'.
ENG|07020207 | 21| | example:
ENG|07020207 | 23| | COMMAND customerreport
ENG|07020207 | 24| | select * from customer
ENG|07020207 | 25| I| ##07020301REPORT
ENG|07020207 | 26| I| ##0819TOTAL AVG account
ENG|07020207 | 27| I| ##0814WIDTH 1 10
ENG|07020207 | 28| I| ##0815EXCLUDE 2
ENG|07020207 | 29| | COMMAND hotellist
ENG|07020207 | 30| | select * from hotel
ENG|07020207 | 31| I| ##07020301REPORT
ENG|07020207 | 32| I| ##0831PRINT ONLY
ENG|07020207 | 33| | USERPRIV
ENG|07020207 | 34|RI| ##07020204GRANT hotellist TO Miller
ENG|07020208 | 1| |
ENG|07020208 | 3| | L I S T command :
ENG|07020208 | 5| | Displays the stored commands.
ENG|07020208 | 7| | SYNTAX : LIST [<owner>.<command_name>]
ENG|07020208 | 9| | LIST without parameters displays a list of all stored
ENG|07020208 | 10| | commands available.
ENG|07020208 | 12| | LIST with command name specifications displays the stored
ENG|07020208 | 13| | command on the screen. If <owner> or <command name>
ENG|07020208 | 14| | contains an '*', '?', or '%', a subset of the stored
ENG|07020208 | 15| | commands which are available is listed (see examples).
ENG|07020208 | 17|S | examples : LIST * lists all the names of the user's own
ENG|07020208 | 19| | stored commands
ENG|07020208 | 20| | LIST M* lists all the names of the user's own
ENG|07020208 | 21| | stored commands beginning with 'M'
ENG|07020208 | 22| | LIST *S* lists all the names of the user's own
ENG|07020208 | 23| | stored commands containing an 'S'
ENG|07020208 | 24| | LIST M??? lists all the names of the user's own
ENG|07020208 | 25| | stored commands which begin with an 'M'
ENG|07020208 | 26| | and are exactly three characters long
ENG|07020208 | 27| | LIST *.* lists all the names of the stored
ENG|07020208 | 28| | comands for which the user has got
ENG|07020208 | 29| | the call privilege (foreign commands)
ENG|07020208 | 30| | LIST M*.S* lists all the names of the foreign
ENG|07020208 | 31| | commands having user names beginning
ENG|07020208 | 32| | with 'M' and command names beginning
ENG|07020208 | 33|R | with 'S'
ENG|07020209 | 1| |
ENG|07020209 | 3| | E D I T command :
ENG|07020209 | 5| | Displays the input area from the LIST mode.
ENG|07020209 | 7| | SYNTAX : EDIT [<owner>.<command name>]
ENG|07020209 | 9| | EDIT without parameters displays an empty input form.
ENG|07020209 | 11| | EDIT with command name specification displays the stored
ENG|07020209 | 12|R | command on the screen.
ENG|070203 | 1| |
ENG|070203 | 3| | P R E P A R I N G R E P O R T :
ENG|070203 | 5| M| #01 REPORT
ENG|070203 | 6| M| #02 REPORT OFF
ENG|070203 | 7| M| #03 REPORT ONLY
ENG|070203 | 8|RM| #04 REPORT <name of a result set>
ENG|07020301 | 1| |
ENG|07020301 | 3| | R E P O R T command :
ENG|07020301 | 5| | Allows REPORT commands be formulated already in the input
ENG|07020301 | 6| | area, together with the SELECT statement.
ENG|07020301 | 8| | SYNTAX : <SELECT statement>
ENG|07020301 | 9| | REPORT
ENG|07020301 | 10| | <report command>
ENG|07020301 | 11| | [<report command>]...
ENG|07020301 | 13| | Each line of the edit form placed after the keyword REPORT
ENG|07020301 | 14|R | is treated like a REPORT command line.
ENG|07020302 | 1| |
ENG|07020302 | 3| | R E P O R T O F F command :
ENG|07020302 | 5| | Suppresses the display of results for SELECT statements.
ENG|07020302 | 7| | SYNTAX : REPORT OFF
ENG|07020302 | 9| | If the option OFF is specified after the keyword REPORT,
ENG|07020302 | 10| | the SELECT statement is executed, but the result is not
ENG|07020302 | 11| | output in tabular format. Thus temporary results can be
ENG|07020302 | 12|R | generated, only having displayed the desired total result.
ENG|07020303 | 1| |
ENG|07020303 | 3| | R E P O R T O N L Y command :
ENG|07020303 | 5| | Displays the last result without reexecuting the SELECT.
ENG|07020303 | 7| | SYNTAX : REPORT ONLY
ENG|07020303 | 9| | If the option ONLY is specified after the keyword REPORT,
ENG|07020303 | 10| | then a previous SELECT statement which might have been
ENG|07020303 | 11| | specified is not executed, but the report is constructed
ENG|07020303 | 12| | from the result table generated most recently. By this
ENG|07020303 | 13| | means it is possible to correct incorrect REPORT commands
ENG|07020303 | 14| | without having to wait again for the result of a SELECT
ENG|07020303 | 15|R | whose exeuction would take a long time.
ENG|07020304 | 1| |
ENG|07020304 | 3| | R E P O R T <name> command :
ENG|07020304 | 5| | Displays the result set generated with the <name>.
ENG|07020304 | 7| | SYNTAX : REPORT <name of a result set>
ENG|07020304 | 9| | If the name of a result set is specified after the keyword
ENG|07020304 | 10| | REPORT, then a previous SELECT statement which might have
ENG|07020304 | 11| | been specified is not executed, but the report is
ENG|07020304 | 12| | constructed by using the result set name. By this means it
ENG|07020304 | 13| | is possible to generate different REPORTs without having to
ENG|07020304 | 14| | wait again for the result of a SELECT whose execution would
ENG|07020304 | 15|R | take a long time.
ENG|070205 | 1| |
ENG|070205 | 3| | O T H E R C O M M A N D S :
ENG|070205 | 4| M| #01 SET
ENG|070205 | 5| M| #02 RESET
ENG|070205 | 6| M| #03 HELP
ENG|070205 | 7| M| #04 EXIT
ENG|070205 | 8| M| #05 RUN
ENG|070205 | 9| M| #06 USE
ENG|070205 | 10| M| #07 SQLMODE
ENG|070205 | 11| M| #08 SQLTIME
ENG|070205 | 12|RM| #09 DATE
ENG|07020501 | 1| |
ENG|07020501 | 3| | S E T command :
ENG|07020501 | 5| | Displays the control parameters.
ENG|07020501 | 7| | SYNTAX : SET
ENG|07020501 | 9| | The SET command enables the individual setting of control
ENG|07020501 | 10| | parameters. The control parameters determine, among other
ENG|07020501 | 11| | things, the default format of tables when being displayed
ENG|07020501 | 12| | by REPORT. These parameters can be altered by overwriting
ENG|07020501 | 13| | them.
ENG|07020501 | 15| | Input is checked and may be rejected, if e.g. a number has
ENG|07020501 | 16| | been entered instead of letters. The altered values remain
ENG|07020501 | 17|S | valid beyond the session's end.
ENG|07020501 | 19| | The SET parameters can temporarily be overridden for the
ENG|07020501 | 20| | current result table by means of the REPORT commands
ENG|07020501 | 21| I| ##0824DECIMAL, ##0817SEPARATOR, ##0823NULL, and ##0831PRINT.
ENG|07020501 | 23| | The meanings of the individual SET parameters
ENG|07020501 | 24|R | are described in detail in the QUERY manual.
ENG|07020502 | 1| | ~
ENG|07020502 | 3| | R E S E T command :
ENG|07020502 | 5| | Clears the input area and the command line.
ENG|07020502 | 7|R | SYNTAX : RESET
ENG|07020503 | 1| |
ENG|07020503 | 3| | H E L P command :
ENG|07020503 | 5| | Displays explanations on the screen.
ENG|07020503 | 7| | SYNTAX : HELP [ <command name> ]
ENG|07020503 | 9| | HELP without command name displays a menu. HELP with
ENG|07020503 | 10|R | command name directly displays the pertinent information.
ENG|07020504 | 1| |
ENG|07020504 | 3| | E X I T command :
ENG|07020504 | 5| | Terminates a QUERY session.
ENG|07020504 | 7| | SYNTAX : EXIT
ENG|07020504 | 9| | EXIT leaves QUERY. All modifications are recorded in the
ENG|07020504 | 10|R | database.
ENG|07020505 | 1| |
ENG|07020505 | 3| | R U N command :
ENG|07020505 | 5| | Performs a database command.
ENG|07020505 | 7| | SYNTAX : RUN
ENG|07020505 | 8| | RUN <command_name> [ <parameter> ... ]
ENG|07020505 | 9| | RUN <owner.command_name> [ <parameter> ... ]
ENG|07020505 | 11| | RUN without command name performs the database command
ENG|07020505 | 12| | currently displayed in the input area.
ENG|07020505 | 14| | The command name must designate a stored command. If the
ENG|07020505 | 15| | command is not the user's own command, its name must be
ENG|07020505 | 16| | preceded by a prefix consisting of the owner name followed
ENG|07020505 | 17|S | by a dot. In LIST mode it is sufficient to specify the
ENG|07020505 | 19| | consecutive number which, on display, is placed before the
ENG|07020505 | 20| | command name.
ENG|07020505 | 22| | If parameters are specified, the i-th value replaces &i
ENG|07020505 | 23| | (%i) in the stored command.
ENG|07020505 | 25| | example : RUN customer_report 'Berlin' 3
ENG|07020505 | 27| | Parameters can also be entered via a form. The layout of
ENG|07020505 | 28| | the form is specified within the stored command.
ENG|07020505 | 30| | example :
ENG|07020505 | 31| | LAYOUT
ENG|07020505 | 32| | customer inquiry:
ENG|07020505 | 33| | -----------------
ENG|07020505 | 34| | customer no : &1
ENG|07020505 | 35|S | ENDLAYOUT
ENG|07020505 | 37| | SELECT firstname, lastname
ENG|07020505 | 38| | FROM customer WHERE customerno = &1
ENG|07020505 | 40| | Up to 16 parameters may be specified.
ENG|07020505 | 42| | The form layout must always be placed at the beginning of
ENG|07020505 | 43|R | the stored command.
ENG|07020506 | 1| |
ENG|07020506 | 3| | U S E command :
ENG|07020506 | 5| | Changes to another database or another user.
ENG|07020506 | 7| | SYNTAX : USE [SERVERDB] <db> [ON <node>]
ENG|07020506 | 8| | USE USER <user> <password> [SERVERDB <db> [ON <node>]]
ENG|07020506 | 9| | USE USERKEY <xuserkey>
ENG|07020506 | 11| | The USE command terminates the database session and opens a
ENG|07020506 | 12| | new one with another user name.
ENG|07020506 | 14| | The keyword USER is followed by the name and password of
ENG|07020506 | 15| | the new user, or the keyword SERVERDB is followed by the
ENG|07020506 | 16|S | database name and after ON by the database node, if needed.
ENG|07020506 | 19| | USERKEY allows an entry in the XUSER file to be accessed.
ENG|07020506 | 21| | USER, PASSWORD and USERKEY name must be enclosed in single
ENG|07020506 | 22| | quotes, if they are not to be converted into upper case
ENG|07020506 | 23|R | characters.
ENG|07020507 | 1| |
ENG|07020507 | 3| | S Q L M O D E command :
ENG|07020507 | 5| | Changes the SQLMODE in which QUERY is working.
ENG|07020507 | 7| | SYNTAX : SQLMODE [ <mode> ]
ENG|07020507 | 9| | With the SQLMODE command the user can change or display the
ENG|07020507 | 10| | SQLMODE in which QUERY is working.
ENG|07020507 | 12|R | Valid modes are: ADABAS, ORACLE, DB2, ANSI
ENG|07020508 | 1| |
ENG|07020508 | 3| | S Q L T I M E command :
ENG|07020508 | 5| | Displays the required time for the last SQL statement.
ENG|07020508 | 7| | SYNTAX : SQLTIME ON | OFF
ENG|07020508 | 9| | By means of the SQLTIME command the user can have displayed
ENG|07020508 | 10| | the runtime of the last SQL statement.
ENG|07020508 | 12| | The implicit runtimes of a subsequent REPORT output will be
ENG|07020508 | 13| | included in the displayed value.
ENG|07020508 | 15| | These times will be inserted into the protocol file, if
ENG|07020508 | 16|S | necessary.
ENG|07020508 | 19| | In the Windows Version of QUERY it will only be inserted in
ENG|07020508 | 20| | the protocol file.
ENG|07020508 | 22|R | SQLTIME OFF disables the function.
ENG|07020509 | 1| |
ENG|07020509 | 3| | D A T E command :
ENG|07020509 | 5|R | Displays the current date and the current time.
ENG|070206 | 1| |
ENG|070206 | 2| | C R O S S T A B F U N C T I O N :
ENG|070206 | 4| | The CROSSTAB function allows the fixed row structure of the
ENG|070206 | 5| | database table to be broken up. Starting from an existing
ENG|070206 | 6| | table, a new one will be created in which the columns
ENG|070206 | 7| | result from the rows of the starting table. Thus a new,
ENG|070206 | 8| | clear representation of the pieces of information may be
ENG|070206 | 9| | obtained.
ENG|070206 | 11| | SYNTAX : CROSSTAB <table name> TO <table name>
ENG|070206 | 12| | COLUMN <column name> ROW <column name> DATA <expression>
ENG|070206 | 13| | [ PREFIX <character string> ] [ DEFAULT <character string> ]
ENG|070206 | 15| | The first table name indicates the starting table, the
ENG|070206 | 16|S | second table name the table to be created.
ENG|070206 | 19| | COLUMN denotes the name of the column in the starting table
ENG|070206 | 20| | the entries of which give the column names in the new
ENG|070206 | 21| | table. The starting table is sorted and grouped according
ENG|070206 | 22| | to the ROW column which will be inserted in the new table.
ENG|070206 | 24| | The expression after the keyword DATA indicates the column
ENG|070206 | 25| | and the arithmetic operation related to it which produce
ENG|070206 | 26| | the contents of the new table rows.
ENG|070206 | 28| | Column name prefixes and default values may be specified
ENG|070206 | 29| | optionally. The PREFIX character string will be placed in
ENG|070206 | 30| | front of the newly created column names. This is especially
ENG|070206 | 31| | useful for numeric output columns. The DEFAULT value will
ENG|070206 | 32| | be entered in the table, when the column of the starting
ENG|070206 | 33| | table does not contain any value.
ENG|070206 | 35|S | Example :
ENG|070206 | 37| E|
ENG|070206 | 38| E| CROSSTAB travel_expenses TO expense_stat
ENG|070206 | 39| E| COLUMN calendar week
ENG|070206 | 40| E| ROW employee
ENG|070206 | 41| E| DATA fixed(sum(travel_expenses), 7, 2)
ENG|070206 | 42| E| DEFAULT 0 PREFIX CW_
ENG|070206 | 43|RE| E$}
