Hi

Am setting up a query to only select the first two characters from a field held in a table on a Oracle database. The query I have to convert is:

SELECT DISTINCT SUBSTR(DEV_TYPE_ID,0,2) FROM DEVICE_TYPE WHERE DEVICE_TYPE.DEV_CAT = 'UMS'

The code that I am using to do this is:

        ResultsetFields fields = new ResultsetFields(1);
        DbFunctionCall dvtSubStr = new DbFunctionCall("SUBSTR{0},0,2", new object[] { DeviceTypeFields.DevTypeId });
        fields.DefineField(DeviceTypeFields.DevTypeId,0);
        fields[0].SetExpression(dvtSubStr);

        IPredicateExpression Filter = new PredicateExpression();
        Filter.Add(DeviceTypeFields.DevCat == "UMS");

        ISortExpression Sort = new SortExpression();
        Sort.Add(DeviceTypeFields .DevTypeId | SortOperator .Ascending );

        DataTable dtDetails = new DataTable();                      
        TypedListDAO dao = new TypedListDAO();


        dao.GetMultiAsDataTable(fields, dtDetails, 50,Sort, Filter, null, false, null, null, 0, 0);

I am getting the following error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of a retrieval query: ORA-00923: FROM keyword not found where expected\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="03132009" RuntimeVersion="2.6.0.0" QueryExecuted="\r\n\tQuery: SELECT SUBSTR\"SLIM\".\"DEVICE_TYPE\".\"DEV_TYPE_ID\",0,2 AS \"DevTypeId\" FROM \"SLIM\".\"DEVICE_TYPE\" WHERE ( ( \"SLIM\".\"DEVICE_TYPE\".\"DEV_CAT\" = :DevCat1)) ORDER BY \"SLIM\".\"DEVICE_TYPE\".\"DEV_TYPE_ID\" ASC\r\n\tParameter: :DevCat1 : AnsiString. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: \"UMS\".\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) at SLIM.Data.DaoClasses.TypedListDAO.GetMultiAsDataTable(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) in C:\User\SLIM LLBLGen Test\DaoClasses\TypedListDAO.cs:line 48 at SLIM.Data.EntityClasses.DeviceTypeEntity.getSubDevType() in C:\User\SLIM LLBLGen Test\EntityClasses\DeviceTypeEntity.cs:line 2615 at SLIM_Win.frmSundry.Load_DevType() in C:\User\SLIM ASP\SLIM\SLIM_Win\frmSundry.cs:line 38 at SLIM_Win.frmSundry.frmSundry_Load(Object sender, EventArgs e) in C:\User\SLIM ASP\SLIM\SLIM_Win\frmSundry.cs:line 33 at System.Windows.Forms.Form.OnLoad(EventArgs e) at System.Windows.Forms.Form.OnCreateControl() at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ContainerControl.WndProc(Message& m) at System.Windows.Forms.Form.WmShowWindow(Message& m) at System.Windows.Forms.Form.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.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.IntCreateWindowEx(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam) at System.Windows.Forms.UnsafeNativeMethods.CreateWindowEx(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam) at System.Windows.Forms.NativeWindow.CreateHandle(CreateParams cp) at System.Windows.Forms.Control.CreateHandle() at System.Windows.Forms.Form.CreateHandle() at System.Windows.Forms.Control.get_Handle() at System.Windows.Forms.Form.SetVisibleCore(Boolean value) at System.Windows.Forms.Control.Show() at SLIM_Win.frmMain.LaunchChildForm(Form childForm) in C:\User\SLIM ASP\SLIM\SLIM_Win\frmMain.cs:line 50 at SLIM_Win.frmMain.tsmSundry_Click(Object sender, EventArgs e) in C:\User\SLIM ASP\SLIM\SLIM_Win\frmMain.cs:line 142 at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.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.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms. UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at SLIM_Win.Program.Main() in C:\User\SLIM ASP\SLIM\SLIM_Win\Program.cs:line 17 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

I know that the problem is in the way that I have done the function call but I can't see the error.

Any help would be appreciated.

thanks

You have missed the brackets of the SUBSTR function. You should use the following line of code instead.

DbFunctionCall dvtSubStr = new DbFunctionCall("SUBSTR({0},0,2)", new object[] { DeviceTypeFields.DevTypeId });