Using Mid Function within Record Selection

[barney1993]

I am using CR developer 11 and an Oracle datasource.
I am trying to restrict data returned by using the mid function on a database field from within the record selection formula field, although when I preview the SQL query, the condition is not displaying in the WHERE clause.

extract from record selection:

{GLF_LDG_ACC_TRANS_AR.LDG_NAME} ='AR' and
mid({GLF_LDG_ACC_TRANS_GL.ACCNBRI},3,3) = '032'

 

[barney1993]

I have solved this problem, it seems that the data is still restricted on the report, although when I preview the SQL query it is not reflected.

 

[Madawc]

That's normal in Crystal, not all report selection commands get translated into SQL. This also means that the work will be done by your machine rather than the server holding the data.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

barney1993,

If you are not seeing the clause in your SQL query, it means it is being processed locally--and therefore slowing your report. It would be more efficient if you created a SQL expression and then referenced that in your selection formula. Depending upon your datasource, you should be able to use one of the following (See what is in your function list):

{fn substring(`GLF_LDG_ACC_TRANS_GL`.`ACCNBRI`,3,3)}


substr(`GLF_LDG_ACC_TRANS_GL`.`ACCNBRI`,3,3)

Select the field from the field list, so that the correct punctuation for your datasource is used in the SQL expression. Let's say you named this SQL expression {%mid}. Then in your record selection formula you would use:

{%mid} = '032'

You should then see this criterion in your "show SQL query".

-LB