Linking two fields of different types 1

[DJWheezyWeez]

I'm using CR XI.

In one database, the Invoice number is a number. In the other database, the Invoice number is a string. What I would imagine doing is creating a new field in the first database converting Invoice number to a string and using that new table in the report instead of the original one. Is this possible? Or is there another way to get the results I want?

-DJWW

 

[fisheromacse]

Off the top of my head, so i apologize in advance for any stupid mistakes, but i believe you could create a formula and then use that to join the tables.

ie:
//InvToNumber
IF isnumeric({Table1.StringInvoice})
then tonumber({Table1.StringInvoice})
else 0


and then in the select expert, formula editor:
{Table2.NumberInvoice}={@InvToNumber}

 

[DJWheezyWeez]

Thanks. That works great. The only issue I have now besides running time is the join. Instead of =, what symbol would I use if I wanted to do a join from Table1 to Table2?

 

[IanWaterman]

Would be better to use a command and bring back the data set you want.

The reason your report is running slowly every record is being brought back to Crystal and then doing final join locally.

Select * from
Table1
left outer join table1.number = convert(number, table2.numberstring)

or if oracle

Select * from
Table1
left outer join table1.number = tonumber( table2.numberstring)

You can replace * with specific fields you want from each table.

Ian

 

[DJWheezyWeez]

That makes sense to me and I understand what the command is trying to do but I'm very new to SQL and have never actually used it before. This is what I'm trying to use:

Select
PV_Bill.'BillNum',
RM30101.'DOCNUMBR'
from
PV_Bill LEFT OUTER JOIN PV_Bill.'BillNum' = CONVERT(INT, RM30101.'DOCNUMBR')

I'm getting an error on Line 5 near 'BillNum'. I know this is probably horribly wrong but I'm hoping you can help me correct it or tell me why it's not working. Thanks in advance.

-DJWW

 

[IanWaterman]

Try

Select
PV_Bill.'BillNum',
RM30101.'DOCNUMBR'
from
PV_Bill
LEFT OUTER JOIN RM30101 on PV_Bill.'BillNum' = CONVERT(INT, RM30101.'DOCNUMBR')

Ian

 

[DJWheezyWeez]

I'm not sure which database I'm supposed to be creating the command in but I tried both and got errors in each.

PV_Bill tables' database's error: Syntax error in SQL statement at or about "'BillNum", RM30101.'DOCNUMBR' from PV".

RM30101 tables' database's error: Line 2: Incorrect syntax near 'BillNum'.

 

[IanWaterman]

Sorry

Missed the point that you were in two databases.
You will need to get your DBA to provided a link from DB to the other and then explicitly sate link in your command

Would be something like

Select
PV_Bill.'BillNum',
rm3.'DOCNUMBR'
from
PV_Bill
LEFT OUTER JOIN Otherdb.dbo.RM30101 rm3 on PV_Bill.'BillNum' = CONVERT(INT, rm3.'DOCNUMBR')

Ian

 

[lbass]

Another approach to this would be to add one of the tables in a subreport and then link the subreport to the main report by linking a formula converting the field to the same datatype to the other field. Note that a subreport behaves as if it is left joined, so the table that contains all the records should be used for the main report.

-LB

 

[DJWheezyWeez]

Thank you both for the help. Since I'm not great with SQL, I ended up going with LB's suggestion because I couldn't get my SQL syntax right.

-DJWW