SQL & PL/SQL

Hi,

The below query is a part of a cursor which takes in p_po_number as input with datatype same as segment1 of po_headers_all (varchar2(20)). The columns event_system_id1 to event_system_id4 are varchar2(240). In most cases p_po_number will be null as the program is scheduled and the input parameter of p_po_number is not passed. In effect, for most cases, the below query will end like event_system_id1 = NVL(NULL,event_system_id1)

I am getting an error of INVALID NUMBER

SELECT TO_NUMBER (event_system_id1)
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND event_system_id1 =
NVL (p_po_number, event_system_id1)

----------------------------------------------------------------------------------------------------
I modified the query to


SELECT event_system_id1
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND event_system_id1 =
NVL (p_po_number, event_system_id1)

which still threw the same error thus ruling out the cause of the issue being that to_number leaving only the implicit conversions of NVL.
---------------------------------------------------------------------------------------------------------
I further modified the query to


SELECT TO_NUMBER (event_system_id1)
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND p_po_number is not null

which gave me the desired result in a round about way.

What confuses me here is both p_po_number and event_system_id1 are of varchar2 type. Why am I getting the error of INVALID NUMBER here and why is NVL converting the second parameter NUMBER datatype when the first one is VARCHAR2 even though null.

I am working in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

P.S: The column event_system_id1 is not restricted to store numbers alone. It also contains character values though I use it only for storing number as it is shared table.