Good morning i have a simple question to do:
Updated
I have to do a select to get if there are records where length are different than a number. I know that my result could be some records or just no result because will be empty, this is my query:
select CASE WHEN id IS null THEN 'no existen' ELSE ID END AS existen
from dvd.cicle
WHERE LENGTH(ID) <> (SELECT MAX(LENGTH(ID)) FROM dvd.cicle )
but my result looks like this:
i need to get as result just the text “no existen” if the result is empty or the ID’s if the query give me something different as the max/length. Can somebody help me?
Regards
1
The question you asked and the query you have shown are different.
You write
have to do a select to get if there are rows where length are different than a number
but the query would check if there are genereally rows with different lengths of their id, not if there are rows that don’t have a specific length of the id.
So what do you really want?
If you generally want to know if rows with different lengths of the id exist, no matter what lengths they have, you can do following:
select
case when count(distinct length(id)) > 1
then 'exists' else 'not exists' end as existen
from cicle;
If you want to know if any id has a length different a specific number, you can do this (here, the number is 5):
select
case when count(case when length(id) <> 5 then 1 end) > 0
then 'exists' else 'not exists' end as existen
from cicle;
See this sample fiddle (Note: Yes, the fiddle doesn’t use Oracle as RDBMS, that’s because the fiddle site with Oracle DB I usually use doesn’t work currently, but above queries are standard SQL and will run in every RDBMS, except SQL Server, there you need len rather than length).
4