antoedincharley wrote:
I am looking for a solution to get values in query and those not available in table data.
Example:
CREATE TABLE TEMPTABLE(ID SMALLINT);
INSERT INTO TEMPTABLE(ID) VALUES(1);
SELECT * FROM TEMPTABLE WHERE ID IN (1,2)
in the Above case I want to get the value 2 as output of a query.
Svein Erling Tysvær answers:
This is not possible in a direct query (well, unless you make a table that contains the numbers that you want to check), but with EXECUTE BLOCK you can do something similar:
execute block (FromValue integer = :MyFromValue, ToValue integer = :MyToValue)
returns (NotInTable integer)
as
begin
NotInTable = FromValue;
while (NotInTable <= ToValue) do
begin
if (not exists (select * from temptable
where id = :NotInTable )) then
suspend;
NotInTable = NotInTable + 1;
end
end
András Omacht answers:
with nums as (
select 1 num from rdb$database
union all
select 2 num from rdb$database)
select n.num
from nums n
where not exists (select 1 from temptable t
where t.id = n.num)