| NVL2 problem when using forms [message #576754] |
Thu, 07 February 2013 14:13  |
 |
aaichah
Messages: 24
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 #576848 is a reply to message #576761] |
Fri, 08 February 2013 09:31  |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
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.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
