Advanced SQL Search - List of UNIQUE values?

Hey,

Try

Select part_no, min(arrival_date) from …. where ARRIVAL_DATE > to_date( '20200101000000', 'YYYYMMDDHH24:MI:SS' )  Group by part_no

/Johannes

Sorry didn’t really read that you are using a saved query. Try this instead:

(source_part_no, arrival_date) in (Select source_part_no, min(arrival_date) arrival_date from RECEIPT_INFO where ARRIVAL_DATE > to_date( '20200101000000', 'YYYYMMDDHH24:MI:SS' )  Group by source_part_no)

i tried it on Apps10 where it’s source_part_no, if you are on a lower version just delete the source_

/Johannes

Hi Johannes,

I’m fairly new to SQL.

I tried pasting: 

part_no, arrival_date) in (Select part_no, min(arrival_date) arrival_date from RECEIPT_INFO where ARRIVAL_DATE > to_date( '20200101000000', 'YYYYMMDDHH24:MI:SS' )

directly into the “SQL where expression” area, and selected Group on Part No in the table Group/Sum.

I’m receiving the error code: User '[me]' is not allowed to access the database object 'RECEIPT_INFO'.

Is this an issue of access, or maybe RECIPT_INFO is not a recognized statement?

Could be different reasons, but it seems like Access issues. Try adding the applications owner before the view. Usually it’s ifsapp. Might be different for your company though. So instead of Receipt_info try ifsapp.Receipt_info. In case your company uses a different appowner replace ifsapp with whatever your Application owner is.

Hi,

I still receive the error message with IFSAPP.RECEIPT_INFO.

However, I see I receive the same error message with whatever I type in the RECEIPT_INFO area in your code. If I replace it with ABC123 is still receive:

User '[me]' is not allowed to access the database object 'ABC123'.

Try to use IFSAPP.RECEIPT_INFO instead.

Since the view RECEIPT_INFO (and all other views in IFS Applications) is owned by the application owner user, normally IFSAPP, your user must add the owner prefix, “IFSAPP.”, to be able to see it.

Also paste this into the advanced query. The GROUP BY is for the sub-select so do not remove it.

(source_part_no, arrival_date) in
(Select source_part_no, min(arrival_date) arrival_date
 from IFSAPP.RECEIPT_INFO
 where ARRIVAL_DATE >= to_date('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY source_part_no)

(deleted)

 (part_no, arrival_date) in
(Select part_no, min(arrival_date) arrival_date
 from IFSAPP.PURCHASE_RECEIPT_NEW
 where ARRIVAL_DATE > to_date('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY part_no)

Hi Tomas,

Wow, that actually worked. I guess I received the error message due to unrecognised  statement/code after all:)

I see it would be beneficial to be able to make this unique value list with multiple criteria. How can I build upon this code I’ve been given?

For instance: I’d like a search result list to show the first delivery of PART_NO + Supplier since >2020.

As one part can have multiple suppliers, I’d be interested in receiving a list of the first delivery of that part from multiple suppliers.

Apologies for expanding upon my original inquiry..

Again, thanks a lot for your help so far Tomas and Novacura.

If you want to have one line per part and supplier we need to group the sub-select on supplier also. Supplier ID is called vendor_no so it would be like this.

(part_no, vendor_no, arrival_date) in
(Select part_no, vendor_no, min(arrival_date) arrival_date
 from IFSAPP.PURCHASE_RECEIPT_NEW
 where ARRIVAL_DATE > to_date('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY part_no, vendor_no)

 (part_no, vendor_no, arrival_date) in
(Select part_no, vendor_no, min(arrival_date) arrival_date
 from IFSAPP.PURCHASE_RECEIPT_NEW
 where ARRIVAL_DATE > to_date('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY part_no, vendor_no)

Hi Tomas

This work great.

I can see duplicates still happen, but the reason is multiple lines are received at exactly the same time. Sometimes with different order no., sometimes not. See attached pictures.

Intended or not, this is useful for counting correct qty of first-delivery. 

Is it however also possible to exclude part no+supplier duplicates in this instance? In some cases I realise an arbitrary order no. would be chosen, but that would be okay in this case.

Thanks again.