I have two aggregate: Master 1 & Person Master
So the field of Master 1 has column Person ID which can be any digit integer upto 5 length and whose reference we have in Person Master.
Here is example:
12345 is Person ID in Master 1 Table & Person Name is mentioned in Person Master along with Person ID, till now solution is simple, but the organization requirement is such that last two digit of id from 12345 i.e 45 can also be registered to the member. Meaning '4'5 Refers to John and 12345 also refers to John.
So the query must return John when we join the statement with id 12345 and if 12345 is not found than 45 should be used to find name because it is not compulsory for our sister company that they use same code standards & in their case 23145 may be registered to David so if you make a join with only last two digit than it will return John ,but it should be David.
-> First Data should find 5 digit ID and if Name is found using 5 Digit ID than it should display the name.
-> If Name is not able to find using 5 digit ID than it should use some kind of Substr(IntegerToText()) action and find the name using last two digit.
Can anyone share the solution to this problem I am not able to find a solution to the problem.
Hi,
So to achieve this you can use filter condition in the aggregate something like below :
Person.Id = "12345" or Person.Id = Substr("12345",Length("12345")-2,Length("12345"))
so basically first condition check for complete string and second condition check only for last 2 digit of the String. If any match it will return the result.
regards
Thanks for the solution though Person ID was in integer so used IntegerToText() to get the Substring than again used TextToInteger() for the aggregate condition,thanks it worked.
Mansimar,
Vikas has provided a solution to the problem you posed (remember to mark as solution).
From an architectural point of view, you would want to avoid having to replicate this logic each time you query.
When the data is loaded into the system, a step should occur that links data with foreign keys. It is fine during this step to have complex logic like you mention, but then entities should be linked using tables and foreign keys. Once this has happened it should simplify queries in the rest of the system.
If you still need to look up the data where someone enters and ID instead of their name, you might consider having two lookup attributes for the Id, that is not the database id. One for format 1 "12345" and another for format 2 "45", then the query could simple be Id1=SearchKeyword or Id2=SearchKeyword.
I hope this helps.
Kind regards,
Stuart
the organisation requirement...
This is the point where you should say: it will kill sql server, and it should be refactored.
Such DB structure is not acceptable.
I agree with Bad Code Guru.
Especially with an entity as Person you should always be 100% sure you get the right record.
In this case i would at least ad leading zeros to all PersonID's with less than 5 digits.
You just cannot have a foreign key relation like this, as it is destined to give wrong data at some point. GPRS will bite you in the end.