Product Documentation

FairCom RTG V3 Update Guide

Previous Topic

Next Topic

Parameter Marks Now Available in Scalar Functions and CASE Statements

FairCom DB now supports use of parameters in case expressions and in calls to scalar functions. Parameters are supported in most instances, with the exception of a few cases where it is not possible to determine the type of a parameter. Exception cases where parameters are not supported:

  • TO_CHAR, first argument
  • NULLIF, first argument
  • COALESCE
  • DATALENGTH

Example

ISQL> create table test2 (name char(10), item_count integer, invoice_date DATE);

ISQL> insert into test2 values ('hammer', 50, now() );

1 record inserted.

ISQL> commit;


ISQL> select * from test2 where ADD_MONTHS(invoice_date, ?) > SYSDATE;

Enter value for:

Parameter: 1

Type: INTEGER

Maximum size: 10

Is null (Y/N): n

Value: 6

NAME ITEM_COUNT INVOICE_DATE

---- ---------- ------------

hammer 50 10/05/2020

1 record selected

Previous Topic

Next Topic

Assign Values to Auto-Increment Fields in INSERT

SQL - auto_increment fields enhanced

IDENTITY column support is now "smart" and automatically inserts values when explicitly specified and auto-generates values when they are not (the default existing behavior).

The syntax/definition of auto_increment fields in this release is identical to the current IDENTITY syntax except INSERT statements are smarter. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically.

Prior to this modification, it was necessary to explicitly disable IDENTITY insertion with
SET identity_insert <table> on | off.

Only one table at a time can be set to identity_insert on.

No table definition changes are required to enable this new support. It enables compatibility with many external SQL frameworks such as SQLAlchemy.

TOCIndex