Hi,

At October 5, 2005, 8:45 PM, Adam wrote:

>>
>> ## The SELECT statement
>> ### Ordering result sets:
>> Allows ORDER BY in contexts other than cursor definitions.
>> NULLs are considered lower than any non-NULL value.

> NULLS FIRST is the default behaviour, but see NULLS LAST if required.

>>
>> ### Limiting result sets:
>> Doesn't support ROW_NUMBER(), supports cursors in all contexts.
>> Alternative to ROW_NUMBER():
>>
>> SELECT FIRST x columns
>> FROM tablename
>> ORDER BY key ASC
>>

> I am not aware of "key".

I think here "key" means the primary key.

>> ### Top-n query:
>> Supports the slow standard SQL query variant only.
>>
>> ### Limit-with-offset
>> Doesn't support ROW_NUMBER(), provides alternative syntax:
>>
>> SELECT FIRST x SKIP y columns
>> FROM tablename
>> ORDER BY key ASC
>>

> Firebird 2 will introduce rows syntax (I think that is the keyword)

That's correct. And, you can use it in subqueries and derived tables
in FB 2.0.

>> ## The INSERT statement
>> ### Inserting several rows at a time: NO

> See INSERT INTO .... SELECT FROM ..... syntax.
> Also can be done with a stored procedure.

I think the meaning here might be the following example:

INSERT INTO ......
(Fields)
VALUES ((ROW1 VALUES)
,(ROW2 VALUES))

And, that is not currently supported, and it's not going to be in FB
2.0. Although, I'm not sure if it was added to the feature request
list, since it was already discussed.

>>
>> ## Data types
>> ### The BOOLEAN type
>> Doesn't support BOOLEAN type, possible alternative CHAR(1) with
>> constraints.

> CREATE DOMAIN BOOLCHAR AS CHAR(1)
> check( (value in ('T','F')) or value is null );

> You can then declare fields as a "BOOLCHAR".

But, a system defined BOOLEAN data type doesn't exist. I think it is
in the feature request list, but it's not part of FB 2.0. It might be
in FB 3.0 or 4.0.

>>
>> ### The CHAR type
>> Follows the standard.
>>
>> ### The TIMESTAMP type
>> Follows the standard, but doesn't implement WITH TIMEZONE.
>> Standard sanity checks pass.

Correct.

>>
>> ## SQL Functions
>> ### CHARACTER_LENGTH
>> Firebird is extremely poor on built-in SQL functions (this situation
>> is remedied in latest 2.0-alpha). CHARACTER_LENGTH() function
> doesn't
>> exist and there's no similar functionality.
>>

> This is a matter of opinion and I disagree. Distributed with Firebird
> 1.5 is a UDF called IB_UDF.DLL. There is a corresponding .sql script
> in the UDF folder.

> There are also third party, including many free dll/so libraries that
> can add pretty much everything below. Failing that, any language that
> allows you to compile a dll/so can be used to make up your own
> function.

Not the SQL standard function.

>> ### SUBSTRING
>> Only ordinary SUBSTRING variant exists.

In FB 2.0, it can now take arbitrary expressions in its parameters.

>>
>> ### REPLACE
>> No such functionality exists.
>>
>> ### TRIM
>> No such functionality exists.
> See comment above, you have LTRIM, RTRIM, LPAD, RPAD etc

Not the SQL standard TRIM.

>>
>> ### LOCALTIMESTAMP
>> No such functionality exists.
>>
>> ### Concatenation
>> Automatically casts the concatenated values into types compatible
>> with concatenation. If an operand is NULL then the result is NULL.

Which is according to the SQL standard.

> See also the Coalesce operator, that allows you to substitute a NULL
> for another value.

> Eg Coalesce(Amount, 0) returns 0 if Amount is null, Coalesce
> (MiddleName,'') returns an empty string if the middle name is null.

Yes, COALESCE is what needs to be used to replace a NULL value in a
value expression if you want the result to be anything other than NULL.

>>
>> ## Turning on query timing
>> No such functionality exists.

You can get this through some of the third-party administration
tools.





--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)