Coversion of varchar field to number.

building reports crystal reports
1

I am needing to link a number field to a varchar field in Database Expert, for Crystal XI. The SQL works for this (SQL used – table1.fieldX = TO_NUMBER(table2.fieldY). However, I cannot figure out how to get this into the report. Basicly, I am needing to convert the varchar field (“fieldY”) to a number field, so that I can then link it to a current number field (“fieldX”).

Thanks in advance to anyone who can help,

Josh McCormick :us: (BOB member since 2009-06-02)

2

Theres a few ways of doing this;

1 - Create the report from a SQL command (using your code in the join).
2 - Use a subreport and main report, put the conversion in a formula in the subreport and link the subreport on the formula.
3 - Edit the SQL query that Crystal creates and then never touch the Record Selection Formula as they SQL statement and the Record selection formual are mutually exclusive.
4 - Use a view in the database.
5 - Use a command for the second table, convert the field in the command and then join the first table to the command table.

ABILtd :uk: (BOB member since 2006-02-08)

3

Thank-you andyskinner!

I ended up using the 5 suggestion. I was wanting to go the route of 3, but could not figure out how to get into the SQL query.

Thanks again,

Josh McCormick :us: (BOB member since 2009-06-02)