Variables in queries Firebird 3.0

Question

This query does not work

select a.id from users a where a.LOGIN = 'Test' into :useriden
WITH T AS (
SELECT
   ID,
   FIO,
   PWDHASH,
   ATTIME,
   ROW_NUMBER() OVER(ORDER BY ATTIME DESC) AS RN
FROM USERSPWDHASHHISTORY
WHERE USERID = :useriden)
SELECT
   ID, 
   FIO,
   PWDHASH,
   ATTIME
FROM T
WHERE RN > 3

How to properly initialize a variable in a database query? Not in a stored procedure or trigger.

Answer 1

What you want cannot be done in DSQL, and in PSQL this would at least be a syntax error, because you aren't terminating statements.

However, you don't need separate statements for this, you can do inline the query

WITH T AS (
    SELECT
       ID,
       FIO,
       PWDHASH,
       ATTIME,
       ROW_NUMBER() OVER(ORDER BY h.ATTIME DESC) AS RN
    FROM USERSPWDHASHHISTORY
    WHERE USERID = (select a.id from users a where a.LOGIN = 'Test'))
SELECT
   ID, 
   FIO,
   PWDHASH,
   ATTIME
FROM T
WHERE RN > 3

or with a join instead of a sub-query

WITH T AS (
    SELECT
       h.ID,
       h.FIO,
       h.PWDHASH,
       h.ATTIME,
       ROW_NUMBER() OVER(ORDER BY ATTIME DESC) AS RN
    FROM USERSPWDHASHHISTORY h
    inner join users u on h.userid = u.id
    WHERE u.LOGIN = 'Test')
SELECT
   ID, 
   FIO,
   PWDHASH,
   ATTIME
FROM T
WHERE RN > 3

Alternatively, you first select the id, then prepare the other query with a ? parameter placeholder for the id, prepare and execute it with the id value you selected previously.

For example, in Java you could do something like:

int id = -1;
try (var stmt = connection.createStatement();
     var rs = rs.executeQuery("select a.id from users a where a.LOGIN = 'Test'")) {
    if (rs.next()) {
        id = rs.getInt(1);
    }
}
try (var stmt = connection.prepareStatement("""
        WITH T AS (
        SELECT
           ID,
           FIO,
           PWDHASH,
           ATTIME,
           ROW_NUMBER() OVER(ORDER BY ATTIME DESC) AS RN
        FROM USERSPWDHASHHISTORY
        WHERE USERID = ?)
        SELECT
           ID, 
           FIO,
           PWDHASH,
           ATTIME
        FROM T
        WHERE RN > 3
        """)) {

    stmt.setInt(1, id);
    try (var rs = stmt.executeQuery()) {
        // process result set
    }
}

However, given this specific example can be done in one statement, such a solution is not preferred given the unnecessary overhead of executing two statements and associated network roundtrips.