Home » SQL & PL/SQL » SQL & PL/SQL » NVL2 problem when using forms (Forms [32 Bit] Version 10.1.2.0.2 (Production))
NVL2 problem when using forms [message #576754] Thu, 07 February 2013 14:13 Go to next message
aaichah
Messages: 23
Registered: July 2011
Location: Ottawa
Junior Member
Hi I am trying to run a statement in Oracle, it runs fine in Oracle reports Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
and in Toad 10.6.1.3, but when I run it in forms, I get an error, nvl2 must be declared

select nvl2(translate('A', 'A1234567890.00','A'), 'F', 'T')
INTO recs_ct
from dual;
this will return F in Toad or reports builder
this will help me to determine if a string is a number

But nvl2 is not recognized in Oracle forms, I need to include this statement in a parameter form that runs the report and I need
to determine the number of records returned before I run the report. Here is the forms builder information:

Forms [32 Bit] Version 10.1.2.0.2 (Production)
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Oracle Toolkit Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.4.2 (Production)
Oracle Procedure Builder V10.1.2.0.2 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 10.1.2.0.2 - Production
Oracle Virtual Graphics System Version 10.1.2.0.2 (Production)
Oracle Tools GUI Utilities Version 10.1.2.0.2 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.4.0 Production

Thank you
Re: NVL2 problem when using forms [message #576755 is a reply to message #576754] Thu, 07 February 2013 14:17 Go to previous messageGo to next message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a (stored) function which will accept parameters you normally use in NVL2 function. Your (stored) function will then utilize NVL2 and return result to the form. Obviously, you'd call your (stored) function from a form.

It *should* work because (stored) function will run in the database, and - as you noticed - NVL2 works there just fine.
Re: NVL2 problem when using forms [message #576761 is a reply to message #576755] Thu, 07 February 2013 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or use DECODE instead of NVL2:
NVL2(A,B,C)=DECODE(A,NULL,C,B)

Regards
Michel
Re: NVL2 problem when using forms [message #576848 is a reply to message #576761] Fri, 08 February 2013 09:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1965
Registered: January 2010
Senior Member
Well, 99.9% equivalent:

SQL> create or replace
  2    function f1
  3      return number
  4      is
  5      begin
  6          dbms_output.put_line('Call to F1');
  7          return 1;
  8  end;
  9  /

Function created.

SQL> set serveroutput on
SQL> select  nvl2(1,2,f1)
  2    from  dual
  3  /

NVL2(1,2,F1)
------------
           2

Call to F1
SQL> set serveroutput on
SQL> select  decode(1,null,f1,2)
  2    from  dual
  3  /

DECODE(1,NULL,F1,2)
-------------------
                  2

SQL> 


SY.
Re: NVL2 problem when using forms [message #576849 is a reply to message #576848] Fri, 08 February 2013 09:38 Go to previous messageGo to next message
aaichah
Messages: 23
Registered: July 2011
Location: Ottawa
Junior Member
Thank you all for your quick reply
I am home today because of the snow storm
I will try your solutions on Monday
Have a wonderful weekend
Re: NVL2 problem when using forms [message #577112 is a reply to message #576849] Tue, 12 February 2013 08:17 Go to previous messageGo to next message
aaichah
Messages: 23
Registered: July 2011
Location: Ottawa
Junior Member
Thank you very much. I used the decode to get the same result
you can close this thread now
Re: NVL2 problem when using forms [message #577113 is a reply to message #577112] Tue, 12 February 2013 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As long as you do not call custom function in the DECODE expressions there will be no difference.

(There is nothing to close a thread, it is open waiting for someone to put something new...)

Regards
Michel
Re: NVL2 problem when using forms [message #577148 is a reply to message #577113] Tue, 12 February 2013 15:23 Go to previous message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot

it is open waiting for someone to put something new...


Right; I know what is going to happen next - someone (I might name a few) will jump in, asking why that "someone new" resurrected an old thread, that there are newer discussions waiting for "someone" to participate ./fa/1597/0/ and even ./fa/448/0/ this thread. Then I'll see that it is locked and most probably un./fa/448/0/ it.
./fa/9322/0/
Previous Topic: Partition Creation based on other table values
Next Topic: Single-row subquery Issue
Goto Forum:
  


Current Time: Wed Jul 30 08:27:31 CDT 2014

Total time taken to generate the page: 0.09068 seconds