Skip to Content
avatar image
Former Member

Option to implement LOOKUP( ) Functionality in HANA Calculation View


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.

source.png (4.6 kB)
target.png (2.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jul 27, 2017 at 04:41 AM

    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
    
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 27, 2017 at 05:31 AM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 03, 2017 at 08:02 PM
    Add comment
    10|10000 characters needed characters exceeded