[firebird-support] Select with a timestamp + 24 hours

['Paul Coshott' p...@coshott.com [firebird-support]]

Hi All,

 

I have a table with a timestamp field. I need to perform a select to return
records that are more than 24 hours past the timestamp value.

 

So something like:

 

Select from STAFF where PASSWORD_TIME + 24 hours < Now

 

Can anyone tell me how to do this?

 

Thanks,

Paul

[firebird-support] Help with error message (Firebird 3)

['Paul Coshott' p...@coshott.com [firebird-support]]

Hi All,

 

I am getting an error during an sql insert, done with Firedac within a
delphi vcl app.

 

The error is:

 

[FireDAC][Phys][FB]violation of PRIMARY or UNIQUE KEY constraint "INTEG_50"
on table "ROSTER"

Problematic key value is ("ROSTERID" = 19)

 

The field ROSTERID is the primary key field.

 

There is no record with the key value of 19 (the highest is 11) and there
are only 4 records in the table.

The sql insert is:

 

INSERT INTO ROSTER

(ROSTERID, COMPANYID, DESCRIPTION, START_DATE, END_DATE, STATUS,
CREATOR, SEND_PUB_NOTIFICATION)

VALUES (

19,

1,

'Roster Starting 19-02-2019 to 25-02-2019',

'2020-01-26',

'2020-02-01',

'D',

1,

False)

 

Can anyone tell me what the error means and what could be the cause.

 

Thanks,

Paul

[firebird-support] Re: Using params to choose the order by fields?

[p...@coshott.com [firebird-support]]

Hi Norbert,
 

 Thanks for the answer. Just one little thing in case anyone else needs to do 
this. The ; after the "END" in the first case statement should not be there.
 

 But otherwise works perfect.
 

 Cheers,
 Paul

[firebird-support] Using params to choose the order by fields?

[p...@coshott.com [firebird-support]]

Hi All,

I want to give my users the choice of ordering their member list by

FIRST_NAME, SURNAME
or
SURNAME, FIRST_NAME

I managed to get it working using a case statement, but only with one field in 
Order By clause. If I put in the second field, it gives me an invalid token 
error on the comma.

 How can I get this to work? Thanks for any help.
 

 Or is there a better way to do this?


Cheers,
Paul

SELECT
  MEMBERS.FIRST_NAME,
  MEMBERS.SURNAME,
  MEMBERS.MEMBERID
FROM
  MEMBERS
WHERE
  MEMBERS.CLUBID = :CID
ORDER BY
  CASE :OBC
WHEN 1 THEN MEMBERS.SURNAME
WHEN 2 THEN MEMBERS.FIRST_NAME
  END;
 

 The above works, but if I put in 2 fields as below, I get an error. 


ORDER BY
  CASE :OBC
WHEN 1 THEN MEMBERS.SURNAME, MEMBERS.FIRST_NAME
WHEN 2 THEN MEMBERS.FIRST_NAME, MEMBERS.SURNAME
  END;