SQL Expression UCase() 1

[bobhagan]

I did a SQL Expression fn UCase({staff.staff_name})
and used it on an Oracle table:

{%staff_nm} startswith UPPERCASE(?staff_nm)

What's very strange is that it always fails the first time - whether using mixed, upper or lower case - and always works on any subsequent run of the report. If you close the report file and start it over, the behavior repeats.
Why is this?

Thanks

Bob Hagan

 

[Reebo99]

In the report, goto Database > verify database. then save your report. This might help.......

Reebo
Scotland (Sunny with a Smile)

 

[bobhagan]

I'll give it a try, but why would this make a difference?


Bob Hagan

 

[Reebo99]

If you've changed the underlying datasource then you need to verify the database. Under File > report options you'll see a Verify on First Refresh.

I've encountered problems of this sort, and it was due to the report doing a verify on the first refresh, but it returns a 'report now ok', but doesn't show the data. then when you refresh the second time it has already verified the database and 'fixed' anything, so the data shows ok.

Reebo
Scotland (Sunny with a Smile)

 

[kenhamady]

I would be curious to see if this happens on other SQL expressions in this report, and also if this happens if you use this SQL expression in a new report created from scratch.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[bobhagan]

We had created an UPPER(staff_name) index in Oracle, but it gave the staff module in the interface fits and we changed it to just staff_name. That's the exent of the changes to the database. I turned on verify on every print (I'm using CR 8.0), but it made no difference.

This was a modification to an old report, but when I began to have trouble, I created a new report with just this selection piece. No difference.

This is the first time I've used SQL Expressions, because before this I worked for a group that used Foxpro, and I did all the querying there and just passed the result to Crystal. I'm stumped.

Bob Hagan

 

[kenhamady]

How, exactly, does it fail the first time? Error message? No Records?

Setting "verify on every print" isn't exactly the same as Verifying the Database. To verify the database use Database - Verify Database. If it says the database is up to date, then the verify isn't the issue. If it says the table has changed, then this might be teh issue.

Another thing is to check the SQL expression generated by CR and see if it is different the first time vs the second time. If not, this might be a database or ODBC problem.

Another test to isolate the problem would be to try this expression with a different table and field. If it works on another field, but not this one, I would suspect a database problem.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[bobhagan]

It returns no records. I've verifed the database as well - Its up to date.

As I mentioned, I've never used SQL Expressions before; how do you determine what's getting generated by CR. Trying the expression on another table sounds like a good idea.

Thanks
Bob Hagan

 

[kenhamady]

Go to Database - Show SQL to see the statement generated by CR.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[bobhagan]

I sort of gave up on this, but the SQL is:

SELECT STAFF."STAFF_NAME",STAFF."STAFF_CODE, {fn UCASE(STAFF."STAFF_NAME&quot}
FROM
"FACTORS"."STAFF" STAFF
WHERE
({fn UCASE(STAFF."STAFF_NAME&quot}) LIKE 'BLAH%'
ORDER BY STAFF."STAFF_NAME" ASC

Bob Hagan

 

[kenhamady]

Does it change from first time to second time?
Did you try it on another table?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

I'd try a few things here:

Make sure that if you're using the ODBC, you use the Crystal supplied ODBC driver.

Apply the service packs/hot fixes.

Use the real Oracle syntax, not the ODBC conversion syntax in the SQL Expression:

UPPER(STAFF."STAFF_NAME&quot

And how did you construct this SQL?

It's porbably easiest to use Report->Edit Selection
Formula->Record and place something like:

{%SQLEXPRESSION1} LIKE 'BLAH%'

Crystal will create the SQL for you.

-k

 

[bobhagan]

Use the real Oracle syntax, not the ODBC conversion syntax in the SQL Expression:

UPPER(STAFF."STAFF_NAME&quot

I finally got around to looking at this again. This works perfectly. Thanks much.

Bob Hagan