Skip to main content

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

  

@K.mitch 

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'
  )

 

@K.mitch 

I think the best to do this is create a function in your package and you can call it from your data source.

Hi @K.mitch 

intersect in data scources is not possible. But you can create an IAL in this case.

 

Is there a reason @Tracy Norwillo ‘s solution won’t work? A sub-SELECT in the WHERE condition seems rather elegant. Nothing wrong with an IAL, but I find them a bit tedious to set up and maintain across environments, etc.

 

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.  

 

@Tracy Norwillo  - thanks - this doesn’t quite work but I will explore this a bit further.

 

 

Terms & ConditionsCookie settings