CODEWITHSUNDEEP
sqlstored-proceduresfirebird
kokokok
kokokok

Reputation: 1102

How join a table and selectable stored procedure filtering some rows?

In Firebird 2.5.9, I am trying to join a stored procedure in a query:

SELECT *
FROM T1
LEFT JOIN STORED_PROC(T1.STARTDATE) ON 1=1
WHERE T1.STARTDATE IS NOT NULL

This stored procedure has a parameter, a data, that it is declared as not null.

When I execute it, I get the following error:

insert failed because a column definition includes validation constraints.Validation error for variable MINDATE, value "*** null ***".

I seems that it is receiving a null value, even the WHERE should excloude the null values. I thought that LEFT JOIN forces to evaluate first the table. Does it mean that the LEFT JOIN includes all records, do the join, and finally filter using the WHERE?

If I do:

SELECT *
FROM T1
LEFT JOIN STORED_PROC('2021-1-1') ON 1=1
WHERE T1.STARTDATE IS NOT NULL

it does not return error, but obviously it is not correct. It is just to confirm that the error is in the passed date.

Finally, if I do:

SELECT * FROM 
(SELECT * FROM T1 WHERE T1.STARTDATE IS NOT NULL) AS A1
LEFT JOIN STORED_PROC(A1.STARTDATE) ON 1=1

then it works fine, but I would like to know because the first version does not work.

Upvotes: 0

Views: 206

Answers (2)

Sergio Hernandez Cerezo
Sergio Hernandez Cerezo

Reputation: 11

Another solution is to replace null dates with current_date using COALESCE(x, value_if_x_is_null) to avoid errors:

SELECT *
FROM T1
LEFT JOIN STORED_PROC(COALESCE(T1.STARTDATE, current_date)) ON 1=1
WHERE T1.STARTDATE IS NOT NULL

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you can just filter the values first and then can join it -

SELECT *
  FROM (SELECT *
          FROM T1
         WHERE T1.STARTDATE IS NOT NULL) TEMP
  LEFT JOIN STORED_PROC(TEMP.STARTDATE) ON 1=1

Upvotes: 1

Related Questions