Linking databases with different formatting

[kpetree10]

Hello, my issue is that I want to link two databases but the one field that I'd use to link them is formatted differently, it looks like this

Database 1 Database 2
00123456/1 123456
00123457/1 123457

Is there a way to remove the leading zeros and the /1 so I can link these databases?

Thanks in advance!

 

[briangriffin]

You could use a subreport and link on formulas, but if possible I prefer to create a view - probably in database1 in your case - and add an additional column to match the format of the database2 field you want to link to.

The view would look something like this:

select
cast(substring(substring(dbfield, 1, charindex( '/',dbfield) - 1),3,len(dbfield) - 2) as integer),
*
from dbtable

Whatever it takes to get the first field into a matching format, which is dependent upon your data and the database. Then just link the database1 view to the database2 field.

 

[lbass]

Are there always two zeros? And is the "/1" always just that? What type of databases?

-LB

 

[kpetree10]

Yes there are always 2 zeros, up until the point we hit 1000000 orders but that would take a really long time and I haven't seen anything other than the /1 so I'm guessing that doesn't change either.

 

[kpetree10]

Oh, I forgot to answer your other question. They are MSSQL databases.

 

[lbass]

You should try Brian's suggestion. Realized I forgot to ask what the datatype is for the field in database2, also. Another approach would be to convert the field in a command in the from clause, e.g.,

Select table1.field, table2.field
from table1 inner join table2 on
mid(table1.ID,3,6) = table2.ID

Not sure what functions are available to you for the conversion.

-LB

 

[kpetree10]

Both fields are strings. I tried Brian's formula but it didn't work...

select
cast(substring(substring({ShipDtl.SEXTERNALNBR}, 1, charindex('/',{ShipDtl.SEXTERNALNBR})-1),3,len({ShipDtl.SEXTERNALNBR}-2) as integer),
*
from ShipDtl

It said "The keyword 'Case' is missing."

Just for reference the field name for database 1 is sExternalNbr on table ShipDtl and the field for database 2 is ord_no on table oeordhdr_sql.

 

[kpetree10]

Okay so I did it in a sort of weird way but I used two formulas, one I used the Left command to select the left right digits then did another formula to select the right six digits. So now my formula named "Base2 Order Format2" has the order in the correct, six digit, format. Now how can I use this formula to link to my 001 database field ord_no?

 

[lbass]

Brian wasn't suggesting a formula, he was suggesting creating a view. And I was suggesting using a command.

If you are using formulas, you can link to the other database by using a subreport where the formula is used as as a linking field to the corresponding field in the subreport (or vice versa).

-LB

 

[Madawc]

Another option is SQL Commands, using SQL to process your data beyond what is possible with pure Crystal. Mostly these are developed and tested in SQL and then added. Is this an option for you?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP