I'm trying to get the first free code between two values and I do not know what I'm doing wrong that always gives me 2 when there are numbers above 2 .
this is the code I'm using:
SELECT MIN('Ean')+1 as prox_ean_libre FROM 'CodigosEan' WHERE 'Ean' BETWEEN '843447200001' AND '843447299999' AND 'empresa' = '199'
In the database I have a record with the code 843447200003 but it still gives me 2 instead of 4 .
What am I doing wrong?
Your query does not include any kind of logic that ensures that MIN(Ean)+1 actually results in a free value.
One way to correct this is by adding a NOT EXISTS expression to filter results where Ean + 1 does not point to an existing record:
SELECT MIN(c.'Ean')+1 as prox_ean_libre
FROM 'CodigosEan' c
WHERE c.'Ean' BETWEEN 843447200001 AND 843447299999
AND c.'empresa' = '199'
and not exists (
select null
from 'CodigosEan' c2
where c2.'empresa' = c.'empresa'
and c2.'Ean' = c.'Ean' + 1
)
I would recommend you first of all, explicitly convert your EAN code to a numeric data type, since you are doing an implicit conversion anyway when using
MIN('Ean')+1
And this is surely causing you problems.
SELECT MIN(CAST('Ean' AS BIGINT))+1 as prox_ean_libre
FROM 'CodigosEan'
WHERE 'Ean' BETWEEN '843447200001' AND '843447299999'
AND 'empresa' = '199';