E9.2 UDO QUERY using complex AND and OR

[JohnDanter2]

Hi folks

I know UDO QUERYs have the match any or match all setting but how would write some complex queries in the tool as match anu is not enough

Example
WHERE
liglpt = 'IN60'
AND waansa IN ( 222710, 222942 )
AND (
( t3rmk = 'FA' AND lilots = '8' )
OR ( t3rmk <> 'FA' ) )
AND limcu IN ( ' 715', ' 715P' )
AND lipqoh > 0

I find this neigh on impssible in JDE UDO queries and usually resort to ER filters in Grid Record is Fetched. I'd rather use UDOs

Thanks

John

 

[Kevin Long]

Hi John,

I agree that you would not be able to do this using an Enhanced Query in an Interactive Application or a data request. If you have readonly access to the JDE Business Data you could use a database connector. That's how I would normally handle a complex query. You could also do this in a logic extension in 9.2.7 where you can build a data request and manually filter out the records you don't want based on the t3rmk field before returning the array.

However, I'm sure you already know this, and was just lamenting the inability to mix ANDs and ORs in an enhanced query. If that is the case, I'm right there with you.

Kevin

 

[DSauve]

This sounds like a great enhancement idea. I encourage you to submit this as an enhancement using Quest's enhancement tool. Oracle works closely with the various Quest special interest groups to gather, evaluate and include them in future product releases. That being said, once you create the enhancement, what really gets Oracle's attention is if other customers rank the enhancement - Oracle is looking for number of customers interested in the enhancement, as well as how highly they rank it.

 

[JohnDanter2]

Hi John,

I agree that you would not be able to do this using an Enhanced Query in an Interactive Application or a data request. If you have readonly access to the JDE Business Data you could use a database connector. That's how I would normally handle a complex query. You could also do this in a logic extension in 9.2.7 where you can build a data request and manually filter out the records you don't want based on the t3rmk field before returning the array.

However, I'm sure you already know this, and was just lamenting the inability to mix ANDs and ORs in an enhanced query. If that is the case, I'm right there with you.

Kevin

Hi Kevin

No no all good ideas my friend.
How I normally solve is by using a dropped table and using the replace as SQL to do it....or the ER. Both work but the downisde of it it is not visible to the user (or developers in the future) like a UDO query is
So all suggestions are welcome. We have a multitude of ways to solve a problem and hearing how others would do it opens your mind

 

[JohnDanter2]

This sounds like a great enhancement idea. I encourage you to submit this as an enhancement using Quest's enhancement tool. Oracle works closely with the various Quest special interest groups to gather, evaluate and include them in future product releases. That being said, once you create the enhancement, what really gets Oracle's attention is if other customers rank the enhancement - Oracle is looking for number of customers interested in the enhancement, as well as how highly they rank it.

Good call
All they need to do is have open and close bracket ability and it would be a seriously poweful tool and save me my hairline

 

[markdcci]

Good suggestion by Don. In fact, I first encountered this situation a few years ago and opened an SR with Oracle and they entered a bug request: Bug 28221948. The status is now at "19 - Approved for User Group Voting". I believe the enhancement request in Quest's website is number 11880.

http://cloudnnf.questoraclecommunity.org/questenhancementtool/ViewEnhancement.aspx?id=11880

I agree that it would make it a much more powerful tool if they were able to incorporate it at some point.

 

[DaveWagoner]

Good suggestion by Don. In fact, I first encountered this situation a few years ago and opened an SR with Oracle and they entered a bug request: Bug 28221948. The status is now at "19 - Approved for User Group Voting". I believe the enhancement request in Quest's website is number 11880.

http://cloudnnf.questoraclecommunity.org/questenhancementtool/ViewEnhancement.aspx?id=11880

I agree that it would make it a much more powerful tool if they were able to incorporate it at some point.

Thanks for looking it up! Upvoted the quest enhancement.

The other thing Oracle would like us to do is to enter a SR with them to be added to the associated bug #. It's a bit of double maintenance for us but it shows our earnestness to see the enhancement go through.

Oracle support knows just what to do to add you/your company to the bug, it's a quick thing to get done.

 

[DSauve]

Great - thanks for including the link to the Quest enhancement! It's up to 17 votes now. Keep those votes coming.

 

[DaveWagoner]

~1 hour from submitting a "quick" enhancement request to being "added" to the bug in Oracle support. Clayton Seeley mentioned in a recent conference that it would be best if we do this in addition to submit/vote on enhancements in the quest tool.

 

[JohnDanter2]

I love this group. Thanks for the link Mark

Thanks folks. Voted but also added my own

Under Construction - Quest Oracle Community

cloudnnf.questoraclecommunity.org

This one is super useful too

Under Construction - Quest Oracle Community

cloudnnf.questoraclecommunity.org

 

[DaveWagoner]

I love this group. Thanks for the link Mark

Thanks folks. Voted but also added my own

Under Construction - Quest Oracle Community

cloudnnf.questoraclecommunity.org

This one is super useful too

Under Construction - Quest Oracle Community

cloudnnf.questoraclecommunity.org

Nice find on the 2nd!

On the new one, I think we might look at it (we do and will, every other month) and think it's a bit similar to 11880? When we run into those we'd typically close it out as a duplicate, then direct the submitter to the other one to upvote/be added to