Entering content frame

This graphic is explained in the accompanying text Converting Values Locate the document in the library structure

For different application cases, you may need to change how values are represented because the operator used can deal only with a specific representation of values.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

The SQL Reference Manual describes the following functions for these purposes: Structure linkCHR(a,n), Structure linkNUM(a), Structure linkUPPER/LOWER(x), Structure linkASCII(x), Structure linkMAPCHAR(x,n,i), Structure linkALPHA(x,n), Structure linkHEX(a), Structure linkCHAR(a,t), Structure linkSOUNDEX(x), Structure linkVALUE(x,y,...), Structure linkDECODE(x,y(i),...,z)

Examples

SELECT * FROM hotel.hotel
  WHERE CHR(hno) LIKE '1__'

Displaying the hotel entries whose numbers contain three digits and begin with 1. The hotel number is converted to character values for this purpose.

Result

HNO

NAME

ZIP

ADDRESS

100

Beach

32018

1980 34th St.

110

Atlantic

33441

111 78th St.

120

Long Beach

90804

35 Broadway

130

Star

90029

13 Beechwood Place

140

River Boat

70112

788 MAIN STREET

150

Indian Horse

92262

16 MAIN STREET

 

See also:

SQL Reference Manual, Structure linkCHR(a,n)

 

SELECT * FROM hotel.hotel
WHERE UPPER(name) = 'STAR'

Displaying the hotel name (case insensitive)

Result

HNO

NAME

ZIP

ADDRESS

130

Star

90029

13 Beechwood Place

 

See also:

SQL Reference Manual, Structure linkUPPER/LOWER(x)

 

SELECT VALUE(firstname, 'Company') firstname, name
  FROM hotel.customer
    WHERE firstname IS NULL

Displaying the company name; the NULL value is to be replaced by the name Company

Result

FIRSTNAME

NAME

Company

Datasoft

Company

Toolware

 

See also:

SQL Reference Manual, Structure linkVALUE(x,y,...)

 

SELECT hno, price, DECODE(type, 'single', 1, 'double', 2, 'suite', 3) code
  FROM hotel.room
    WHERE hno < 60

Displaying the hotel rooms with another name for the room types

Result

HNO

PRICE

CODE

10

200

2

10

135

1

20

100

2

20

70

1

30

80

2

30

45

1

40

140

2

40

85

1

50

180

2

50

105

1

50

500

3

See also:

SQL Reference Manual, Structure linkDECODE(x,y(i),...,z)

More examples for Data Query

 

Leaving content frame