38.7.2 Data manipulation SQL statements (SELECT statement)

If the SELECT statement is executed, the search results are displayed in the following format:

COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
SELECT 'C1 = ',"C1" FROM "T1";
 EXP0001_NO_NAME C1                                      ...1
 --------------- ----------------                        ...2
           C1 =   -123456789.1234                        ...3
           C1 =  ****************                        ...4
KFAA96404-I 2 rows were selected.                        ...5
KFAA96415-I SQL processing time (Fetch) : 0.477001 sec.  ...6

Explanation:

Displays the result of the query expression and the column names for the derived table. For details about the column names in derived tables (retrieval item column names), see Rules in Specification format and rules for the SELECT statement in SELECT (retrieve rows) in Data Manipulation SQL in the manual HADB SQL Reference.
Multiple hyphens (-) are successively displayed for each selection expression. If ON (display the retrieval results in hexadecimal format) is specified in #SET DUMPMODE, equal signs (=) are successively displayed.

The number of hyphens (-) or equal signs (=) indicates the width that can be displayed in the retrieval results. Hyphens (-) or equal signs (=) are displayed such that one of the following lengths, whichever is longer, is reached:

• Maximum length when column data is expressed in character format

• Length of the retrieval item column name
Displays the search results. If a data conversion scalar function is specified in the selection expression, the search results might not be displayed correctly due to truncation of trailing bytes of multi-byte characters. Note also that if the CHR scalar function's target data is in the range from 127 to 159, the search results might not be displayed correctly. For details about the data conversion functions, see Data conversion functions in Scalar Functions in the manual HADB SQL Reference.
When the retrieval result is a null value, the character string to be displayed for a null value, specified in #SET NULL, is displayed. However, if the character string to be displayed for a null value exceeds the maximum data length for retrieval results, the character string is truncated in the middle.

The maximum length of the character string to be displayed for a null value is the same as the length of the hyphens (-) or equal signs (=) that are displayed in explanation 2. above.
Displays the number of rows in the search results. If the number of rows exceeds the maximum value, three asterisks (***) are displayed.
If ON (output SQL statement execution time) is specified in #SET EXECTIME REPORT, SQL statement execution time is output.

The following table describes the search results display formats.

Table 38‒10: Search results display formats
No.	Data type of search results	Search results display format^#1
1	`INTEGER`	Right-aligned, shortest literal format for integer data. For positive numbers, a plus sign (`+`) is not displayed. For negative numbers, a minus sign (`-`) is displayed immediately before the valid data.
2	`SMALLINT`
3	`DECIMAL`^#3	For the fraction portion, the scaling of the results of the derived column is displayed right-aligned. If the scaling of the results of the derived column is 0 and the `-d` option is specified, the decimal point is not output. The integer portion is displayed in the shortest format. For positive numbers, a plus sign (`+`) is not displayed. For negative numbers, a minus sign (`-`) is displayed immediately before the valid data.
4	`DOUBLE` `PRECISION`	The exponent portion is displayed in the shortest format. The mantissa portion is always displayed as 17 digits. The entire data is displayed right-aligned. For positive numbers, a plus sign (`+`) is not displayed. For negative numbers, a minus sign (`-`) is displayed immediately before the valid data.
5	`CHARACTER`^#5	Literal format of each data type without the single quotation marks (`'`). A colon (`:`) is displayed for a character that cannot be displayed ((`X'00'` to `X'1F'`).
6	`VARCHAR`^#6
7	`DATE`^#5	Predefined output representation for date data. For details about the predefined output representation for date data, see Predefined character-string representations in the manual HADB SQL Reference.
8	`TIME`^#5	Predefined output representation for time data. For details about the predefined output representation for time data, see Predefined character-string representations in the manual HADB SQL Reference.
9	`TIMESTAMP`^#5	Predefined output representation for time stamp data. For details about the predefined output representation for time stamp data, see Predefined character-string representations in the manual HADB SQL Reference.
10	`BINARY`^{#4, #5}	If `BINARY` is specified in a selection expression, the value is displayed in either of the following formats: Hexadecimal format Binary format
11	`VARBINARY`^{#4, #6}	If `VARBINARY` is specified in a selection expression, the value is displayed in either of the following formats: Hexadecimal format Binary format
12	`ROW`^{#2, #5}	Hexadecimal format if `ROW` is specified in the selection expression

#1: If the data cannot be converted to character format, as many question marks (?) as matches the maximum length of the data in the character format are displayed.
#2: If ROW is specified in the selection expression, this is the data type.
#3: For the DECIMAL type, if the precision and scaling are the same, leading zeros are added. However, if the -s option was specified and the precision and scaling are both 38, no zeros are added.
#4: The display format to be used is determined by the -b option of the adbsql command or specification of #SET BINARY_DISP_MODE. If ON is specified in #SET DUMPMODE (displays retrieval results in hexadecimal format), the hexadecimal format is used regardless of the specification of #SET BINARY_DISP_MODE.
#5: If the maximum length of data that is represented in string format is shorter than the maximum column width, the data displayed in the column is right-aligned. In other cases, the displayed data is left-aligned.
#6: If the maximum length of data that is represented in string format is shorter than the maximum column width, the difference between the maximum string data length and column width is filled with spaces to the left. In other cases, the displayed data is left-aligned.

To Page Top