Link Number to Text field

[celauritz]

I have two databases. One keeps the account number as a numerical field (1234567). The other keeps it as text, with a leading zero (01234567). Is there any way to link these two together?

 

[synapsevampire]

Probably. Please post your database and Crystal version.

You'll find that omitting basic technical information nets questions, not answers.

You might use a command object in CR 9 or above and CAST/CONVERT the data types in the join, providing it's a SQL type database (as you can see, we need to know this stuff).

Otherwise the better solution, shortly after deboning your dba using a dull, rusty corkscrew, is to build out a View which already does the join for you.

Again, this is dependent on the database(s) involved, but it solves the problem long term.

-k

 

[celauritz]

One is a server side database on an AS/400 that holds our billing system. It contains the numerical fields. If I attempt to SQL query this database, it crashes the billing system. The table is subsmstr and the field is suacct.

SUACCT
1000000
1000008
1000012

The second is a PostgreSQL database that I can easily query. It contains the text fields. The table is dq_customer and the field as rr_acct

RR_ACCT
01000000
01000008
01000012

I am using Crystal XI. I need to link these two fields together to get information in other fields from the tables out of both...

I hope that's enough. I'm not very technical when it comes to what you are asking for. Sorry.

 

[synapsevampire]

We do have PostgreSQL database experts in here on occasion.

I would try creating a command object for the PostgreSQL database side, and use something like:

select cast(rr_acct as int), <field>, <field> from dq_customer

Now join them in Crystal based on the numeric casted field.

If you don't know the SQL syntax, create a report in Crystal with just your PostgreSQL database and the fields required, and then use Database->Show SQL Query to get the SQL and edit it to include a CAST.

Here's a decent page on PostgreSQL:

http://www.sql.org/sql-database/postgresql/FAQ/

-k