COALESCE declaration

[NitinJain]

Hello,
I am using the given insert statement while creating a function, but it gives the following error.
LINE/COL ERROR -------------------------------------------------------
65/2 PL/SQL: SQL Statement ignored
70/6 PLS-00201: identifier 'COALESCE' must be declared

INSERT INTO t_arch (id, parent_node_id, child_node_id, parent_ord_no, child_ord_no, type_id, specified_in_dom)
(SELECT sq_arch.NEXTVAL,
nodeId,
newChild,
1,
COALESCE(MAX(child_ord_no), 0) + 1,
newArchTypeId,
1
FROM t_arch
WHERE parent_node_id = nodeId AND
specified_in_dom = 1
);

Regards

 

[Bimalaggarwal]

Which Version or Oracle are you using ?
I think COALESCE expression is avaliable from Oracle 9i onwards.

 

[sem]

COALESCE works on 9i only. Is your database (client-side pl/sql engine) 9i?

 

[sfvb]

Since there are only two choices, why not just use the NVL function:

...
NVL(MAX(child_ord_no), 0) + 1,
...

 

[NitinJain]

Hello Bimal,
I think Coalesce expresson is available from Oracle version 8.

http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58232/apc.htm

Regards

Nitin

 

[NitinJain]

Sorry Bimal,
I have found out that Coalesce expression is only available from 9i.

Thanks for your help.

Nitin