Table Range Join

[Former Member]

Hi all, I have created 2 tables (tblBranch and tblReport).

tblBranch

Branch_Code	Code	GWP
BAT	B001	250
BAT	B003	100
BAK	B004	190
CB	C003	230
CB	C050	120
CB	C500	90

tblReport

Branch_Code	Branch	Code_Min	Code_Max
BAT	BA	null	null
BAK	BA	null	null
CB	CBA1	C000	C299
CB	CBA2	C300	C999

So, I do the theta join between Code to Code_Min and Code_Max in the universe. What I'm trying to achieve get the GWP (tblBranch) for each Branch (tblReport).

BAT and BAK (Branch Code) are suppose to combine into BA (Branch).

CB (Branch Code) will be link to CBA1 if the Code (tblBranch) in between C000 (tblReport.Code_Min)  and C299 (tblReport.Code_Max)

CB (Branch Code) will be link to CBA2 if the Code (tblBranch) in between C300 (tblReport.Code_Min)  and C999 (tblReport.Code_Max)

However when I try to validate the universe using web intelligence (filter report based on the Branch):

for BA Branch, the GWP is 540 (correct)

for CBA1 Branch, the GWP is 440 (wrong)

for CBA2 Branch, the GWP is 440 (wrong)

Anyone can help me to define where is the mistake or problem coming from.

[former_member207878]

Have you set cardinality N:1?? If it is, then try LTRIM function to remove first letter of code and after that compare. It will give you correct result.

Can you please attach screenshot of "Edit Join" dialog box?