Linking Tables - NOT case sensative 1

[TomSalvato]

Hello experts,

Is there a way to link two tables via one string field ... for example ...

Table_A Table_B
StringFieldA ------> StringFieldB

The problem here is that, while StringFieldA is always uppercase, StringFieldB is often lowercase.
I need the link to act as if they're both upper or lower ... something like:

uppercase(StringFieldA) = uppercase(StringFieldB)

I was thinking it might involve creating a command, but I'm not very strong on my command building. Is there another easier way that I'm missing?

Thanx in advance for any help thrown my way!

-TS

 

[hilfy]

One easy way you could create the command would be to link the tables together like your currently doing and then use the "View SQL" option on the Database menu. From there you can copy the SQL, paste it into the Command Editor and modify it and then delete the tables from the report.

If you do this one thing to be aware of is that you shouldn't use parameters and the Select Expert in the main report to filter your data because Crystal will pull ALL of the data into memory and then filter it there. Instead, create your parameters in the Command Editor and use them in the Where clause of the query to filter the data before it is returned from the database.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[PeterDimitrov]

Case sensitivity is part of the database settings so you can create a command and make it case insensitive.
Another option is to create views based on the tables, which will provide the joined columns in UPPER (or LOWER) case. You will base your report on the views and the data will be coherent.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[TomSalvato]

Thanx so much, Hilfy ... went with your suggestion and created that command. Seems to work nicely.

I noticed that Crystal will not let you use SQL Expression Fields when you do this, though. I know we lose that option when we use multiple data sources, but it seems odd that we would lose it here. Both the tables being used in this command are in the same database, aftet all. Strange, but not a big deal right now as this particular report does not need any SQL Expression Fields. Just something I thought I'd mention in the event that anyone knows the reasoning behind this.

Thanx again!

-TS

 

[hilfy]

You lose it here because Crystal doesn't control the query when you use a command. So it can't add the SQL Expression to the select statement. The way around this is to add the SQL Expressions as "fields" in the select part of the query so they get returned as "fields" in your data.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[TomSalvato]

Ok, I suppose that makes sense. Thanks again for all your help, Hilfy. Your solution seems to be working just fine. Don't know what I would do without this forum!?!?

-TS