SQL

Previous Next

A USoft application is primarily written in SQL. To develop a USoft application, all you need is a good command of the standard ANSI-92 SQL grammar and an understanding of how SQL is leveraged in USoft.

The topics in these help sections concentrate on syntax:

They tell you which SQL constructs are supported in which contexts. Unless otherwise stated, these constructs are handled by USoft itself and thus supported independently of the underlying RDBMS.

They tell you about USoft-specific extensions to the SQL language.

They do not cover the purpose or meaning of SQL constructs. If you are unfamiliar with SQL, take any standard tutorial. USoft offers special 1-day "SQL for USoft" courseware.

Click on a subject to continue:

SQL statements

SQL expressions

SQL conditions

SQL functions

 

or choose a subject or language element from this alphabetic list:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

 

!=

Mathematical operator

$$CURRENTDATE$$

Pseudo-column

$$CURRENTDATETIME$$

Pseudo-column

$$RDBMSOWNER$$

Pseudo-column

$$RDBMSUSER$$

Pseudo-column

$$TRANSACTIONDATETIME$$

Pseudo-column

$$USER$$

Pseudo-column

(+)

Operator in syntax for outer join

*=

Operator in syntax for outer join

.

Separator between component and method in a method invocation statement

||

Operator notation for CONCAT function

+  (1)

Operator notation for CONCAT function

+ (2)

Mathematical operator

-

Mathematical operator

/

Mathematical operator

*   (1)

Mathematical operator

*   (2)

Column name wildcard

<

Comparison operator

<=

Comparison operator

<>

Comparison operator

=*

Operator in syntax for outer join

>

Comparison operator

>=

Comparison operator

ABS

Scalar function

ACOS

Scalar function

ADD_DAYS

Scalar function

ADD_MONTHS

Scalar function

ADD_YEARS

Scalar function

Aliases, Column

 

Aliases, Table

 

AND

Logical operator

ASC

Keyword in an ORDER BY clause

ASCENDING

Keyword in an ORDER BY clause

ASCII

Scalar function

ASIN

Scalar function

ATAN

Scalar function

ATAN2

Scalar function

AVG

Group function

BETWEEN

SQL operator

CASE

Scalar function

CAST

Scalar function

CEIL

Scalar function

CEILING

Scalar function

CHAR

Scalar function

CHAR_TO_DATE

Scalar function

CHARINDEX

Scalar function

CHARINDEX

Scalar function

CHARTOROWID

Scalar function

CHR

Scalar function

COALESCE

Scalar function

COL_LENGTH

Scalar function

COL_NAME

Scalar function

Column aliases

 

Column expressions

 

COMMIT

Transaction control statement

Comparison operators

 

Component

Identifier for object called in a method invocation statement

CONCAT

Scalar function

Condition

Contents of a WHERE or HAVING clause

Conditions

 

CONNECT BY

Clause in a SELECT statement

CONVERT

Scalar function

Correlated subquery

Subquery containing a reference to a table instance in the embedding statement

COS

Scalar function

COSH

Scalar function

COT

Scalar function

COUNT

Group function

CROSS JOIN

Keyword in join construct

CURDATE

Scalar function

CURRENT_DATE

Scalar function

CURRENT_TIMESTAMP

Scalar function

CURTIME

Scalar function

DATABASE

Scalar function

DATALENGTH

Scalar function

DATE_TO_CHAR

Scalar function

DATEADD

Scalar function

DATEDIFF

Scalar function

DATEDIFF_IN_DAYS

Scalar function

DATENAME

Scalar function

DATEPART

Scalar function

DAYNAME

Scalar function

DAYOFMONTH

Scalar function

DAYOFWEEK

Scalar function

DAYOFYEAR

Scalar function

DB_ID

Scalar function

DB_NAME

Scalar function

DECODE

Scalar function

DEGREES

Scalar function

DELETE

Keyword in data manipulation statement

DELETE FROM

Keyword in data manipulation statement

DESC

Keyword in ORDER BY clause

DESCENDING

Keyword in ORDER BY clause

DIFFERENCE

Scalar function

DISTINCT

Group operator in SELECT lists

DUMP

Scalar function

ELAPSEDQUERYTRANSACTIONTIME

Scalar function

ELAPSEDTRANSACTIONTIME

Scalar function

EXISTS

SQL operator

EXP

Scalar function

EXTEND

Escape syntax for calling RDBMS functions

FLOOR

Scalar function

FROM (1)

Keyword in a DELETE statement

FROM (2)

Clause in a SELECT statement

FROM (3)

Clause in a data manipulation statement

Function calls

 

GETDATE

Scalar function

GREATEST

Scalar function

GROUP BY

Clause in a SELECT statement

Group-by list

Contents of a GROUP BY clause

Group functions

 

HAVING

Keyword in GROUP BY clause

HEXTORAW

Scalar function

HEXTOROWID

Scalar function

Host variables

 

HOST_ID

Scalar function

HOST_NAME

Scalar function

HOUR

Scalar function

IFNULL

Scalar function

IN

SQL operator

Independent subquery

Subquery not containing a reference to a table instance in the embedding statement

INDEX_COL

Scalar function

INITCAP

Scalar function

Inline functions

 

Inner join

Principle for combining data from multiple table instances

INNER JOIN

Keyword in join construct

INSERT

Keyword in data manipulation statement

INSERT

Scalar function

INSTR

Scalar function

INSTRB

Scalar function

INTERSECT

Set operator

INVOKE

Keyword in method invocation statement

IS NOT NULL

SQL operator

IS NULL

SQL operator

ISINTEGER

Scalar function

ISNULL

Scalar function

ISNUMERIC

Scalar function

Join

SELECT statement retrieving data from multiple table instances

JOIN

Keyword in join construct

Join condition

Condition in join construct

Joined subquery

Subquery containing a reference to a table instance in the embedding statement

LAST_DAY

Scalar function

LCASE

Scalar function

LEAST

Scalar function

LEFT

Scalar function

LEFT JOIN

Operator in syntax for outer join

LEFT OUTER JOIN

Operator in syntax for outer join

LEN

Scalar function

LENGTH

Scalar function

LENGTHB

Scalar function

LIKE

SQL operator

LITERAL

Keyword in rdbms.EXTEND function call syntax

Literals

 

LN

Scalar function

LOCATE

Scalar function

LOG

Scalar function

LOG10

Scalar function

Logical operators

 

LOWER

Scalar function

LPAD

Scalar function

LTRIM

Scalar function

Mathematical operators

 

MAX

Group function

Method

Identifier for method called in a method invocation statement

MIN

Group function

MINUS

Set operator

MINUTE

Scalar function

MOD

Scalar function

MONTH

Scalar function

MONTHNAME

Scalar function

MONTHS_BETWEEN

Scalar function

NEW_TIME

Scalar function

NEXT_DAY

Scalar function

NLS_INITCAP

Scalar function

NLS_LOWER

Scalar function

NLS_UPPER

Scalar function

NLSSORT

Scalar function

NOT

Logical operator

NOT EXISTS

SQL operator

NOT IN

SQL operator

NOT LIKE

SQL operator

NOW

Scalar function

NULL

Literal

NULLVAL_CHAR

Scalar function

NULLVAL_INT

Scalar function

NUMBERTOCHAR

Scalar function

NVL

Scalar function

OBJECT_ID

Scalar function

OBJECT_NAME

Scalar function

OLD

Scalar function

ON

Condition in join construct

OR

Logical operator

ORDER BY

Clause in a SELECT statement

Outer join

Principle for combining data from multiple table instances

PATINDEX

Scalar function

PI

Scalar function

POWER

Scalar function

Pseudo-columns

 

QUARTER

Scalar function

RADIANS

Scalar function

RAND

Scalar function

RAWTOHEX

Scalar function

rdbms.EXTEND

Escape syntax for calling RDBMS functions

RDBMSTYPE

Scalar function

RECORDS

Scalar function

REGEXP_INSTR

Scalar function

REGEXP_REPLACE

Scalar function

REGEXP_SUBSTR

Scalar function

RELATE

Keyword in inner join construct

REPEAT

Scalar function

REPLACE

Scalar function

REPLICATE

Scalar function

REVERSE

Scalar function

RIGHT

Scalar function

RIGHT JOIN

Operator in syntax for outer join

RIGHT OUTER JOIN

Operator in syntax for outer join

ROLE

Scalar function

ROLLBACK

Transaction control statement

ROUND

Scalar function

ROWIDTOCHAR

Scalar function

ROWNUM

Pseudo-column

RPAD

Scalar function

RTRIM

Scalar function

Scalar functions

 

SECOND

Scalar function

SELECT

Keyword in query statements

Select list

Contents of a SELECT clause

SESSION_ID

Scalar function

SET

Clause in a UPDATE statement

SIGN

Scalar function

SIN

Scalar function

SINH

Scalar function

SOUNDEX

Scalar function

SPACE

Scalar function

SQRT

Scalar function

START WITH

Clause in a SELECT statement

STDDEV

Group function

STR

Scalar function

STUFF

Scalar function

Subquery

Variable element in a WHERE or HAVING condition

SUBSTR

Scalar function

SUBSTRB

Scalar function

SUBSTRING

Scalar function

SUM

Group function

SUSER_ID

Scalar function

SUSER_NAME

Scalar function

SYSDATE

Pseudo-column

Table aliases

 

TAN

Scalar function

TANH

Scalar function

TEXTPTR

Scalar function

TEXTVALID

Scalar function

TIMESTAMP_TO_DATE

Scalar function

TIMESTAMPADD

Scalar function

TIMESTAMPDIFF

Scalar function

TO_CHAR

Scalar function

TO_CHARDATE

Scalar function

TO_CLOB

Scalar function

TO_DATE

Scalar function

TO_INTEGER

Scalar function

TO_NCLOB

Scalar function

TO_NUMBER

Scalar function

TO_NVARCHARMAX

Scalar function

TOP

Keyword in a SELECT statement

TRANSACTION_DATE

Scalar function

TRANSLATE

Scalar function

TRUNC

Scalar function

TRUNCATE

Scalar function

UCASE

Scalar function

UID

Scalar function

UNION

Set operator

UNION ALL

Set operator

UPDATE

Keyword in data manipulation statement

UPPER

Scalar function

USER

Pseudo-column

USER_ID

Scalar function

USER_NAME

Scalar function

USERENV

Scalar function

USERGROUP

Scalar function

VALUES

Clause in a INSERT statement

VARIANCE

Group function

VSIZE

Scalar function

WEEK

Scalar function

WHERE

Clause in a SELECT statement

WHERE

Clause in a data manipulation statement

YEAR

Scalar function