Reminder to self: sort out why a while ago I had trouble with a nested [WayBack] CASE construct causing parsing anomalies in 2.5.8.
–jeroen
Archive for the ‘Firebird’ Category
Firebird CASE construct
Posted by jpluimers on 2019/06/12
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Testing for NULL and equality in practice
Posted by jpluimers on 2019/05/30
Great article: Testing for NULL and equality in practice [WayBack]
Most important table piece I learned:
Testing (in)equality of A and B in different Firebird versions
Test type Firebird version <= 1.5.x >= 2.0 Equality Inequality
–jeroen
Share this:
Posted in Database Development, Development, Firebird, SQL | Leave a Comment »
UPDATE OR INSERT
Posted by jpluimers on 2019/04/25
I totally forgot that Firebird as of 2.5 supports [WayBack] UPDATE OR INSERT, so as a reminder to self:
UPDATE OR INSERT INTO
{target} [({column_list})]
VALUES ({value_list})
[MATCHING ({column_list})]
[RETURNING {values} [INTO {variables}]]
{column_list} ::= colname [, colname ...]
{value_list} ::= value [, value ...]
{ret_values} ::= ret_value [, ret_value ...]
{variables} ::= varname [, varname ...]
–jeroen
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
InterBase/Firebird divisions: use E0 syntax to force floating point calculations
Posted by jpluimers on 2019/04/23
I was a bit baffled at the below results because in other other databases.
You need to use this kind of notation ####.0E0 to force an integer to be evaluated as floating point, which is an extra .0 than explained by [WayBack] sql – Why does Firebird truncate decimal places when dividing? – Stack Overflow
I’m not the only person confused by this: [WayBack] [#CORE-2849] Simple mathematics gone wrong – Firebird RDBMS Issue Tracker.
I know that as of Firebird 2.1 you can use the [WayBack] dateadd() function for calculations on timestamps, date or time values (hence the confusing name dateadd), but you don’t always have the delta readily available in one unit (second, minutes, etc) and for a mixed Firebird/InterBase deployment having one kind of logic is often preferred.
select
cast('now' as timestamp) as NowTimeStamp
, (15.0*(1.0/24.0/60.0)) as A15MinutesInDays
, (15.0/24.0/60.0) as B15MinutesInDays
, (15.0E0/24/60) as C15MinutesInDays
, cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)) as AIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0))) as ADifferenceInDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24 as ADifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24*60 as ADifferenceMinutes
, cast('now' as timestamp) + (15.0/24.0/60.0) as BIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0)) as BDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24 as BDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24*60 as BDifferenceMinutes
, cast('now' as timestamp) + (15.0E0/24/60) as CIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60)) as CDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24 as CDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24*60 as CDifferenceMinutes
-- Firebird 2.1 and up: use dateadd , dateadd(minute, 15, cast('now' as timestamp)) as DIn15MinutesTimeStamp , cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp)) as DDifferenceInDays , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24 as DDifferenceHours , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24*60 as DDifferenceMinutes from rdb$database /* Transposed results are below. Excected values for: A15MINUTESINDAYS 0.0104166666666667 B15MINUTESINDAYS 0.0104166666666667 Transposed results: NOWTIMESTAMP 20-9-2017 20:31:40 A15MINUTESINDAYS 0 B15MINUTESINDAYS 0.01 C15MINUTESINDAYS 0.0104166666666667 AIN15MINUTESTIMESTAMP 20-9-2017 20:31:40 ADIFFERENCEINDAYS 0 ADIFFERENCEHOURS 0 ADIFFERENCEMINUTES 0 BIN15MINUTESTIMESTAMP 20-9-2017 20:46:04 BDIFFERENCEDAYS -0.01 BDIFFERENCEHOURS -0.24 BDIFFERENCEMINUTES -14.4 CIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 CDIFFERENCEDAYS -0.010416667 CDIFFERENCEHOURS -0.250000008 CDIFFERENCEMINUTES -15.00000048 DIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 DDIFFERENCEDAYS -0.010416667 DDIFFERENCEHOURS -0.250000008 DDIFFERENCEMINUTES -15.00000048 */
–jeroen
Share this:
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Generating a million sequential numbers on the fly in a SQL Server query
Posted by jpluimers on 2019/01/16
A while back I wrote on Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements.
SQL Server has different features and performance characteristics so here are some links on doing similar things in SQL Server:
- [WayBack] sql server – SQL, Auxiliary table of numbers – Stack Overflow
- [WayBack] sql server – What is the best way to create and populate a numbers table? – Stack Overflow
As always: if performance is important, measure before starting to optimise!
Via: [WayBack] sql – All hour of day – Stack Overflow
–jeroen
Share this:
Posted in Database Development, Development, Firebird, SQL, SQL Server | Leave a Comment »
