SQL Convert(varchar, <fieldname>) equivalent in Crystal Reports record selection formula

Question

I have a sql query where clause that is as below:

WHERE ("Table1"."count"<>"Table1"."onhand"
       OR "Table2"."batch_code" IS NOT NULL        
       OR ("Table1"."BatchMinVariance"<>0 OR "Table1"."BatchMaxVariance"<>0)
       )

I am setting record selection on the fly in my code using crystal equivalent for the above:

({Table1.count} <> {Table1.onhand}     
           or (not isnull({Table2.batch_code}) 
           or ({Table1.BatchMinVariance} <> 0 OR {Table1.BatchMaxVariance} <> 0) )

This works absolutely fine and crystal handles it displaying correct results.

Requirement is to add another condition to the WHERE clause as below: Line 4 is new condition.

    WHERE 
    ("Table1"."count"<>"Table1"."onhand"
           OR "Table2"."batch_code" IS NOT NULL            
           OR ("Table1"."BatchMinVariance"<>0 OR "Table1"."BatchMaxVariance"<>0)
           OR (CONVERT(varchar, "Table3".in_snapshot_yn) + CONVERT(varchar, "Table3".counted_yn) IN ('01', '10'))
   )

NOTE condition 4 is new condition, I have used sql CONVERT function because those are boolean fields and so that only those rows are shown that have values 01 or 10. The sql query gives me expected results, but I am in a fix to find the Crystal equivalent of this condition. For condition 4, I tried:

OR (TOTEXT(TONumber({Table3.in_snapshot_yn}), 0)&""&TOTEXT(TONumber({STAKE_SERIALNO.counted_yn}), 0) IN ("01, 11") <> true) 

I have tested this in the report itself to see whether crystal likes or not and crystal wont complain. But when I put it in my code as condition 4, wont like it. Gives an lame error that bracket ) is missing. I know that this not a bracket issue and definitely something to do with the condition.

Complete CR equivalent condition looks like below:

    ({TABLE1.count} <> {TABLE1.onhand} 
OR (not isnull({Table2.batch_code}) 
OR ({TABLE1.BatchMinVariance} <> 0 OR {TABLE1.BatchMaxVariance} <> 0) )
OR NOT (TOTEXT(TONumber({Table3.in_snapshot_yn}), 0)&""&TOTEXT(TONumber({Table3.counted_yn}), 0) IN ("01, 11")) )

Any hints would be appreciated. I am using SQL Server 2014 and CR 13.

Answer 1

I found it. There was a problem with the "double quotes" on my new condition.

TOTEXT(TONumber({Table3.in_snapshot_yn}), 0)&""&TOTEXT(TONumber({Table3.counted_yn}), 0) IN ("01, 11")

As soon as I change them to Single quotes, it just worked fine. I dont know why it would do this. The record selection as I said is done well out of the report so I dont have a huge dataset in Crystal.

Please see complete crystal equivalent record selection below.

({TABLE1.count} <> {TABLE1.onhand} 
OR (not isnull({Table2.batch_code}) 
OR ({TABLE1.BatchMinVariance} <> 0 OR {TABLE1.BatchMaxVariance} <> 0) )
OR NOT (TOTEXT(TONumber({Table3.in_snapshot_yn}), 0)&''&TOTEXT(TONumber({Table3.counted_yn}), 0) IN ('01, 11')) )

Answer ends here.

For user @Lan.. Just in case you need to know how I am doing this in c#, I have pasted the code below.

var recordSelection = new List<string>();
recordSelection.Add(<crystal equivalent where clause as above>);
reportDocument.RecordSelectionFormula(recordSelection);
// FYI reportDocument is initialised like this ReportDocument reportDocument;