How to use {Prop:Filter} in Prop:sql

questions
1

I have filter returned by BRW1::View:Browse{Prop:Filter} that is standard filter string and uses clarion prefix and field name like
ASV:ACCOUNTSYSREF = 742 AND ASV:CompSysRef = GLO:FilterComp

Is there any easiest way to use it in prop:sql ??

SQLCommand{PROP:SQL} = 'select SysRef, LocAmt, ForAmt FROM dbo.MyTable WHERE ' & 
BRW1::View:Browse{Prop:Filter}

LOOP
        NEXT(SQLCommand)
....

I suppose that BRW1::View:Browse{Prop:Filter} should be “converted” somehow to be valid in prop:sql

2

Something to consider (from the docs):
“With the SQL statement, you can now use native SQL code as filter criteria from within the IDE template interface. Example: In the Record Filter prompt, enter SQL(‘your SQL filter code’).”

IMO, using SQL in the filter while allowing the browse to specify select fields is the easiest way to go.

3

Douglas, I know this.

My question is this: since I have already filter expression (form prop:filter) can I use it in prop:sql statement?

4

I would say the answer is no. I’m not aware of any automatic method.

5

If you need the expression in the runtime you can read the {prop:Sql} from the primary file of the view after you did your first Next(view) command. Alternatively you could read it through the SQL Callback interface or using the Trace utility. Converting the filter to SQL without actually retrieving the data from the view via next/previous (view) is a no-go as far as I know.

6

Yes!
using the FCB.SQLCallBackInterface.ExecutingCode you can grab whatever is being sent to the server. You then have to parse out what you want. i.e. look for WHERE and grab the gererated SQL from there.

1 Like
7

Thank to all.

The easiest way was to create View in Clarion and then set the existing filter using MyView{Prop:Filter}. Then process my view as a normal file with loop/next.

8

I write a lot of SQL and I almost never use the {PROP:Filter} property. For one thing, your filter is using the Clarion prefix and a direct reference to a variable that SQL knows nothing about. If is much easier to define a SQLCmd (CSTRING(512)) variable and set it to the correct SQL command. In your example: SQLCmd = ‘SELECT SysRef, LocAmt, ForAmt FROM dbo.MyTable WHERE AccountSysRef = 742 AND CompSysRef = ‘’’ & GLO:FilterComp & ‘’’’. This will return just the record set that you are interested in and no further filtering is necessary. I usually do all of my Query development in sqldeveloper or the MSSQL console and then transfer them to Clarion, substituting variables as necessary.

1 Like
9

Noticing the inclusion of GLO:FilterComp, but assuming one is instead using a variable local to the procedure, I am wondering what is the absolute last ABC method in which SqlCmd can be set before the query is sent to the backend?