Solved

Using a Intersect query in a lobby data source

  • 8 February 2022
  • 6 replies
  • 251 views
K
Rank
Userlevel 1
Badge +3

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

  

icon

Best answer by Tracy Norwillo 8 February 2022, 21:30

View original
This topic has been closed for comments

6 replies

S
Rank
Userlevel 6
Badge +12

@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

Just because something is part of a plan doesn't make it right.
T
Rank
Userlevel 4
Badge +11

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

 

ZTC ZTC JGOTA
Rank
Userlevel 6
Badge +14

@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.

JL
Link
Rank
Userlevel 7
Badge +22

Hi @K.mitch 

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

 

Kind regards
S
Rank
Userlevel 6
Badge +12

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

Just because something is part of a plan doesn't make it right.
K
Rank
Userlevel 1
Badge +3

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