I am trying to create a lobby data source using an intersect query.
Effectively the query is pulling from the same view but using different criteria to match the records.
The query works perfectly in SQL developer but I am having issues being able to make it work in the data source.
Alternatively is there a way in a lobby to display quick report results rather than a link to the quick report?
Many Thanks
Best answer by Tracy Norwillo
If I understand you correctly, the following condition should work to get shop orders satisfying both sets of conditions. Of course, putting the intersect in an IAL would work too.
exists (select 1 from so_oper_dispatch_list a
where so_oper_dispatch_list.order_no = a.order_no
and so_oper_dispatch_list.release_no = a.release_no
and so_oper_dispatch_list.sequence_no = a.sequence_no
and a.operation_no = '1000' and a.oper_status_code = 'Released'
and a.work_center_no = 'B0151' and a.state = 'Started'
)
and exists (select 1 from so_oper_dispatch_list b
where so_oper_dispatch_list.order_no = b.order_no
and so_oper_dispatch_list.release_no = b.release_no
and so_oper_dispatch_list.sequence_no = b.sequence_no
and b.operation_no = '999' and b.oper_status_code = 'Closed' and b.state = 'Started'
)
As far as I can tell, the Condition on a Lobby data source is what would go in a WHERE clause. INTERSECT is not really part of a WHERE clause, so it doesn’t fit there.
Have you thought about creating an IAL then querying that as your data source’s View? You can also employ API method calls in the Condition, but I am not sure that can help you here…
Thanks,
Joe Kaufman
If I understand you correctly, the following condition should work to get shop orders satisfying both sets of conditions. Of course, putting the intersect in an IAL would work too.
exists (select 1 from so_oper_dispatch_list a
where so_oper_dispatch_list.order_no = a.order_no
and so_oper_dispatch_list.release_no = a.release_no
and so_oper_dispatch_list.sequence_no = a.sequence_no
and a.operation_no = '1000' and a.oper_status_code = 'Released'
and a.work_center_no = 'B0151' and a.state = 'Started'
)
and exists (select 1 from so_oper_dispatch_list b
where so_oper_dispatch_list.order_no = b.order_no
and so_oper_dispatch_list.release_no = b.release_no
and so_oper_dispatch_list.sequence_no = b.sequence_no
and b.operation_no = '999' and b.oper_status_code = 'Closed' and b.state = 'Started'
)
+14I think the best to do this is create a function in your package and you can call it from your data source.
Is there a reason
Thanks,
Joe Kaufman
Thanks everyone - I wanted to avoid the creating IAL as I am trying to demonstrate shop orders that are sitting between the last closed operation and the next station on the production line. - As our business has multiples lines and stations I don’t feel the IAL is the right way to go.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
