SQL Select for ABC Browse Conditional Record Filter

questions
1

Hi,

C11 EE ABC, MSSQL

I’m new to using SQL in Clarion.
In the Conditional Browse Behavior i want to set the Record Filter with a SQL Select.
The select searches for the occurence of a user typed text in some fields of the file showing.
The Clarion search is very slow, when i do the Select on the backend it is much faster.

What is the proper syntax to add the Select in the record filter of the browse?
I tried a lot but get all kind of Bind errors, although all fields i use are binded and the file itself also of course.

Thanks in advance for helping!
Rob

2

Take a look at prop:SQLFilter

In my app I have something like this:

BRW1.ApplyFilter PROCEDURE
  CODE
  PARENT.ApplyFilter
  ! [Priority 7500]
      if TMP:FilterFlagHighLighted                                            
          BRW1::View:Browse{prop:SQLFilter} = '+a.INPROHLRSysRef <> 0'
      ELSE
          BRW1::View:Browse{prop:SQLFilter} = '+0=0'
      .

so I have TMP:FilterFlagHighLighted check box on my screen and depends on its value I filter my browse.

+ means that your new filter will be added to the existing one (so you can have some filter in Template settings as usual)

3

My first suggestion would be to use Trace and take a look at the select statement Clarion is generating for your browse. Most of the time, it will be the same as one would use directly. However, if not, understanding what causes the difference is very useful learning.

If you find it necessary to enter an SQL select yourself, check the docs for the SQL() statement. It has replaced PROP:SqlFilter and is now preferred.

4

Hi Douglas,

Thanks for your reply.
Yes i would like to enter a SQL Select myself, i checked the docs for the SQL() statement.
But where to place the SQL(‘Select …’) and how to incooperate it with the ABC browse and the conditional Tabs behavior.

5

Just put it in the filter for the browse box, like:

image

Notice that you don’t need to double the quotes. What you have in the SQL() brackets needs to be things that the server knows about, which does not include the contents of fields on the clarion side. But you can be tricky and have both SQL() sections and not SQL() sections where you use clarion fields that are bound.

You are likely to have trouble starting off, so in the window init after opening the files, put something like:
dfield{PROP:Profile} = ‘c:\temp\sqlf.log’
dfield{PROP:LogSQL} = 1
dfield{PROP:Details} = 1

so you can see what gets sent to the back end. For a simple single file it will look like:

SELECT A.TABLE_GUID, A.PARENT_FIELD_GUID, A.FIELD_NAME, A.GUID, A.DATATYPE, A.DATA_SIZE, A.PLACES, A.FIELD_ORDER, A.DCT_NAME FROM DFIELD A WHERE ( field_name like ‘CREATE%’ ) ORDER BY A.DCT_NAME, A.GUID.

Note that Clarion has aliased the table name, so if you had dfield.field_name in your SQL() expression it would cause an error. You have control (can;t remember how at the moment) over the prefixes that the driver will use, so you don;t have to remember which is a, which is b, etc. when you have more complicated expressions. Also note that what you have in the SQL brackets gets sent entirely as is.

2 Likes
6

I always put browse filter statements in the ApplyFilter before parent embed.
This includes both SQL() filters and Clarion filter statements. For instance, for something like all names added after some date with a bitwise status the embed could look something like:

! FILTER:1, FILTER:2 are equates
! 2=Pending status and normally would be defined as an equate also
NameTable{PROP:Alias} = ‘n’
SELF.SetFilter(‘Nm:CreateDate>’ & Loc:Date,FILTER:1)
SELF.SetFilter(‘SQL(n.statusbits & 2 <> 0)’,FILTER:2)

HTH. What backend are you using?

1 Like
7

Don’t get too hung up on the entire SQL SELECT statement. Clarion actually produces pretty good SQL mostly. The filter of the view, as per JonW just gets tacked on to the WHERE clause of the generated SQL.
You may want to use a variable to construct the filter using an SQL() bit because it’s difficult to append variable stuff inside the SQL(). ie
MyVar = ‘SQL( fieldname like ‘’’ & Clip(MySearch) & ‘%’’ )’
and in the Record Filter of the browse put !MyVar
The ! says its a variable
You may need to put A.fieldname because Clarion uses A to Z as table aliases

2 Likes
8

Agree completely. When one sees a speed difference between a Clarion query and what is assumed to be the same in SQL, your point is exactly why using trace to see the Clarion query will easily explain the difference most of the time.