Hi

I am new to LLBLGen and need help with converting the following query to O/R. I have tried a number of methods and keep coming up with a not a group by error. the SQL is

        SELECT min(na.EUC_CD) EUC_CD, na.NMI, count(inv_hist_sun) QTY
        FROM slim.Detailed_Inventory di, slim.nmi_allocation na
        WHERE di.nmi (+) = na.nmi 
            AND ((Inv_Hist_SUN IS NULL AND na.Contest_Fl = 'Y' AND Obsolete_Fl = 'N')
            OR (na.contest_fl = 'Y' AND (sysdate BETWEEN Date_Fr AND NVL (Date_To, sysdate+1))))
        GROUP BY na.EUC_CD,na.NMI;

The code that I have used is:

        ResultsetFields fields = new ResultsetFields(3);
        fields.DefineField(new EntityField("EucCd",
new ScalarQueryExpression(NmiAllocationFields.EucCd.SetAggregateFunction(AggregateFunction.Min))), 0);
        fields.DefineField(NmiAllocationFields.Nmi,1);
        fields.DefineField(new EntityField("Qty", new ScalarQueryExpression(DetailedInventoryFields.InvHistSun.SetAggregateFunction(AggregateFunction.Count))), 2);             

        RelationCollection rcCount = new RelationCollection();
        rcCount.Add(DetailedInventoryEntity.Relations.NmiAllocationEntityUsingNmi,JoinHint.Right);

        IPredicateExpression Filter = new PredicateExpression();

        //Filter.Add(DetailedInventoryFields.InvHistSun.IsNull==true);
        Filter.Add(NmiAllocationFields.ContestFl == "Y").AddWithAnd(NmiAllocationFields.ObsoleteFl == "N");
        Filter.AddWithOr(NmiAllocationFields.ContestFl == "Y");
        Filter.AddWithAnd(DetailedInventoryFields.DateFr <= System.DateTime.Now);
        Filter.AddWithAnd(DetailedInventoryFields.DateTo >= System.DateTime.Now.AddDays(1.0));

        ISortExpression Sort = new SortExpression();
        Sort.Add(NmiAllocationFields.EucCd | SortOperator.Ascending);

        IGroupByCollection group = new GroupByCollection();
        group.Add(fields[1]);


        DataTable dtDetails = new DataTable();      
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dtDetails, 200, null, Filter, rcCount, false, group, null, 0, 0);

Thanks

Please post the generated SQL. (enable DQE tracing -> Troubleshooting & Debugging in the manual) And post the exact exception and stack trace.

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of a retrieval query: ORA-00979: not a GROUP BY expression\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 * FROM (SELECT DISTINCT (SELECT MIN(\"SLIM\".\"NMI_ALLOCATION\".\"EUC_CD\") AS \"EucCd\" FROM \"SLIM\".\"NMI_ALLOCATION\") AS \"EucCd\", \"SLIM\".\"NMI_ALLOCATION\".\"NMI\" AS \"Nmi\", (SELECT COUNT(\"SLIM\".\"DETAILED_INVENTORY\".\"INV_HIST_SUN\") AS \"InvHistSun\" FROM \"SLIM\".\"DETAILED_INVENTORY\") AS \"Qty\" FROM \"SLIM\".\"NMI_ALLOCATION\" , \"SLIM\".\"DETAILED_INVENTORY\" WHERE \"SLIM\".\"NMI_ALLOCATION\".\"NMI\"=\"SLIM\".\"DETAILED_INVENTORY\".\"NMI\"(+) AND ( ( \"SLIM\".\"NMI_ALLOCATION\".\"CONTEST_FL\" = :ContestFl1 AND \"SLIM\".\"NMI_ALLOCATION\".\"OBSOLETE_FL\" = :ObsoleteFl2 OR \"SLIM\".\"NMI_ALLOCATION\".\"CONTEST_FL\" = :ContestFl3 AND \"SLIM\".\"DETAILED_INVENTORY\".\"DATE_FR\" <= :DateFr4 AND \"SLIM\".\"DETAILED_INVENTORY\".\"DATE_TO\" >= :DateTo5)) GROUP BY \"SLIM\".\"NMI_ALLOCATION\".\"NMI\") WHERE rownum <= 200\r\n\tParameter: :ContestFl1 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"Y\".\r\n\tParameter: :ObsoleteFl2 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"N\".\r\n\tParameter: :ContestFl3 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"Y\".\r\n\tParameter: :DateFr4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 16/04/2009 8:29:40 AM.\r\n\tParameter: :DateTo5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 17/04/2009 8:29:40 AM.\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.DetailedInventoryEntity.getNMISLCount() in C:\User\SLIM LLBLGen Test\EntityClasses\DetailedInventoryEntity.cs:line 2233 at SLIM_Win.frmNMISLCnt.frmNMISLCnt_Load(Object sender, EventArgs e) in C:\User\SLIM ASP\SLIM\SLIM_Win\frmNMISLCnt.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.nMISLCountToolStripMenuItem_Click(Object sender, EventArgs e) in C:\User\SLIM ASP\SLIM\SLIM_Win\frmMain.cs:line 112 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()

Thanks

The problem is in the way you define the resultSet fields. Instead of:

ResultsetFields fields = new ResultsetFields(3); fields.DefineField(new EntityField("EucCd", new ScalarQueryExpression(NmiAllocationFields.EucCd.SetAggregateFunction(AggregateFunction.Min))), 0); fields.DefineField(NmiAllocationFields.Nmi,1); fields.DefineField(new EntityField("Qty", new ScalarQueryExpression(DetailedInventoryFields.InvHistSun.SetAggregateFunction(AggregateFunction.Count))), 2);

You should use:

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(NmiAllocationFields.EucCd, 0, "EucCd", AggregateFunction.Min);
fields.DefineField(NmiAllocationFields.Nmi, 1);
fields.DefineField(DetailedInventoryFields.InvHistSun, 2, "Qty", AggregateFunction.Count);

Thanks for that. Got most of it working. I am having trouble with the null compare with the InvHistSun. This field is a decimal in the oracle 8i database.

     SELECT min(na.EUC_CD) EUC_CD, na.NMI, count(inv_hist_sun) QTY
        FROM slim.Detailed_Inventory di, slim.nmi_allocation na
        WHERE di.nmi (+) = na.nmi 
            AND ((Inv_Hist_SUN IS NULL AND na.Contest_Fl = 'Y' AND Obsolete_Fl = 'N')
            OR (na.contest_fl = 'Y' AND (sysdate BETWEEN Date_Fr AND NVL (Date_To, sysdate+1))))
        GROUP BY na.EUC_CD,na.NMI;

I have tried the following IPredicateExpressions to get it right.

Filter.Add(DetailedInventoryFields.InvHistSun == null); comes up with ambiguous call.

Filter.Add(DetailedInventoryFields.InvHistSun.ToString() == "");, Filter.Add(DetailedInventoryFields.InvHistSun.IsNull==true);, Filter.Add(DetailedInventoryFields.InvHistSun.IsNull.Equals(null)); and Filter.Add(DetailedInventoryFields.InvHistSun.ToString() == null); give cann't convert from bool to IPredicate error.

Can you please assist?

Filter.Add((DetailedInventoryFields.InvHistSun == System.DBNull.Value));

thanks for your help with this. I have run into a further problem in that the results don't match what results when running directly in Oracle. The problem is the where clause.

        WHERE di.nmi (+) = na.nmi 
            AND ((Inv_Hist_SUN IS NULL AND na.Contest_Fl = 'Y' AND Obsolete_Fl = 'N')
            OR (na.contest_fl = 'Y' AND (sysdate BETWEEN Date_Fr AND NVL (Date_To, sysdate+1))))

The SQL statement that is generated is:

QueryExecuted="\r\n\tQuery: SELECT * FROM (SELECT DISTINCT MIN(\"SLIM\".\"NMI_ALLOCATION\".\"EUC_CD\") AS \"EucCd\", \"SLIM\".\"NMI_ALLOCATION\".\"NMI\" AS \"Nmi\", COUNT(\"SLIM\".\"DETAILED_INVENTORY\".\"INV_HIST_SUN\") AS \"Qty\" FROM \"SLIM\".\"NMI_ALLOCATION\" , \"SLIM\".\"DETAILED_INVENTORY\" WHERE \"SLIM\".\"NMI_ALLOCATION\".\"NMI\"=\"SLIM\".\"DETAILED_INVENTORY\".\"NMI\"(+) AND ( ( \"SLIM\".\"DETAILED_INVENTORY\".\"INV_HIST_SUN\" IS NULL AND \"SLIM\".\"NMI_ALLOCATION\".\"CONTEST_FL\" = :ContestFl1 AND \"SLIM\".\"NMI_ALLOCATION\".\"OBSOLETE_FL\" = :ObsoleteFl2 OR \"SLIM\".\"NMI_ALLOCATION\".\"CONTEST_FL\" = :ContestFl3 AND \"SLIM\".\"DETAILED_INVENTORY\".\"DATE_FR\" <= :DateFr4 AND \"SLIM\".\"DETAILED_INVENTORY\".\"DATE_TO\" > :DateTo5)) GROUP BY \"SLIM\".\"NMI_ALLOCATION\".\"NMI\" ORDER BY \"SLIM\".\"NMI_ALLOCATION\".\"NMI\" ASC) WHERE rownum <= 200\r\n\tParameter: :ContestFl1 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"Y\".\r\n\tParameter: :ObsoleteFl2 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"N\".\r\n\tParameter: :ContestFl3 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"Y\".\r\n\tParameter: :DateFr4 : DateTime. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: 22/04/2009.\r\n\tParameter: :DateTo5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 23/04/2009 9:39:45 AM.\r\n"

When I run the following query in Oracle it gives the correct counts:

        SELECT min(na.EUC_CD) EUC_CD, na.NMI, count(inv_hist_sun) QTY
        FROM slim.Detailed_Inventory di, slim.nmi_allocation na
        WHERE di.nmi (+) = na.nmi 
            AND ((Inv_Hist_SUN IS NULL AND na.Contest_Fl = 'Y' AND Obsolete_Fl = 'N')
            OR (na.contest_fl = 'Y' AND (sysdate BETWEEN Date_Fr AND NVL (Date_To, sysdate+1))))
        GROUP BY na.NMI.

When use run the query in the application I get no rows. Testing using the generated statement indicates that the problem is with the dates.

I have used the FieldBetweenPredicate and dbFunctionCall to set up a between expression and convert the NVL correctly.

Filter.AddWithAnd(new FieldBetweenPredicate(System.DateTime.Now, DetailedInventoryFields.DateFr, new DbFunctionCall("NVL", new object[] { DetailedInventoryFields.DateTo, System.DateTime.Now.AddDays(1.0) })));

I get syntax errors with the System.DateTime.Now as it requires an entity here. I have set up entityfields and set the System.DAteTime.Now to it using the current value etc but it doesn't get past the syntax errors.

Can you please tell me where I am going wrong?

thanks

Would you please try to use 2 predicates instead of the FieldBetweenPredicate ? Instead of: sysdate BETWEEN Date_Fr AND NVL (Date_To, sysdate+1)

Try implementing: Date_Fr < sysdate AND NVL (Date_To, sysdate+1) > sysdate

Thanks for your help with this.