07-27-2017 4:34 AM - edited 02-03-2024 11:37 PM
Hi All,
I had this requirement to lookup a value from one table to another containing only ranges of value. The approach was to use a graphical calculation view but I can't seem to make it work because it has to have a join between table A and B.
To put a bit of clarity, say we have Table A - TBL_PRINCIPAL_MASTER and Table B - TBL_PRINCIPAL_RANGE with the following values
Should be result set:
Apparently, I can't do left outer join because it doesn't have a common value between the two table from which I could bind the result set. The thing which may tie the two can only be determined through conditional operator other than equal to(=)i.e. IF TBL_PRINCIPAL_MASTER.CODE IS BETWEEN TBL_PRINCIPAL_RANGE.FROM AND TBL_PRINCIPAL_RANGE.TO.Or CODE>=FROM AND CODE<=TO.
Now I am trying to work it out using SQL Calculation View/Procedure/Function, whichever serves the purpose. What I will do is populate TBL_PRINCIPAL_MASTER as my base table and iterate through it, get the CODE and pass it down to TBL_PRINCIPAL_RANGE as parameter.
Any advice will be much appreciated.
Regards,
Rod Castro
P.s. From BO Data Services perspective, this is definitely attainable.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Lars. My problem is having that exact same logic (or similar), implemented into a graphical calculation view. Or should I ditch that idea and use SQL instead?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Not too sure where the problem is here.
The following approach appears straight forward to me:
create column table principal_master
(code integer, name nvarchar(30));
create column table principal_range
(range_from integer, range_to integer, description nvarchar(40) );
insert into principal_master values (2, 'Rod');
insert into principal_master values (8, 'Marc');
insert into principal_master values (5, 'Jan');
insert into principal_range values (1, 5, 'Corporate Principal');
insert into principal_range values (6, 10, 'Regional Principal');
select
pm.*, pr.*
from
principal_master pm
left outer join principal_range pr
on pm.code between pr.range_from and pr.range_to;
CODE NAME RANGE_FROM RANGE_TO DESCRIPTION
2 Rod 1 5 Corporate Principal
8 Marc 6 10 Regional Principal
5 Jan 1 5 Corporate Principal
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.