WHILE

Use a WHILE statement is used to control flow. It allows code to be executed repeatedly as long as the WHILE condition is true. Optionally, you may use the WHILE statement with DO and END WHILE.

Note You cannot use a mixed syntax for the WHILE statement. You may use either WHILE with DO and END WHILE, or only WHILE.

If you are using multiple statements with a WHILE condition, you must use BEGIN and END to indicate the beginning and ending of the statement blocks.

Syntax

[ label-name : ] WHILE proc-search-condition [ DO ] [ proc-stmt [; proc-stmt ] ]...
[ END WHILE ][ label-name ]

Remarks

A WHILE statement can have a beginning label, in which case it is called a labeled WHILE statement.

Examples

The following example increments the variable vInteger by 1 until it reaches a value of 10, when the loop ends.

WHILE (:vInteger < 10) DO

SET :vInteger = vInteger + 1;

END WHILE

See Also

CREATE PROCEDURE

CREATE TRIGGER

Grammar Element Definitions

The following is an alphabetical list of element definitions used in the grammar syntax.

alter-options ::= alter-option-list1 | alter-option-list2

alter-option-list1 ::= alter-option |(alter-option [, alter-option ]...)

alter-option ::= ADD [ COLUMN ] column-definition

| ADD table-constraint-definition

| ALTER [ COLUMN ] column-definition

| DROP [ COLUMN ] column-name

| DROP CONSTRAINT constraint-name

| DROP PRIMARY KEY

| MODIFY [ COLUMN ] column-definition

alter-option-list2 ::= PSQL_MOVE [ COLUMN ] column-name TO [ [ PSQL_PHYSICAL ] PSQL_POSITION ] new-column-position | RENAME COLUMN column-name TO new-column-name

as-or-semicolon ::= AS | ;

before-or-after ::= BEFORE | AFTER

call-arguments ::= positional-argument [ , positional-argument ]...

col-constraint ::= NOT NULL

| NOT MODIFIABLE

| UNIQUE

| PRIMARY KEY

| REFERENCES table-name [ ( column-name ) ] [ referential-actions ]

collation-name ::= 'string'

column-constraint ::= [ CONSTRAINT constraint-name ] col-constraint

column-definition ::= column-name data-type [ DEFAULT Expression ] [ column-constraint [ column-constraint ]... [CASE | COLLATE collation-name ]

column-name ::= user-defined-name

commit-statement ::= see COMMIT statement

comparison-operator ::= < | > | <= | >= | = | <> | !=

constraint-name ::= user-defined-name

correlation-name ::= user-defined-name

cursor-name ::= user-defined-name

data-type ::= data-type-name [ (precision [ , scale ] ) ]

data-type-name ::= see PSQL Supported Data Types

db-name ::= user-defined-name

expression::= expression - expression

| expression + expression

| expression * expression

| expression / expression

| expression & expression

| expression | expression

| expression ^ expression

| ( expression )

| -expression

| +expression

| ~expression

| ?

| literal

| scalar-function

| { fn scalar-function }

| USER

literal ::= 'string' | N'string'

| number

| { d 'date-literal' }

| { t 'time-literal' }

| { ts 'timestamp-literal' }

scalar-function :: = See Scalar Functions

expression-or-subquery ::= expression | ( query-specification )

fetch-orientation ::= NEXT

group-name ::= user-defined-name

index-definition ::= ( index-segment-definition [ , index-segment-definition ]... )

index-name ::= user-defined-name

index-number ::= user-defined-value (an integer between 0 and 118)

index-segment-definition ::= column-name [ ASC | DESC ]

ins-upd-del ::= INSERT | UPDATE | DELETE

insert-values ::= values-clause

| query-specification

join-definition ::= table-reference [ INNER ] JOIN table-reference ON search-condition

| table-reference CROSS JOIN table-reference

| outer-join-definition

label-name ::= user-defined-name

literal ::= 'string' | N'string'

| number

| { d 'date-literal' }

| { t 'time-literal' }

| { ts 'timestamp-literal' }

order-by-expression ::= expression [ CASE | COLLATE collation-name ] [ ASC | DESC ]

outer-join-definition ::= table-reference outer-join-type JOIN table-reference ON search-condition

outer-join-type ::= LEFT [ OUTER ]| RIGHT [ OUTER ] | FULL [ OUTER ]

parameter ::= parameter-type-name data-type [ DEFAULT proc-expr | = proc-expr ]

| SQLSTATE

parameter-type-name ::= parameter-name

| parameter-type parameter-name

| parameter-name parameter-type

parameter-type ::= IN | OUT | INOUT | IN_OUT

parameter-name ::= [ : ] user-defined-name

password ::= user-defined-name | 'string'

positional-argument ::= expression

precision ::= integer

predicate ::= expression [ NOT ] BETWEEN expression AND expression

| expression comparison-operator expression-or-subquery

| expression [ NOT ] IN ( query-specification )

| expression [ NOT ] IN ( value [ , value ]... )

| expression [ NOT ] LIKE value

| expression IS [ NOT ] NULL

| expression comparison-operator ANY ( query-specification )

| expression comparison-operator ALL ( query-specification )

| EXISTS ( query-specification )

proc-expr ::= same as normal expression but does not allow IF expression, or scalar functions

proc-search-condition ::= same as search-condition but does not allow expressions with subqueries

proc-stmt ::= [ label-name : ] BEGIN [ ATOMIC ] [ proc-stmt [ ; proc-stmt ]... ] END [ label-name ]

| CALL procedure-name ( proc-expr [ , proc-expr ]... )

| CLOSE cursor-name

| DECLARE cursor-name CURSOR FOR select-statement [ FOR UPDATE | FOR READ ONLY ]

| DECLARE variable-name data-type [ DEFAULT proc-expr | = proc-expr ]

| DELETE WHERE CURRENT OF cursor-name

| delete-statement

| FETCH [ fetch-orientation [ FROM ] ] cursor-name [ INTO variable-name [ , variable-name ] ]

| IF proc-search-condition THEN proc-stmt [ ; proc-stmt ]... [ ELSE proc-stmt [ ; proc-stmt ]... ] END IF

| IF proc-search-condition proc-stmt [ELSE proc-stmt]

| insert-statement

| LEAVE label-name

| [ label-name : ] LOOP proc-stmt [ ; proc-stmt ]... END LOOP [ label-name ]

| OPEN cursor-name

| PRINT proc-expr [ , 'string' ]

| RETURN [ proc-expr ]

| transaction-statement

| select-statement-with-into

| select-statement

| SET variable-name = proc-expr

| SIGNAL [ ABORT ] sqlstate-value

| START TRANSACTION [tran-name]

| update-statement

| UPDATE SET column-name = proc-expr [ , column-name = proc-expr ]... WHERE CURRENT OF cursor-name

| [ label-name : ] WHILE proc-search-condition DO [ proc-stmt [ ; proc-stmt ] ]... END WHILE [ label-name ]

| [ label-name : ] WHILE proc-search-condition proc-stmt

| alter-table-statement

| create-index-statement

| create-table-statement

| create-view-statement

| drop-index-statement

| drop-table-statement

| drop-view-statement

| grant-statement

| revoke-statement

| set-statement

procedure-name ::= user-defined-name

public-or-user-group-name ::= PUBLIC | user-group-name

query-specification [ [ UNION [ ALL ] query-specification ]...

[ limit-clause ][ ORDER BY order-by-expression [ , order-by-expression ]... ] [ FOR UPDATE ]

query-specification ::= ( query-specification )

| SELECT [ ALL | DISTINCT ] [ top-clause ] select-list

FROM table-reference [ , table-reference ]...

[ WHERE search-condition ]

[ GROUP BY expression [ , expression ]...

[ HAVING search-condition ] ]

referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ]

| NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ]

referential-actions ::= referential-update-action [ referential-delete-action ]

| referential-delete-action [ referential-update-action ]

referential-update-action ::= ON UPDATE RESTRICT

referential-delete-action ::= ON DELETE CASCADE

| ON DELETE RESTRICT

release-statement ::= see RELEASE statement

result ::= user-defined-name data-type

rollback-statement ::= see ROLLBACK WORK statement

savepoint-name ::= user-defined-name

scalar-function ::= see Scalar Function list

scale ::= integer

search-condition ::= search-condition AND search-condition

| search-condition OR search-condition

| NOT search-condition

| ( search-condition )

| predicate

select-item ::= expression [ [ AS ] alias-name ] | table-name.*

select-list ::= * | select-item [ , select-item ]...

set-function ::= AVG ( [ DISTINCT | ALL ] expression )

| COUNT ( < * | [ DISTINCT | ALL ] expression > )

| COUNT_BIG ( < * | [ DISTINCT | ALL ] expression > )

| MAX ( [ DISTINCT | ALL ] expression )

| MIN ( [ DISTINCT | ALL ] expression )

| STDEV ( [ DISTINCT | ALL ] expression )

| STDEVP ( [ DISTINCT | ALL ] expression )

| SUM ( [ DISTINCT | ALL ] expression )

| VAR ( [ DISTINCT | ALL ] expression )

| VARP ( [ DISTINCT | ALL ] expression )

sqlstate-value ::= 'string'

table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint

table-constraint ::= UNIQUE (column-name [ , column-name ]... )

| PRIMARY KEY ( column-name [ , column-name ]... )

| FOREIGN KEY ( column-name [ , column-name ] )

REFERENCES table-name

[ ( column-name [ , column-name ]... ) ]

[ referential-actions ]

table-element ::= column-definition

| table-constraint-definition

table-expression ::=

FROM table-reference [ , table-reference ]...

[ WHERE search-condition ]

[ GROUP BY expression [ , expression ]...

[ HAVING search-condition ]

table-name ::= user-defined-name

table-permission ::= ALL

| SELECT [ ( column-name [ , column-name ]... ) ]

| UPDATE [ ( column-name [ , column-name ]... ) ]

| INSERT [ ( column-name [ , column-name ]... ) ]

| DELETE

| ALTER

| REFERENCES

table-reference ::= { OJ outer-join-definition }

| [db-name.]table-name [ [ AS ] alias-name ]

| join-definition

| ( join-definition )

table-subquery ::= query-specification [ [ UNION [ ALL ]
query-specification ]...][ limit-clause ][ ORDER BY order-by-expression [ , order-by-expression ]...]

limit-clause ::= [ LIMIT [offset,] row_count | row_count OFFSET offset | ALL [OFFSET offset] ]

offset ::= number | ?

row_count ::= number | ?

transaction-statement ::= commit-statement

| rollback-statement

| release-statement

trigger-name ::= user-defined-name

user_and_password ::= user-name [ : ] password

user-group-name ::= user-name | group-name

user-name ::= user-defined-name

value ::= literal | USER | NULL | ?

value-list ::= ( value [ , value ]... )

values-clause ::= DEFAULT VALUES | VALUES ( expression [ , expression ]... )

variable-name ::= user-defined-name

view-name ::= user-defined-name

SQL Statement List

SqlStatementList is defined as:

SqlStatementList

statement ';' | SqlStatementList ';'

statement ::= statement-label ':' statement

| BEGIN ... END block

| CALL statement

| CLOSE CURSOR statement

| COMMIT statement

| DECLARE CURSOR statement

| DECLARE variable statement

| DELETE statement

| FETCH statement

| IF statement

| INSERT statement

| LEAVE statement

| LOOP statement

| OPEN statement

| PRINT statement

| RELEASE SAVEPOINT statement

| RETURN statement

| ROLLBACK statement

| SAVEPOINT statement

| SELECT statement

| SET statement

| SIGNAL statement

| START TRANSACTION statement

| UPDATE statement

| WHILE statement

Predicate

A predicate is defined as:

expression compare-operator expression

| expression [ NOT ] BETWEEN expression AND expression

| expression [ NOT ] LIKE string-literal

| expression IS [ NOT ] NULL

| NOT predicate

| predicate AND predicate

| predicate OR predicate

| '(' predicate ')'compare-operator ::= '=' | '>=' | '>' | '<=' | '<' | '<>' | '!='

| [ NOT ] IN value-list

Expression

An expression is defined as:

number

| string-literal

| column-name

| variable-name

| NULL

| CONVERT '(' expression ',' data-type ')'

| '-' expression

| expression '+' expression

| expression '-' expression

| expression '*' expression

| expression '/' expression

| expression '&' expression

| '~' expression

| expression '|' expression

| expression '^' expression

| function-name '(' [ expression-list ] ')'

| '(' expression')'

| '{' D string-literal '}'

| '{' T string-literal '}'

| '{' TS string-literal '}'

| @:IDENTITY

| @:ROWCOUNT

| @@BIGIDENTITY

| @@IDENTITY

| @@ROWCOUNT

An expression list is defined as:

expression-list ::= expression [ , expression ... ]

Global Variables

PSQL supports the following global variables:

• @@IDENTITY and @@BIGIDENTITY

• @@ROWCOUNT

• @@SESSIONID

• @@SPID

Global variables are prefaced with two at signs, @@. All global variables are variables per connection. Each database connection has its own @@IDENTITY, @@BIGIDENTITY, @@ROWCOUNT, and @@SPID values.

@@IDENTITY and @@BIGIDENTITY

Either of these variables returns its most recently inserted column value. The value is a signed integer value. The initial value is NULL.

The variable can refer to only a single column. If the target table includes more than one IDENTITY column, the value of this variable refers to the IDENTITY column serving as the table primary key. If no such column exists, then the value of this variable refers to the first IDENTITY column in the table.

If the most recent insert was to a table without an IDENTITY column, then the value of @@IDENTITY is set to NULL.

For BIGIDENTITY values, use @@BIGIDENTITY. For SMALLIDENTITY values, use @@IDENTITY.

Examples

SELECT @@IDENTITY

Returns NULL if no records have been inserted in the current connection, otherwise returns the IDENTITY column value of the most recently inserted row.

SELECT * FROM t1 WHERE @@IDENTITY = 12

Returns the most recently inserted row if it has an IDENTITY column value of 12. Otherwise, returns no rows.

INSERT INTO t1(c2) VALUES (@@IDENTITY)

Inserts the IDENTITY value of the last row inserted into column C2 of the new row.

UPDATE t1 SET t1.c1 = (SELECT @@IDENTITY) WHERE t1.c1 = @@IDENTITY + 10

Updates column C1 with the IDENTITY value of the last row inserted, if the value of C1 is 10 greater than the IDENTITY column value of the last row inserted.

UPDATE t1 SET t1.c1 = (SELECT NULL FROM t2 WHERE t2.c1 = @@IDENTITY)

Updates column C1 with the value NULL if the value of C1 equals the IDENTITY column value of the last row inserted.

The example below creates a stored procedure and calls it. The procedure sets variable V1 equal to the sum of the input value and the IDENTITY column value of the last row updated. The procedure then deletes rows from the table anywhere column C1 equals V1. The procedure then prints a message stating how many rows were deleted.

CREATE PROCEDURE TEST (IN :P1 INTEGER);

BEGIN

DECLARE :V1 INTERGER;

SET :V1 = :P1 + @@IDENTITY;

DELETE FROM t1 WHERE t1.c1 = :V1;

IF (@@ROWCOUNT = 0) THEN

PRINT 'No row deleted';

ELSE

PRINT CONVERT(@@ROWCOUNT, SQL_CHAR) + ' rows deleted';

END IF;

END;

CALL TEST (@@IDENTITY)

@@ROWCOUNT

This variable returns the number of rows that were affected by the most recent operation in the current connection. The value is an unsigned integer. The initial value is zero.

The @@ROWCOUNT variable is valid only when used after an INSERT, UPDATE, or DELETE statement.

Examples

SELECT @@ROWCOUNT

Returns zero if no records were affected by the previous operation in the current connection, otherwise returns the number of rows affected by the previous operation.

CREATE TABLE t1 (c1 INTEGER, c2 INTEGER)

INSERT INTO t1 (c1, c2) VALUES (100,200)

INSERT INTO t1(c1, c2) VALUES (300, @@ROWCOUNT)

SELECT @@ROWCOUNT

Results:

1 (in @@ROWCOUNT variable)

In line four, the @@ROWCOUNT variable has the value of 1 because the previous INSERT operation affected one row.

Also see the example for @@IDENTITY.

@@SESSIONID

This variable returns an eight-byte integer value for the PSQL connection. The integer is a combination of a time value and an incremental counter. This variable can be used to identify uniquely each PSQL connection.

@@SESSIONID requires a connection to the database engine to return a value. If the connection to the database engine is lost, the variable cannot return an identifier.

Example

SELECT @@SESSIONID

The example returns an integer identifier such as 26552653137523.

@@SPID

This variable (server process identifier) returns the identifier integer value of the system thread for the PSQL connection.

If the connection to the database engine is lost, SPID cannot return an identifier. Instead, the statement returns an error with a SqlState of 08S01.

Example

SELECT @@SPID

The example returns an integer identifier such as 402.

Other Characteristics

This topic describes other characteristics of the SQL grammar. It is divided into the following sections:

• Temporary Files

• Working with NULL Values

• Working with Binary Data

• Creating Indexes

• Comma as Decimal Separator

Temporary Files

When PSQL must generate a temporary table in order to process a given query, it creates the file in a location determined by the following rules.

First, if you have manually added the string key value PervasiveEngineOptions\TempFileDirectory to the Windows registry, PSQL uses the path set for TempFileDirectory. (The registry location differs depending on the bit architecture of the product. For PSQL 32-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. For PSQL 64-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI.)

If you have not defined the registry entry, PSQL uses the following sequence on Window platforms:

•The path specified by the TMP environment variable.

•The path specified by the TEMP environment variable.

•The path specified by the USERPROFILE environment variable.

•The Windows directory.

For example, if the TMP environment variable is not defined, PSQL uses the path specified in the TEMP environment variable, and so forth.

On Linux and macOS distributions, PSQL uses the current directory for the server process. No attempt is made to use TMP.

PSQL deletes all temporary files needed to process a query when it has completed processing the query. If the query is a SELECT statement, then the temporary files exist as long as the result set is active, that is, until the result set is freed by the calling application.

When are Temporary Files Created?

PSQL uses three types of temporary files: in-memory, on-disk, and Btrieve (MicroKernel Engine).

In-memory Temporary File

In-memory temporary files are used for the following circumstances:

•forward-only cursor

•number of bytes in the temporary file is less than 250,000

•SELECT statements with ORDER BY, GROUP BY, or DISTINCT that do not use an index, that have no BLOB or CLOB in the ORDER BY, GROUP BY, or DISTINCT, and that have no BLOB or CLOB in selection list with UNION.

On-disk Temporary File

On-disk temporary files are used for the following circumstances:

•forward-only cursor

•number of bytes in the temporary file is greater than 250,000

Btrieve Temporary File

Btrieve temporary files are used for the following circumstances:

•Forward-only cursor with BLOB or CLOB in ORDER BY, GROUP BY, or DISTINCT or with BLOB or CLOB in selection list with UNION

•Dynamic or static cursor with UNION queries or SELECT statements with ORDER BY, GROUP BY, or DISTINCT that do not use an index.

PSQL does not create a Btrieve temporary file for each base table in a static cursor SELECT query. Instead, each base table is opened by using the MicroKernel to reserve pages in the file as a static representation of the file. Any change made through a static cursor cannot be seen by that cursor.

Working with NULL Values

PSQL interprets a NULL as an unknown value. Thus, if you try to compare two NULL values, they will compare as not equal.

An expression that evaluates to WHERE NULL=NULL returns FALSE.

Working with Binary Data

Consider the following scenario: you insert the literal value '1' into a BINARY(4) column named c1, in table t1. Next, you enter the statement, SELECT * FROM t1 WHERE c1='1'.

The engine can retrieve data using the same binary format as was used to input the data. That is, the SELECT example above works properly and returns the value, 0x01000000, even though there is no literal match.

Note The engine always adds a zero (‘0’) to the front of odd-digit binary values that are inserted. For example, if you insert the value '010', then the value '0x00100000' is stored in the data file.

Currently, PSQL does not support suffix 0x to denote binary constants. Binary constants are a string of hexadecimal numbers enclosed by single quotation marks.

This behavior is the same as SQL Server.

Creating Indexes

The maximum column size for indexable VARCHAR columns is 254 bytes if the column does not allow Null values and 253 bytes if the column is nullable.

The maximum column size for CHAR columns is 255 bytes if the column does not allow Null values and 254 bytes if the column is nullable.

The maximum column size for indexable NVARCHAR columns is NVARCHAR(126). This limit applies to both nullable and not-null columns. The NVARCHAR size is specified in UCS-2 character units.

The maximum column size for NCHAR columns is NCHAR(127). This limit applies to both nullable and not-null columns. The NCHAR size is specified in UCS-2 character units.

The maximum Btrieve key size is 255. When a column is nullable and indexed a segmented key is created with 1 byte for the null indicator and a maximum 254 bytes from the column indexed. VARCHAR columns differ from CHAR columns in that either length byte (Btrieve lstring) or a zero terminating byte (Btrieve zstring) are reserved, increasing the effective storage by 1 byte. NVARCHAR (Btrieve wzstring) columns differ from NCHAR columns in that a zero terminating character is reserved, increasing the effective storage by 2 bytes.

Comma as Decimal Separator

Many locales use a comma to separate whole numbers from fractional numbers within a floating point numeric field. For example, these locales would use 1,5 instead of 1.5 to represent the number one-and-one-half.

PSQL supports both the period and the comma as decimal separators. PSQL accepts input values using the period or the comma, based on the regional settings for the operating system. By default, the database engine displays values using the period.

Note When the decimal separator is not a period, numbers appearing in SQL statements must be enclosed in quotes.

For output and display only, the session-level command SET DECIMALSEPARATORCOMMA can be used to specify output (for example, SELECT results) that uses the comma as the decimal separator. This command has no effect on data entry or storage.

Client/Server Considerations

Support for the comma as decimal separator is based on the locale setting in the operating system. Both the client operating system and the server operating system have a locale setting. The expected behavior varies according to both settings.

•If either the server or client locale setting uses the comma as decimal separator, then PSQL accepts both period-separated values and quoted comma-separated values.

•If neither the server nor the client locale setting uses the comma decimal separator, then PSQL does not accept comma-separated values.

Changing the Locale Setting

Decimal separator information can be retrieved or changed only for a machine running a Windows operating system. The decimal setting for Linux and macOS cannot be configured, and it is set to a period. If you have a Linux and macOS server engine and you want to use the comma as decimal separator, you must ensure that all your client computers are set to a locale that uses the decimal separator.

To change the regional settings on a Windows operating system, access the settings from the Control Panel. Stop and restart PSQL services after your change to enable the database engine to use the setting.

Examples

Example A - Server locale uses the comma for decimal separator

Client locale uses comma as decimal separator:

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1.232')

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 FROM c1 = '10,123'

The above two SELECT statements, if executed from the client, return:

10.123, 1.232

SET DECIMALSEPARATORCOMMA=ON

SELECT * FROM t1 FROM c1 = '10,123'

The above SELECT statement, if executed from the client after setting the decimal separator, returns:

10,123, 1,232

Client locale uses period as decimal separator, and these statements are issued from a new connection (meaning default behavior for SET DECIMALSEPARATORCOMMA):

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1.232')

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 WHERE c1 = '10,123'

The above two SELECT statements, if executed from the client, return:

10.123, 1.232

Example B - Server locale uses the period for decimal separator

Client locale uses comma as decimal separator:

Same as client using comma in Example A.

Client locale uses period as decimal separator:

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1,232') -- error in assignment

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 WHERE c1 = '10,123' -- error in assignment

The first select statement above, if executed from the client, returns:

10.123, 1.232

SET DECIMALSEPARATORCOMMA=ON

SELECT * FROM t1 FROM c1 = 10.123

The above SELECT statement, if executed after setting the decimal separator for display, returns:

10,123, 1,232