Uppercase Lowercase issue on JOIN 1

[GCL2007]

Using CR XI and Oracle database, am trying to set up a report where I need to join two tables in Database Expert. The problem I am having is there isn't an ID field or any other field that I can link the tables with. I am forced to use a description field to link on. This would be fine, however in one table, the descriptions are converted to upper case and in the other table they are mixed case. Am wondering if I can do a simple join, like a left outer join between the two tables on that field somehow. Other than the case, the data should be linkable. Any thoughts?

 

[IanWaterman]

Oracle is annoying like that.

I do not have Crystal on this PC, but I think if you go to File Options you can set Case insensitive. Not sure if this applies to Report or databases connections too.

Failing that you will need to build a command, and do the join there

in your join statement use

upper(table1.fieldname1) = upper(table2.fieldname2)

If you are not familiar with SQL add all the fields you require for report as is then open Database -> Show SQL.
Do not design report as you will have to discard.

Copy the SQL and then open new report and select Add Command from Database expert. In the SQL where you see the join to the two tables use UPPER () as described above.

Ian

 

[GCL2007]

Thank you Ian... I believe the SQL command might just work for me.. Thank you very much...