Hi!
I have a table with a tent_recpdat field (date of receipt of the goods of type date (dd-mm-yyyy)), I need to make a select that brings me the results of receipts that are more than 50 days old, I tried the sql command below, but no results.
select tent_fornece_1, tent_nomefor_1, tent_nronota_1, datedif(day, tent_recpdat_1, getdate()) as dif_dias from notent where dif_dias > 10
select tent_fornece_1, tent_nomefor_1, tent_nronota_1, datediff(day, tent_recpdat_1, getdate()) as dif_dias from notent
where tent_recpdat_1 < DATEADD(day, -50, cast(GETDATE() as DATE))
Ah, it's Oracle. SQLTeam.com is a Microsoft SQL Server focused site.
The Oracle equivalent should be:
select tent_fornece_1, tent_nomefor_1, tent_nronota_1, datediff(day, tent_recpdat_1, getdate()) as dif_dias from notent
where tent_recpdat_1 < CURRENT_DATE - INTERVAL '50' DAY
I have not tested that however.
SELECT tent_fornece_1, tent_nomefor_1, tent_nronota_1, (SYSDATE - tent_recpdat_1) as dif_dias
FROM notent
WHERE (SYSDATE - tent_recpdat_1) > 50;
Ah, be careful now... That WHERE clause will prevent an Index Seek, even in Oracle, because it has to scan the table for all values to do the calculation before it can do the comparison. That's know as a "non-SARGable" predicate.
Ha yes, this query is indeed as you point out 'suboptimal' ;-). I haven't tested it, but should work in my opinion.
So why not make the simple change so that it's like the query that @robert_volk posted? There can be a huge difference between "suboptimal but should work" and "will work as fast as possible". 
As fast as possible is always better than suboptimal!
But for my understanding (I'm not an expert and apologize if I'm wrong)... is my query not faster because the calculation is performed in the query itself, rather than being performed by the database (knowing Oracle)?
Look at the WHERE clause... you're doing a calculation on a column rather than just comparing a column to a calculation.
I see, you are right, my query is more costly!