SDO, complicated query/filtering

You did not mention your release of Progress/OpenEdge.

I recommend moving towards a temp-table SDO. Works really nice except for batching and you have full freedom of which data to put into.

- i can prolly add another layer for prodataset between the SDO and database, but this seems like a big change to me.

That's also a very nice option! Using the DataView as an alternative to SDOs and OERA style business entities with ProDatasets on the backend.

http://communities.progress.com/pcom/docs/DOC-36518

http://communities.progress.com/pcom/docs/DOC-36528

http://communities.progress.com/pcom/docs/DOC-36538

But you are right - it's a big change. But it offers a bunch of new options.

HI Jan,

  manipulate a SDO associated to a  viewer and/or a browser is possible thanks to some dynamic function.

To see how use these function you can see under OpenEdge Help looking for SDO procedures such OpenQuery, SetQueryWhere, CloseQuery and so forth; anyway herein i give you some samples.

Procedure InitialiseObject.

  define variable hSDO as handle no-undo.

  hSDO = h_.

  DYNAMIC-FUNCTION('closeQuery':U IN hSDO).

  DYNAMIC-FUNCTION('setQueryWhere':U IN hSDO, INPUT "field = " + STRING(value) ).

  DYNAMIC-FUNCTION('openQuery':U IN hSDO).

Afterthat you can open the browser linked to this SDO.

Hope this could help

Cheers,

   Alex

Hi Alex,

at this moment iam not on my working computer, so far away from manual, whitepapers etc, but SetQueryWhere doesnt seem to solve my problem from my point of view, because this way i will only reposition query to specified value, but this wont allow me to actually show only few records, which dont have any field i could use to mark. Althought i was thinking, that i could add an calculated field, which i would populate with data from my ID temp-table (basicly it would be only a flag field SHOW/DONT SHOW) and then reopen query only on these records. But this solution still forces me to first open query on all the records (really lot of them, its payment headers) and then filter this, which in my opinion would let into bad performance. I also found some work-around solution like this, but as i said iam using only a small fragment of records from the table (much less then 1%), so this isnt the right for me i fear:-/.

But maybe iam getting You wrong, ill try to think about this more in monday when iam back in the place.

Thanks for answering and helping!

And have a nice week-end.

Jan

Hello Mike,

our release should be 10.2B, iam not 100% sure tbh, as i tend to forgot versions and atm iam not in work.

Thanks for advice, i will probbably remake it as temp-table SDO, seems pretty nice for me, that i can control the situation more.

Also i came with a solution of making the header SDO over two tables a temp-table and the actual header table. In normal situation (temp-table not needed) i would remake the query, so the temp-table is non-important. In my situation, i will remake the query to "FOR EACH temp-table, EACH header of temp-table", so it will filter my records in 1:1 relationship as i want. SDO is always made on two tables, only the importance and order of the tables changes. Sadly i havent been to work today, so i will try to bend it this way in monday:-).

Thanks for tip, answering and the links, my colleague is already telling me to this change and i probbably will in few months, its just the lack of time for big re-work.

Have a nice week-end

Jan

So i just figured the solution (thanks to replyes).

I made a brand new SDO over the header table joined with OUTER-JOIN to the temp-table i use to filter records.

When i start the SDO it behaves just like the old SDO and works only on header table.

When i need the new "special" functionality, i change the query using DYNAMIC-FUNCTION('setOpenQuery', INPUT modifiedQuery) and remove the OUTER-JOIN, now query will be opened only on headers, which IDs i have in my temp-table. If i need to switch back to normal mode (uprobabbly in my aplication) i just go back to the original query. Only little drawback is the time consuming query-changing, but maybe ill be able to make it faster in time.

So hope this helps if anybody has similar problem to mine and thanks for help.

Jan

Some issues to be aware of:

• You should avoid setOpenQuery() and use setBaseQuery() instead. They should give the same behavior, but setOpenQuery() has some serious overhead (for backwards compatibilty) when running on a client that uses an AppServer.
• It is not recommended to change the BaseQuery (with setOpenQuery() or setBaseQuery()) after the SDO is initialized.    

If you are on 10.2A the following outer join support could possibly simplify your case:

• assignQuerySelection() removes the OUTER-JOIN for a specific buffer when the first field expression is added to a buffer:
• removeQuerySelection() adds the OUTER-JOIN back when the last field expression is removed from a buffer
• This behavior only works if you have OUTER-JOIN in the BaseQuery.

There are also methods to manipulate the OUTER-JOIN of the QueryString or any query directly

• assignBufferOuterJoin() and removeBufferOuterJoin() allows you to assign and remove the OUTER-JOIN for the specified buffer in the current query or the optionally specified query. This allows you to add and remove OUTER-JOIN in the case you do not have it in the BaseQuery.

Support functions

• bufferHasOuterJoin()returns true if the specified buffer in the current query or optionally specified query has OUTER-JOIN.
• bufferHasOuterJoinDefault() returns true if the specified buffer in the design query (BaseQuery in SDOs) has OUTER-JOIN.