Help with sys func Set Selection Group

Ok, so in 9.0 (or some TR past Xe) Oracle finally gave us the ability to set parenthesis around data selection in ER code like we have always had in C code with JDB_SetSelectionX.

However, I am struggling to get it to do *excatly* what I want since it is not implemented the same way as it is in JDB_SetSelectionX.

Basically on an APPL I want the user to set filter/qbe fields and then I want to programatically in ER code append my own data selection which is a list of values for a single field so I have to put in a bunch of OR clauses. I am using the sys func Set Selection Group, and I am running into problems when I have 3 or more values.

What I want is:
WHERE (FirstName=FC_FirstName AND LastName=QC_LastName) AND (MCU='111' OR MCU='222' OR MCU='333' OR MCU='444')

What I am getting is:
WHERE (FirstName=FC_FirstName AND LastName=QC_LastName) AND (MCU='111' OR (MCU='222' OR (MCU='333' OR MCU='444')))


This works but I dont like all the extra parenthesis. It needs the ER equivalent of JDEDB_PAREN_OPEN, JDEDB_PAREN_CLOSE but...
Anyone know exactly how Set Selection Group works or have any documentation on it?

I have tried all sorts of different combinations of Set Selection Group(And/Or/None) along with Set Selection(And/Or/None) and I just can't get it to do exactly what I want.

What a coincidence. I fought with the Group monster all morning:

Here are my tests: do what you want to figure out how the thing works. IMO it doesn't work nearly well enough!

<font class="small">Code:</font><hr /><pre> Set Selection Append Flag(FC Grid, <Yes>)
Set Selection Group(FC Grid, "F41021", "pqoh", <Not Equal To>, <Zero>, <Or>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, "F41021", "ITM", <Equal To>, <Zero>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021
WHERE (((LIITM = ? OR (LIPQOH <> ? OR LIPQOH > ? )))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, "F41021", "ITM", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021
WHERE ((((LIITM = ? AND LIITM = ? ) AND (LIPQOH <> ? OR LIPQOH > ? )))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE
(((LIITM = ? OR (LIITM = ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((LIITM = ? OR (LIITM = ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM = ? ) OR (LIITM > ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((LIITM = ? OR ((LIITM = ? AND LIITM > ? ) AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((LIITM < ? OR ((LIITM = ? AND LIITM > ? ) AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((((LIITM = ' ' AND LIITM < 0 ) AND LIITM > 0 ) AND (LIPQOH <> ? OR LIPQOH > ? )))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM < ? ) OR (LIITM > ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC
~~~~~~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM < ? ) OR (LIITM > ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "pqoh", <Not Equal To>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM = ? ) OR (LIITM > ? AND (LIPQOH <> ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM = ? ) OR (LIITM > ? AND (LIPREQ > ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Blank>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((LIITM = ? OR ((LIITM = ? AND LIITM > ? ) AND (LIPREQ > ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Not Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE
(
(
(
(
(LIITM = ? AND LIITM > ? )
OR
(LIITM <> ? AND LIITM > ? )
)
OR
(
(LIITM < ? AND LIITM > ? )
AND
(LIPREQ > ? OR LIPQOH > ? )
)
)
)
) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <And>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Not Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((((LIITM = ? AND LIITM > ? ) OR (LIITM <> ? AND LIITM > ? )) OR (((LIITM < ? AND LIITM > ? ) AND LIITM > ? ) AND (LIPREQ > ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC
~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <None>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Not Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)
Set Selection(FC Grid, F41021, "ITM", <Greater Than>, <Blank>, <And>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE (((((LIITM = ? AND LIITM > ? ) OR (LIITM <> ? AND LIITM > ? )) OR (((LIITM < ? AND LIITM > ? ) AND LIITM > ? ) AND (LIPREQ > ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Selection Group(FC Grid, F41021, "PREQ", <Greater Than>, <Zero>, <None>)
Set Selection Group(FC Grid, F41021, "pqoh", <Greater Than>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Not Equal To>, <Zero>, <Or>)
Set Selection(FC Grid, F41021, "ITM", <Less Than>, <Zero>, <Or>)

SELECT LILOTN,LILOCN,LIMCU,LIITM FROM CV2DTA.F41021 WHERE ((((LIITM = ? OR LIITM <> ? ) OR (LIITM < ? AND (LIPREQ > ? OR LIPQOH > ? ))))) ORDER BY LIITM ASC , LIMCU ASC , LILOCN ASC , LILOTN ASC
</pre><hr />

LOL. I spent a day on it as well and ended up with a text document pretty much like what you posted. I just can't figure out what they are doing. They really, really need a way to explicitly control the begin/end parenthesis. I am getting a WHERE clause that will work in this ONE isolated case, but if I ever need to do anything more complex with multiple fields or anything more dynamic I am afraid that this API call is going to be useless.

It appears that when you mix "Set Selection Group" with "Set Selection" it try's to process the "Set Selection" calls first and then the Group calls, but depending on how many calls you are making, you get wildly sporadic groupings.

It's the 'OR' that kills it. Once you start putting those in, the whole thing falls apart.

You can control parens, but it's got to be done by hand in a C function.

Look at B4200510: JDB_SetNestedSelection, and JDEDB_PAREN_CLOSE/Open are used, plus aggregates.

You can see how they build:

<font class="small">Code:</font><hr /><pre>SELECT RPBCRC, SUM(RPAAP), SUM(RPAG) FROM F03B11 WHERE
RPAN8 = <Sold To> AND RPCO = <Company> AND RPBCRC = <Cust Currrency> AND
RPPST NOT IN ('P', 'S')AND ((RPDCT != 'RU' AND RPDGJ <= 'AgingDate') OR
(RPDCT = 'RU' AND (RPDGJ <= 'AgingDate' OR RPPOST = 'P'))) GROUP BY RPBCRC </pre><hr />

In RDA, however, you're out of luck. Well, we both are. Wait, no, the whole planet is!

At the Sunday Geek Meet (Enhancement Request thing) At Collaborate I asked for for the ability to be able to define 'Real' Data selection from within ER in a UBE - the the request was supposed to be tagged to my name.

I haven't anything from Oracle - whether it was approved or just laughed at....

Sir Larry or Jean - any insight?

(db)

I just wish they would give all the functionality in ER code that they give in C via JDB_SetSelectionX as far as controlling the WHERE clause.

- The ability to set parens (as discussed here)
- The ability to do an IN clause
- probably a few more I am missing

Not so concerned about the aggregates since I can do that in C, but when it comes to controlling the SQL that populates a grid you are stuck with the neutered ER code.

Hi Daniel,

Those requests are in Quest's hands waiting to be uploaded so everyone can see them. . .

Oracle also has the list of enhancment requests in their hot little hands.

As you know this particular request has been around for a while.

I "think" I heard an oracle product manager say this area was on the "to do" list - but don't hold me to it.

Cheers,