Crystal Reports 2011 - 'Like' function issue on Pervasive Database

0
votes

I am working with Crystal Reports 2011 and querying Views in a Pervasive v2011 data engine. I am trying to execute a pretty long select in Crystal Reports on a Pervasive database.

Everything works fine until my last WHERE in my SELECT. I have a parameter and I am trying to find the snippet that is in the parameter in description fields. I try to add wild cards to a LIKE statement on a Description field by correcting the forumla in Select expert.

From there it appears that Crystal is messing things up in a huge way. In the long query when I view the SQL query, it appears it is not coding it correctly at all (Database > Show SQL Query). When I strip the query down as basic as I can, it codes the like correctly but returns nothing. If I copy the code on the big query from Show SQL Query, fix the like statement then paste and execute it into PCC, it returns the results I expect.

Query Portion from Pervasive Generated SQL ("V_PO_LINES"."DESCRIPTION" LIKE '%' OR "V_PO_LINES"."DESCRIPTION" LIKE '%' OR "V_PO_LINES"."DESCRIPTION" LIKE '{?LikeText}')

Query wrote by Crystal in Stripped down and simplified query. SELECT "PO_LINES"."DESCRIPTION" WHERE "PO_LINES"."DESCRIPTION" LIKE '%{?LikeText}%'

SQL snip from long query that works in PCC. ("V_PO_LINES"."DESCRIPTION" LIKE '%{?LikeText}%')

Select Statement Crystal Select Expert. {V_PO_LINES.DATE_LAST_RECEIVED} <> Date (not closed) and {V_PO_LINES.DATE_DUE_LINE} = {?Date Range} and {V_PO_LINES.VENDOR} = {?Vendor} and {V_PO_LINES.DESCRIPTION} like '%' + {?LikeText} + '%'

I am on 2011 Version 14.04.738 RTM

Is anyone else running into this? Is there a fix or workaround?

1
crystal-reportspervasive
Can you paste the relevant snippet from the record selection formula in Crystal? And what do you mean by "...strip the query down as basic as I can"? – Ryan
I will edit the post based on what is in the select statement of Crystal. I will change things up so they are generic. What I mean by making it as basic as I can is that I am just doing a like query on a single field in a single view. The actual long query links 3 views and several select statements. It all works 100% correctly until I add a like. – user3215616
I changed a couple thinks in the select expert statement so they made sense. Not Closed = a value in the system for the field when an item is not closed. – user3215616

1 Answers

1
votes

If your {?LikeText} parameter accepts multiple values, that will cause Crystal to treat the '+' sign as an add-element-to-array operator. So doing '%' + {?LikeText} + '%' is really creating an array of strings and not a single concatenated string:

[ '%', 'WhatYouEnteredInTheParameter', '%' ]

which results in Crystal parsing this into logical-ORs in the SQL. To fix this, go into your parameter and set "Allow Multiple Values?" to "No".

I'd also suggest that you try using '*' instead of '%' which is Crystal's wildcard.