Hello There,
I have a simple WITH Clause sql construct. On execution it gives the below error. Could you please advise on what needs to be corrected?:
oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLException: Invalid column index
Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL Query:
<code>
WITH
------------
FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
CURSOR c1 IS
SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
cc VARCHAR2(100);
BEGIN
FOR x IN c1
LOOP
cc := x.aa;
END LOOP;
RETURN cc;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'Error GetValue1: '|| p_value;
END GetValue1;
------------
PO AS
(SELECT * FROM po_headers_all)
------------
, POLines AS
(SELECT * FROM po_lines_all)
------------
Main Query
------------
SELECT po.segment1, polines.po_header_id , GetValue1('ABC') GetValue--, GetValue2()
FROM PO, POLines
WHERE polines.po_header_id = po.po_header_id
</code>
Thank you.
Look forward to hearing from you.
Regards,
Don