tag:blogger.com,1999:blog-18037024.post4435077908589253459..comments2024-02-16T14:33:11.277+01:00Comments on Annals of Oracle's Improbable Errors: ORA-06550 and PLS-00201:identifier 'NVL2' must be declaredByte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-18037024.post-47729493755122345822009-03-19T15:16:00.000+01:002009-03-19T15:16:00.000+01:00SnippetJoe,unless i am misinterpreting the COALESC...SnippetJoe,<BR/>unless i am misinterpreting the COALESCE documentation, COALESCE works like this:<BR/><BR/>COALESCE (expr1, expr2)<BR/><BR/>is equivalent to:<BR/><BR/>CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END<BR/><BR/><BR/>My NOTNVL is slightly different because it is equivalent to:<BR/><BR/>CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE null END<BR/><BR/>Right?<BR/><BR/>Bye,<BR/>FlavioByte64https://www.blogger.com/profile/15629209362377395020noreply@blogger.comtag:blogger.com,1999:blog-18037024.post-36337922241837717212009-03-19T14:46:00.000+01:002009-03-19T14:46:00.000+01:00Byte64, your NOTNVL function looks a lot like the ...Byte64, your NOTNVL function looks a lot like the built in COALESCE function. Give it a shot.SnippetyJoehttps://www.blogger.com/profile/14976294759055735163noreply@blogger.comtag:blogger.com,1999:blog-18037024.post-31416534249281852062007-04-19T18:22:00.000+02:002007-04-19T18:22:00.000+02:00Neil,funnily enough i would't suffer too much for ...Neil,<BR/>funnily enough i would't suffer too much for the absence of CASE, i mean, when i have complex conditions i think is much more readable the if-elsif-else-end structured clause of PL/SQL, but NVL2 is a handy function, so, may be this is no news for you but it could be for someone else and that's what this blog is all about.<BR/><BR/>Patrick,<BR/>in my wish list of built-ins i have a Byte64https://www.blogger.com/profile/15629209362377395020noreply@blogger.comtag:blogger.com,1999:blog-18037024.post-70421585175309560202007-04-19T17:32:00.000+02:002007-04-19T17:32:00.000+02:00I think I had the same issue with NVL2 a few weeks...I think I had the same issue with NVL2 a few weeks ago. I don't really see a reason why it's not available, but hey, I'm sure there is a good reason - or the developers where just lazy :-)<BR/><BR/>Neil, CASE is available in PL/SQL.<BR/><BR/>PatrickPatrick Wolfhttps://www.blogger.com/profile/16652802762749621200noreply@blogger.comtag:blogger.com,1999:blog-18037024.post-15293317395701573722007-04-19T17:23:00.000+02:002007-04-19T17:23:00.000+02:00This is nothing new. There has always been separa...This is nothing new. There has always been separation between Oracle's SQL and PL/SQL engine. Some functions only work in SQL. Unfortunately CASE is one of those functions as well.Anonymousnoreply@blogger.com