Idea Details

Use a local group for IN clause - F32433

Last activity 02-11-2019 01:16 PM
Langevin's profile image
08-22-2014 11:33 AM

When coding a readeach, it would be nice to have the where clause allow the coder to select a local group view that may have been populated elsewhere in the program.  For example,


Readeach Customer

where desired customer number in local_g customer_number.


Only details for those customers sitting in the local group customer view will be selected.


02-11-2019 01:16 PM

Due to the breadth and complexity of implementation, along with the potential difficulty created for existing applications, this idea has been moved to "Not Planned". At this time, we do not plan to implement this idea or consider it for future releases.


- Rob Thompson

09-03-2014 09:22 AM

I think this is the best idea.  Fill to Max the Last value in your group view and let the dbms optimize it.

09-02-2014 06:21 PM

I wouldn't dismiss any target - there's been too much of fracturing of features over time to my mind, already.  Further aggravating the problem won't help.


I still think the simplest answer would be to pad out with previously supplied values.  So if you only have 3 of the 5 supplied, then entries 4 and 5 are just the same as entry 1.  It might seem redundant, but it's trustworthy.  Fully enumerating all possibilities just seems ripe for code and size bloat, and a horrible nightmare for maintenance.  Is it not possible to do so?  It would just mean that you'd need to do a little jiggery pokery before invoking the SQL - store the LAST of the group, copy entry #1 values from LAST + 1 TO MAX, call the SQL, reset LAST value.


Your other alternative is to not issue precompiled SQL.  DB2 will support dynamic SQL, as will Oracle, so for the presence of an IN clause you could always have it issued dynamically instead.

09-02-2014 06:14 PM

Yes, you can't assume spaces is a good value.  It might be something you don't want to select on, and it's only valid for a character type anyway.  For numeric, datetimes, etc it would be different.


The only value you can truly rely on is one that has been passed in the view contents.

09-02-2014 06:02 PM

Funny you should mention intellectual property on ideas as I just now listened to a presentation where they stated that as part of being a member of these communities, all ideas are owned by CA (or something to that affect).


You are correct that the problem is wholly for targets that use pre-compilation but those target cover a majority (if not a large majority) of our client target environments so it is not something that can be dismissed.


As to the solution for the pre-compilation targets, see my response above (which did not get entered earlier due to a glitch in the way responses to ideas via email was working, or rather not working, for me).  To further illustrate the generated code, if the repeating group had a max size of 5, then the generator might generate something like the following:


     if LAST is valid

          SELECT x FROM y WHERE x IN ( item1, item2, item3, item4, item5 )

     else if LAST - 1 is valid

          SELECT x FROM y WHERE x IN ( item1, item2, item3, item4 )

     else if LAST - 2 is valid

          SELECT x FROM y WHERE x IN ( item1, item2, item3 )

     else if LAST - 3 is valid

          SELECT x FROM y WHERE x IN ( item1, item2 )


          SELECT x FROM y WHERE x = item1

09-02-2014 05:50 PM

My worry would be that you don’t want to select “x = SPACES” but the IN clause is allowing it because you have items in the list that are set to SPACES.  In other words, I want “x=’a’” or “x=’b’” or “x=’c’” and I don’t want “x=SPACES” but because of the way the SELECT statement IN clause is coded, “x=SPACES” will be returned.  Unless there is something in the syntax of the IN clause that states that any parameters that are SPACES are ignored (but that wouldn’t help if SPACES actually is something you want to return) or something.

08-28-2014 06:29 PM

As you know John, I previously requested this on the old forums before they transitioned into this one.  Arguably, if you're asking the community for how to solve the problem, doesn't that open a can of worms as to who owns the intellectual property?


Having said that, your problem is only for those targets that use precompilation - to my knowledge, just DB2 and Oracle.  All other implementations (ODBC, JDBC, MS/SQL, etc.) issue dynamic SQL where the string is built and parameters bound within the generated code.  So for this scenario, your problem is a very straightforward one.


However, even with the precompilation scenario, you have a very straightforward solution, I think.  You know the maximum size of the group view, so that defines your upper bound.  For a group view that is not full, you would pad out the missing members with an item already from the list.  So using the 3 item example, where the group view only contains 2 items:


SELECT x FROM y WHERE x IN ( item1, item2, item1 )


It's redundant, sure, but the optimiser for the DBMS is likely to optimise it away anyway.  And for the scenario where a group view is empty?  It's undefined behaviour, and an application error, not a Gen one.  It's a linear pattern, not an exponential growth.


Summarily, it's essentially:


FOR EACH group view member, bind item #

FOR i = LAST OF group view to MAX OF group view, bind item 1

08-22-2014 02:00 PM

I understand, but I understood that one can code a select statement with any number of parameters in the IN clause, and populate those parms at runtime with valid IDs or spaces.  So, if my group max size is 5, I can code "SELECT x FROM y WHERE x IN (item1, item2, item3, item4, item5)" even if item 4 is spaces and item 5 is spaces.


I've seen this practice in a lot of cobol code. 


When we define code in gen, we can specify the maximum size of any group.  Whether it is 5 or 500, my supposition is that the generator can determine the maximum number of parms to place within the IN clause, and allow the dbms to do the rest.


Am I misunderstanding the technology?

08-22-2014 12:21 PM

What I'm saying is that the READ statement gets generated as a SELECT and you would need a separate SELECT statement for each number of items in the group view.  If you had 3 items, the SELECT statement would be "SELECT x FROM y WHERE x IN (item1, item2, item3)".  If you had 5 items , the SELECT statement would be "SELECT x FROM y WHERE x IN (item1, item2, item3, item4, item5)".


Every possible permutation of the statement would have to be generated for the size of the group view.  Not impossible, and definitely easier with a code generator, but not pretty either.  Unless someone has some other thoughts on how this might be implemented.

08-22-2014 12:05 PM

At design time, we know how big a group view is.  It's the nature of the group view to say how many records it can hold.  I propose that behind the scene, the sql have a place holder for each possible record in the group view.  At run time, the group view is parsed and populates the place holder in the read each.  When the group view is defined as 100 rows, but only 3 are populated, I suppose the remaining 97 will be empty.

08-22-2014 12:00 PM

Basically, instead of listing the values of every item in the group view in the IN clause of a WHERE clause (whether on a READ EACH or anywhere else I would imagine), you would just specify a view within a repeating group view and all the values in that repeating group view at the time of the execution of the statement would be populated in the IN clause.  So, to expand on your example, let's say the customer number in the local_g repeating group view is populated with three rows containing the value 10, 25, and 37.  The statement might look like the following:


     READ EACH customer

          WHERE DESIRED customer number IN (local_g customer number(grpview))


Note that in order to differentiate between the usage of a single item in the group view, the "(grpview)" phrase has been added to the display of the statement for that view.  Another way might be to specify the group view name along with the entity view name, entity type name and attribute view name.


That sound about right?


Since the IN clause requires individual items to be specified explicitly, I'm not sure that this could be done because the item list is not known until runtime but the statement is specified at generation time. Unless you also generated a set of if statements or a switch statement or something for every possible size of the repeating group view as well.


Anyone have any ideas on how this could be implemented?