Appending a SQL Where Clause to the Select Statement

[DBohner-(db)]

My turn to plea for guidence....

Is there a way to append a where clause to the end of a select statement for an application?

Ideally – what I’m looking for is a way to append data selection to the end of an Application’s Select statement – similar to the same way that Row Security is appended to the end of an application (or UBE)’s select statement.

Without security
Select * from file where QBE is true

With Security
Select * from file where (QBE is true) and (Security clause is true)

The append SQL functions inside the application do not nest the selection criteria - the OR statement voids everything on either side of the OR... I need to be able to NEST a complex OR statement inside brackets - like what Row Security appends.

What I need – is a way to nest an additional Where Claus to the statement, the same way security does – maintaining the original content of the select <font color="red">)AND( </font> the appended selection criteria of a client’s choice.

HELP?

(db)
------------------------------------------
Daniel Bohner
ExistingLight, LLC
http://www.existinglight.net
[email protected]
[email protected]
Home Office: 208.467.3316
mobile: 208.250.1917
fax: 208.246.3425
------------------------------------------
XBOX Gamer-Tag: drbohner
MSN IM: [email protected]
Yahoo IM: [email protected]
------------------------------------------

 

[cbruce]

Have you tried using the ER System functions:

1. Grid | Set Selection Append Flag (set to Yes)
2. Grid | Set Selection (provide additional criteria)

I realize that the Set Selection does not support parenthesis and nested OR clauses. It's a bit of a hack, but you could try a distibutive expansion: i.e.

if you need A & (B | C),
then code it as A & B | A & C

The hack is that you must duplicate the A part of the clause.

As a sidenote, when I need to do more complex data selection, I use a concept similar to the Data Selection Business function B9600430. It handles parenthesis and OR clauses great. Unfortunately, if you go this route, you pretty much have to abandon the JDE grid SQL logic and do everything manually -- but it does work.

Cheers,

 

[DBohner-(db)]

cbruce,

That is the closest anyone has been able to get me - to a way to manipulate the Select statement - THANK YOU.

Do you know where I find some good examples of how the function works (P98220 has very little)...

At this client, they've requested to be able to turn on/off Row Security at the switch of a processing option. We can turn it on/off using Application Exclusive - but we cannot 'just turn it on/off' based on the processing option (yet). I'm guessing I could put all the logic into a NER - and create the statement in long-hand...

Thanks for your most excellent assist!

I'm ready for more suggestions, while I work on this one... if there are any takers.

(db)

 

[peter_hamilton]

Here's another hack, to get around what they want.
The easiest way to dynamically turn off row security is to remove the row security.
At the start of the app, check PO to switch off row security.
If PO for Row security is off, then move any existing row security lines for this user/group/app to a new file (same fields).
If PO for Row security is on, the move the user/group/app details from the backup file (above) back into row security.

The only problem I can see using the above is:
1). Are row security details loaded into cache? If so, then once they are loaded, you have to log-off to unload.
2). If you can't specify version for the app, then the above it not really going to work, as if two users in the same group are accessing the same app (but different versions), then the above can't work.

The only other way around the problem, is to NOT use the tool to create the sql statement for the form. I.e. You need to create the SQL statement and have er to read the records and load the grid. Doing it this way bypasses row security completely.
So you could have the form loading records using the tool when row security is required, and have the form loaded manually when row security is to be bypassed.

 

[Zoltan_Gyimesi]

Hi Daniel,

You can code your security in Grid Record Is Fetched event and issue a SuppressGridLine systemfuntion call, when security is ON.

However this solution has performance disadvantage, when large number of record will be excluded, because all excluded record will be fetched.

Regards,

Zoltán

 

[DBohner-(db)]

Thanks Zoltan -

The reason I'm looking for a way to append a clause to the select statement is... the performance issues called by suspending the grid row....

Users will like to test wether the mod is working, so of course they will QBE for something they can't see - all three zillion records will be suspended... A FAT client will appear to hang and an HTML client will get popup messages after each 2050 records have been read...

We're stuck on the Suspend Grid Row, for now.

(db)

 

[DBohner-(db)]

Re: RE: Appending a SQL Where Clause to the Select Statement

Peter,

These ideas will be kept for round two - I'm not hungry enough to feed that much effort into modding an application of this size.

A question though - can you clarify how you turn on/off row security based on your processing option?

(db)

 

[peter_hamilton]

Right,
so you want QBE on your selection as well.
The problem you face, is even if inside the application you got extra (,) involved with your sql, it still won't work, as the security AND (x=3Dy) will be appended to the end of the generated sql statement.

It doesn't solve 100% of your QBE problem, but you can interrogate the values entered into he QBE fields, and use these values when you build your own (manual) sql statement and read and populate the grid.
And when the PO is off, add a user selection so that no records should ever be selected, and in last grid record is read (or post find button) write your own routine to manually select and write grid records to the screen. The problem you face doing this way, is you will load all the records selected. You could try and add your own load screen at a time logic, but it gets even more messier.

The only other way of trying this, is not to use security for this app, but to create your own security table for it. That way, it is up to you, on how the security is appended to the selection. And this can easily be done inside the app.

Cheers,

Peter.