error in translation of selection formula into where clause

[cpagnani]

Hi folks,

I run a report (v8) against Oracle8 db, by a VB6 form, using Crystal OCX, setting inside selection formula
{view.number_field} = 2001

When running in VB6 IDE it translates to
view.number_field = 2001, (with final comma)
giving Oracle error SQL command not properly ended.

When running in VB6 EXE it translates to
view.number_field = 2001.0
and it works.

The field is of Oracle type NUMBER.
I'm in Italy so decimal separator is comma.
Both VB and CR are localized versions.

Can anyone help me to understand this behaviour.

Thanks
Bye

Claudio

 

[kenhamady]

That is odd. It probably is coming from the regional settings of the PC, but it isn't happening corretly, since you aren't getting the decimals. Even if you format the field field within the report, and putting the decimal back to period for this one field, but I am not sure this will affect the SQL.

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[cpagnani]

I made a little mistake in both case there's NO 0 after decimal point or comma, but decimal point followed by nothing is good SQL for Oracle.

Ken you're right, I change regional settings to U.S. and all is good.
I think it's a bug in CR: regional settings can't affect SQL syntax.
Unfortunately I can't report a bug to Crystal Decisions for free and self-serve support doesn't provide any KB article or patch, so I left only the workaround.