Skip to main content

I am trying to use CTE’s in a quick report, but when I try to execute the query in IFS’ Query Tool it gives me the error “Input ‘my_query’ ” did not match regular expression.

 

I tried with a basic CTE, that works in PL/SQL Developer:

 

WITH my_cte AS

(
SELECT 3+4 as result FROM DUAL
)

SELECT result from my_cte

 

I am not even trying to use a regular expression. Does anybody know why it does not work?

 Here is the detailed error log: 

 

Ifs.Fnd.FndSystemException: Explorer: An Unhandled Exception has occurred ---> System.NullReferenceException: Object reference not set to an instance of an object.
   at Ifs.Application.Fndadm.SQLQueryTool.frmDynamicSelect.ContainsAnyLOBItems(IList`1 columns)
   at Ifs.Application.Fndadm.SQLQueryTool.frmDynamicSelect.Execute(SalString sSql)
   at Ifs.Application.Fndadm.SQLQueryTool.frmDynamicSelect.UserMethod(SalNumber nWhat, SalString sMethod)
   at Ifs.Application.Fndadm.SQLQueryTool.frmDynamicSelect.vrtUserMethod(SalNumber nWhat, SalString sMethod)
   at Ifs.Fnd.ApplicationForms.cMethodManager.vrtUserMethod(SalNumber nWhat, SalString sMethod)
   at Ifs.Fnd.ApplicationForms.cMethodManager.cMethodManager_WindowActions(Object sender, WindowActionsEventArgs e)
   at PPJ.Runtime.Windows.SalWindow.InvokeWindowActions(WindowActionsEventArgs args, SalContext context, Int32 startIndex)
   at PPJ.Runtime.Windows.SalWindow.FireWindowActions(Int32 msgCode, IntPtr wParam, IntPtr lParam)
   at PPJ.Runtime.Windows.SalWindow.DispatchThenProcess(Message& m)
   at PPJ.Runtime.Windows.SalWindow.ProcessWndProc(Message& m)
   at PPJ.Runtime.Windows.SalForm.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   --- End of inner exception stack trace ---

The commands available in the query tool are more restricted than those available from PL/SQL developer.  Any command that is going to attempt to ‘create’ an entity as part of the SQL statement is going to fail.  The SQL query tool is completely read only and as such is bound by only connecting to the existing tables and views.

The commands available in the query tool are more restricted than those available from PL/SQL developer.  Any command that is going to attempt to ‘create’ an entity as part of the SQL statement is going to fail.  The SQL query tool is completely read only and as such is bound by only connecting to the existing tables and views.

Thank you for your explanation! 

But CTE’s should still work in a quick report right? I just have to write and test the query in PL/SQL Developer then.

Yes, the quick report functionality is in between PL/SQL developer and the SQL Query Tool, you should be able to do what you are wanting to do in the quick report, just not test it in the Query Tool.

Reply


Terms & ConditionsCookie settings