We have a view and a table that we need to join on license number. Unfortunately for historic reasons (possible insanity as well) they both store the license differently.
The view stores them as a varchar like so.
LLG1103
LLG1104
The table stores them as a number like so.
1103
1104
Is it possible to join them in the model? Or will there need to be some tricky manipulation to join them in the reports they would be used in?
The work around that I came up with is to create a hidden (to the user anyway) query calculation in the view that uses the ltrim function to trip off the leading text and just leave the number, which is then put into the to_number function. Like so.
to_number(ltrim ([license], 'LLG'))
I then used the result of that query calculation to join to my table.
Have I just shot myself in the foot of is that a valid solution?
that is definitely a valid solution, AFAIK........
Is there any performance concern?
If it is indexed or m. view and you rely on an index the string manipulation would disable the index ...
- Alp
Well the query calculation is a new field in the model. The existing items in the view are untouched. So I'm not sure how that would effect indexing or performance. Seems to be working fine though. Which means I have yet to find anything wrong with it.... not necessarily that there isn't anything wrong with it.
Quote from: bavis on 03 Aug 2010 12:26:11 PM
We have a view and a table that we need to join on license number. Unfortunately for historic reasons (possible insanity as well) they both store the license differently.
The view stores them as a varchar like so.
LLG1103
LLG1104
The table stores them as a number like so.
1103
1104
Is it possible to join them in the model? Or will there need to be some tricky manipulation to join them in the reports they would be used in?
When you are creating the relationship between these two objects in FM, you can choose to set up the join as an expression rather than a straight link. This will allow you to include the relevant syntax to extract the numeric digits from the character item and convert them to a numeric datatype as part of the join, and will remove the need for an embedded query calculation.
Regards,
MF.