how to link two table with field with different length

[BigFatCatPinkie]

Need help!!

Try to link two tables from two DB.. with field Health card number

Table A, Health care number field with only the 10 char
Table B, Health card number field could be 10 digits or 12 char, with 2 char version code

how can i link these two tables to make sure i did not lose any clients if they have the 12 char with version code..

Thanks,

 

[njahnavi]

if you can use the command ...
then try to union both the tables with
where conditons..

 

[BigFatCatPinkie]

do u mean by using SQl command?
i wrote a sql script that can pull the table ok.
but i am not sure how/where can i put the sql command in the crystal...

thx..

 

[njahnavi]

YOu have "AddCommand" option in the database expert.

you can do a search on "Add Command" in crystal help to have more idea.

Crystal allows you to get data either By "Add COmmand" or by "By pulling tables" or "By stroed Procedures"...

The point is if you use addcommand then you have to get the whole data needed by "add command" you can not mix two ways of getting data using crystal..

Hope i made some sense..if you feel this explanation is not enough then let me know...

 

[lbass]

In the command, you can link the tables like this:

Select TableA.field1, TableB.field2
From TableA inner join TableB on
TableA.healthcardno = left(tableB.healthcardno,10)
where //etc.

If the command doesn't accept left(), try {fn left()} instead.

-LB

 

[BigFatCatPinkie]

Thanks njahnavi & lbass, i have it figured out finally..
hahha.. and i am so very glad.
I felt like finding an island full of treasure in it.. i never know I could just dump the sql script in the crystal..

Thanks again!!
BigFatCat Pinkie.