SQL GRAPHIC scalar function in DB2 for i SQL

SQL GRAPHIC scalar function in DB2 for i SQL

GRAPHIC

The GRAPHIC function returns a fixed-length graphic string of a value of different data type.

Integer to Graphic

GRAPHIC(integer-expression)

Integer expression can be SMALLINT, INTEGER, or BIGINT

Example#1:

SELECT graphic(00100) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC ( 00100 )
100              

Decimal to Graphic

DECIMAL(decimal-expression, decimal character)

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#2:

SELECT GRAPHIC(010.01,',') FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC
10,01  

The first parameter is an decimal data type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If decimal-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with decimal-expression.

SELECT GRAPHIC(-010.01) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC
-10.01 

Floating-point to Graphic

GRAPHIC(floating-point-expression, decimal-character)

Floating point expression can be DOUBLE or REAL

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#3:

SELECT GRAPHIC(-7.2E+75,',') FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC
-7,2E75

The first parameter is an floating type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If floating-point-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with floating-point-expression.

SELECT GRAPHIC(-7.2E+75) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC
-7.2E75

Decimal floating-point to Graphic

CHAR(decimal-floating-point-expression, decimal-character)

Decimal Floating point expression can be DECFLOAT

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#4:

SELECT GRAPHIC(-894.545442E-34,',') FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC                
-8,9454544200000005E-32

The first parameter is an decimal floating type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If decimal-floating-point-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with decimal-floating-point-expression.

SELECT GRAPHIC(-894.545442E-34) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC                
-8.9454544200000005E-32

Character to Graphic

GRAPHIC(character-expression, integer, codeunits16/CodeUnits32/Octets)

Example#5:

SELECT GRAPHIC('TESTDATA',4,1200) FROM SYSIBM.SYSDUMMY1 

Output:

GRAPHIC
TEST   

Graphic to Graphic

GRAPHIC(graphic-expression, integer, codeunits16/CodeUnits32)

Execute below queries to understand this.

CREATE TABLE QTEMP/TABLE1 (NAME GRAPHIC ( 10) CCSID 1200 NOT NULL
WITH DEFAULT);

INSERT INTO QTEMP/TABLE1 VALUES('TEST DATA');

Example#6

SELECT graphic(name,5, 1200) FROM qtemp.table1

Output:

GRAPHIC
TEST   

Datetime to Graphic

GRAPHIC(datetime-expression, ISO/USA/EUR/JIS/LOCAL)

Datetime expression can be date, time, or timestamp

Example#7:

Timestamp to Graphic

SELECT GRAPHIC('2005-11-16-15.11.15.544784',26,1200 ) FROM
SYSIBM.SYSDUMMY1                                          

Output:

GRAPHIC                   
2005-11-16-15.11.15.544784

Date to Graphic

SELECT GRAPHIC(DATE('2005-11-16'), USA) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC   
11/16/2005

Time to Graphic

SELECT GRAPHIC(TIME('15:10:25'), USA) FROM SYSIBM.SYSDUMMY1

Output:

GRAPHIC 
03:10 PM