Using SQL filters in browses

Using SQL filters in browses is something we all need to do from time to time.  It is actually very simple to do, but it needs a few lines of code to make it easy.  By default the tables in a Clarion view use alpabetical characters as aliases.  If you have a view with two tables in it, they will be referred to as A and B by the SQL code generated by the SQL driver that you are using.  By using PROP:Alias before the view is opened you can determine what alias is used.  For example:

Transactions {PROP:Alias} = 'TRN'  
Cusip        {PROP:Alias} = 'CUS'  
Products     {PROP:Alias} = 'PRO'

This means you can refer to columns in the Transactions table as 'TRN.' in your SQL filter.  The alias must be set before the VIEW structure is opened.  I normally do it in ThisWindow.Init, before the files are opened.  Constructing the SQL filter can be SQL dialect dependent, so I'm not going to dig into that here, but below is code that I use to filter a browse I'm working on right now. 

ResetBrowseFilter         ROUTINE  
DATA
SQL  CSTRING(4097)  
CODE    
  SQL = '' !! Construct the filter here    
  !! Use either this method:
  TradeBrowse.View {PROP:SQLFilter} = SQL
  !! Or this one:
  TradeBrowse.SetFilter('SQL(' & SQL & ')')  
  TradeBrowse.ResetFromView

That's it!  The PROP:SQLFilter can be used directly on any view in both ABC and Legacy applications.  The documentation used to claim that PROP:SQLFilter is deprecated, but the documentation for Clarion 8 only states that it should be replaced with the more versatile SQL() function.  PROP:SQLFilter was deprecated more than 10 years ago and seems now to be un-deprecated.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk