Query build will not allow to select (First Distinct) value of Part Description
I have created a fairly simple query here selecting all order lines in a “Planned” or “Released” status, where Part No is like “-”, and any value in part description. I then sum the Sales Qty for the Part No that is grouped.
Our issue is our Part Description is not unique so I really do not need to separate by part description, but if I do not group with it the field does not return values. As you can see for Part No 100-ST50 we have a description of Diamond 20# and a description of Diamond 20lb, I simply need the first description shown for a reference and need the subtotal for part no to include all entries so the subtotal for part no 100-ST50 should be 950. I do not know how to have SQL simply return the first distinct value for part description.
Page 1 / 1
Hello
The limitation is that you cannot apply any aggregation function to non-numeric fields in the Group/Sum column. If you could apply a min/max to the Sales Part Description then this would be a simple fix. Of course this would be possible within a quick report.
Whilst this might not be the most efficient solution, you could add a Reference type custom field to Customer Order Line, to retrieve the Part Catalog Description. This would be unique per part and could be included in the group by condition.
Regards
Harley
@Harley - I am probably going to move forward with the Quick Report for this , however I am intrigued by your suggestion to add a Reference Type custom field. We have a table within IFS that now has distinct values for these part numbers but I cannot find where or how to tie to that table from this particular query. Since I am looking in the “Customer Order Lines” table, how do I pull a reference from a field in a different table within IFS? Any help you can give here is greatly appreciated. :-)
@Harley - I am probably going to move forward with the Quick Report for this , however I am intrigued by your suggestion to add a Reference Type custom field. We have a table within IFS that now has distinct values for these part numbers but I cannot find where or how to tie to that table from this particular query. Since I am looking in the “Customer Order Lines” table, how do I pull a reference from a field in a different table within IFS? Any help you can give here is greatly appreciated. :-)
@lmckim - if you are using IFS9 or greater, you can add a Custom Field to the CustomerOrderLine logical unit. The reference i was referring to in this case was CustomerOrderLine → SalesPart → Part Catalog.
You can retrieve information from another table in simplistic queries using sub-queries on the other table/field. Here is one I wrote last week for a user to retrieve RMA lines based on a field that is in the header of the customer order (REGION_CODE). This allows the user to select only the RMA lines applicable to their region even though the region_code is not carried down to the RMA line overview.
DATE_REQUESTED < trunc(to_date( '#TODAY#', 'YYYY-MM-DD-HH24:MI:SS' ))-45 and DATE_RETURNED is null and (OBJSTATE = (select &AO.RETURN_MATERIAL_LINE_API.FINITE_STATE_ENCODE__('Planned') from dual) or OBJSTATE = (select &AO.RETURN_MATERIAL_LINE_API.FINITE_STATE_ENCODE__('Released') from dual)) and ORDER_NO in (select order_no from inst1app.customer_order_cfv where contract in ('210','220','510','520','581') and REGION_CODE = '®ION_CODE')
In this manner, you don’t have to add multiple custom fields on multiple views.
@ShawnBerk this sounds exactly what I am looking for, I know I have the Part Description as unique in the inventory part in stock table. Can I contact you via email to send you an example of my SQL for you to take a look at?
Just to add some clarity for anyone who may come across this reply. My solution is only a pre-filter, it does not allow for displaying the result of a join from another table. You can display the join results using a Quick Report, but the simplified queries on a particular view won't pull in the value from another table as part of the query result. If you need it to be just a search and not a quick report, then the custom field from @Harley is the only solution to get it to be part of the display results.
Shawn - Thanks so much for clarifying for me. I will proceed with Quick Report.
