Formatting SELECT Query Results
Formatting SELECT Query Results#
The results of a SELECT query can be formatted as desired by the user.
SET LINESIZE#
Sets the size (number of characters) of one line to be displayed when the results of a SELECT statement are output. It must be between 10 and 32767.
iSQL> SET LINESIZE 100; -- Set the display size of one line to 100.
SET LOBSIZE#
This specifies the number of characters to display when a CLOB column is queried using a SELECT statement.
In order to query CLOB column data using a SELECT statement, the transaction mode must first be set to AUTOCOMMIT OFF.
CREATE TABLE C1(I1 INTEGER, I2 CLOB);
INSERT INTO C1 VALUES(1, 'A123456789');
INSERT INTO C1 VALUES(2, 'A1234');
INSERT INTO C1 VALUES(3, 'A12345');
INSERT INTO C1 VALUES(4, 'A1234567890123');
iSQL> autocommit off; -- This sets the transaction mode to OFF so that a CLOB column can be queried.
Set autocommit off success.
iSQL> select * from c1;
C1.I1 C1.I2
---------------------------
1 A123456789
2 A1234
3 A12345
4 A1234567890123
4 rows selected.
iSQL> set lobsize 10; -- This specifies the number of characters to display on the screen when querying a CLOB column using a SELECT statement
iSQL> select * from c1;
C1.I1 C1.I2
--------------------------
1 A123456789
2 A1234
3 A12345
4 A123456789
4 rows selected.
SET LOBOFFSET#
This specifies the starting location from which to display CLOB data when a CLOB column is queried using a SELECT statement.
In order to query CLOB column data using a SELECT statement, the transaction mode must first be set to AUTOCOMMIT OFF.
CREATE TABLE C1(I1 INTEGER, I2 CLOB);
INSERT INTO C1 VALUES(1, 'A123456789');
INSERT INTO C1 VALUES(2, 'A1234');
INSERT INTO C1 VALUES(3, 'A12345');
INSERT INTO C1 VALUES(4, 'A1234567890123');
iSQL> autocommit off;
Set autocommit off success.
iSQL> set loboffset 4; -- This specifies the starting location of data to be shown on the screen number of characters to skip) when querying a CLOB column using a SELECT statement.
iSQL> select * from c1;
C1.I1 C1.I2
--------------------------
1 456789
2 4
3 45
4 4567890123
4 rows selected.
SET FEEDBACK#
Outputs the number of records found when the results of a SELECT statement are output.
SET FEEDBACK ON|OFF|n
- ON: Output the number of resultant records after execution of a SELECT statement.
- OFF: Do not output the number of resultant records after execution of a SELECT statement.
- n: Output the number of resultant records when the number is n or greater.
iSQL> SET FEEDBACK ON;
iSQL> SELECT * FROM employees WHERE ENO < 3;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
1 Moon Chan-seung CEO 0119566236 3002 M R
5
2 Davenport Susan designer 0113654540 1500 F 721219 18-NOV-2009 H
2 rows selected.
SET PAGESIZE#
Specifies the number of resultant rows to display at one time.
iSQL> SET PAGESIZE 2; -- Show results in groups comprising two rows each
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
1 Moon Chan-seung CEO 0119566236 3002 M R
5
2 Davenport Susan designer 0113654540 1500 F 721219 18-NOV-2009 H
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
3 Kobain Ken engineer 0162581369 1001 2000 M 650226 11-JAN-2010 H
4 Foster Aaron PL 0182563984 3001 1800 M 820730 H
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
5 Ghorbani Farhad PL 0114558231 3002 2500 M 20-DEC-2009 H
0
6 Momoi Ryu programmer 0197853222 1002 1700 M 790822 09-SEP-2010 H
.
.
.
20 rows selected.
iSQL> SET PAGESIZE 0; -- Show all of the results on one page.
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
1 Moon Chan-seung CEO 0119566236 3002 M R
5
2 Davenport Susan designer 0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer 0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
SET HEADING#
Sets whether to output the header with a SELECT result.
iSQL> SET HEADING OFF; -- Header is not displayed with the result.
iSQL> SELECT * FROM employees;
1 Moon Chan-seung CEO 0119566236 3002 M R
5
2 Davenport Susan designer 0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer 0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
iSQL> SET HEADING ON; -- Outputs header in result.
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
1 Moon Chan-seung CEO 0119566236 3002 M R
5
2 Davenport Susan designer 0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer 0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
SET COLSIZE#
When the results of a SELECT statement are output, sets the number of characters from a column of type CHAR or VARCHAR to display so that columns containing long lines of text can be easily viewed.
iSQL> CREATE TABLE LOCATION(
ID INTEGER,
NAME CHAR(20),
ADDRESS VARCHAR(500),
PHONE CHAR(20));
Create success.
iSQL> INSERT INTO LOCATION VALUES(1, 'ALTIBASE', 'Inyoung Bldg, 5fl 44-11 Youido-dong Youngdungpo-qu seoul, 150-890. Korea', '82-2-769-7500');
1 row inserted.
In the following example, the number of characters of a column of type CHAR or VARCHAR is set to 7:
iSQL> SET COLSIZE 7;
iSQL> SELECT ID,NAME,ADDRESS,PHONE FROM LOCATION;
ID NAME ADDRESS PHONE
--------------------------------------------
1 ALTIBAS 10Fl., 82-2-20
E Daerung 82-1000
post-to
wer II,
Guro-d
ong, Gu
ro-qu,
Seoul 1
52-790.
Korea
1 row selected.
SET NUM[WIDTH]#
This command sets the number of characters to display for data of NUMERIC, DECIMAL, NUMBER, and FLOAT columns in SELECT result sets. Data with many significant digits can be made more legible by setting this value high.
The following example sets NUMWIDTH to 30, and then queries NUMERIC, DECIMAL, NUMBER, and FLOAT columns.
iSQL> CREATE TABLE t1
(
c_numeric NUMERIC(38, 0),
c_decimal DECIMAL(38, 0),
c_number NUMBER(38, 0),
c_float FLOAT(38)
);
Create success.
iSQL> INSERT INTO t1 VALUES(12345678901234567890, 12345678901234567890, 12345678901234567890, 12345678901234567890);
1 row inserted.
iSQL> SET NUMWIDTH 30
iSQL> SELECT c_numeric, c_decimal, c_number, c_float FROM t1;
C_NUMERIC C_DECIMAL
-----------------------------------------------------------
C_NUMBER C_FLOAT
-----------------------------------------------------------
12345678901234567890 12345678901234567890
12345678901234567890 12345678901234567890
1 row selected.
SET NUMF[ORMAT]#
Syntax#
SET NUMF[ORMAT] format;
This command sets a format of NUMERIC, DECIMAL, NUMBER, and FLOAT type to display their SELECT results. It will take precedence over SET NUMWIDTH settings.
Refer to the "General Reference> Data Types > Numeric Data Types > Numeric" in order to grasp on the formatting on format.
The following is an example of viewing through an exponential form.
iSQL> create table t1(i1 float(30));
Create success.
iSQL> insert into t1 values (123456789012);
1 row inserted.
iSQL> SET NUMFORMAT 9.99EEEE
iSQL> select * from t1;
T1.I1
-------------
1.23E+11
1 rows selected.
CL[EAR] COL[UMNMS]#
This command releases the display format of all of the columns which have been specified by COLUMN commands.
Syntax#
CL[EAR] COL[UMNS]
COLUMN#
This command verifies or sets the display format for a target column of SELECT. The setting is applied to the following cases only.
- The length of character data type.
- The display format of numeric data type.
Syntax#
COL[UMN] [{column | expr} [option]]
column or expr should be indicating a target column or an expression, and it should be identical with the one used in the SELECT statement. Every specified column or the specified format can be confirmed by the COL[UMN] [{column | expr}] command.
The followings can be used in option.
| Option | Description |
|---|---|
| CLE[AR] | This option releases a specified column. |
| FOR[MAT] format | This option sets the display format for the specified column The character data type column: This type can set the display length of the CHAR and VARCHAR type. It will take precedence over SET COLSIZE settings. The numeric data type column: The display format of the NUMBER, DECIMAL, FLOAT, and NUMERIC type can be specified by this option. Refer to "General Reference > Data Types > Numeric Data Types > Numeric " for the available format which can be applied into. It will take precedence over SET NUMFORMAT settings. |
| ON|OFF | This option confirms whether or not to apply the specified display. OFF: OFF leaves the column setting as it is, however; it is not applied to output ON : The specified setting is applied. |
Description#
The display format of a target column in the SELECT statement can be specified. If multiple display formats are selected, the last format will be applied.
In order to release the display format, the user can use the CLEAR or OFF option. The differences between the CLEAR and OFF option is that the CLEAR option can completely remove the specified display setting whereas the OFF option executes the same except it is not applied to output
Example#
The following example demonstrates displaying the length of an address column in VARCHAR(60) with 20.
iSQL> @schema.sql
iSQL> COLUMN address FORMAT A20
iSQL> select cno, address from customers;
CNO ADDRESS
----------------------------------------------
1 2100 Exposition Boul
evard Los Angeles US
A
...
The following commands should be taken in order to delete the given setting.
iSQL> COLUMN address CLE