Hana Sql script help needed

[Former Member]

Hi guys,

I am new to HANA . I have got a requirement .Please help me with below issue.

I have a table "ABC"

ABC

COUNTRY	REGION	REGION ID
USA	WEST	100
USA	NORTH	700
USA	NORTH EAST	700

Now there is another table DEF

REGION ID	COUNTRY	PROFIT	REVENUE
600	USA	100	200
650	USA	50	150
200	UK	50	150
100	APAC	100	250

Now the requirement is

IF "REGION ID" FROM TABLE ABC is 700 then fetch values from table DEF which has REGION ID =600 OR 650 ,and COUNTRY FROM table ABC= COUNTRY from TABLE DEF,and sum (profit+revenue) from table DEF.

[michael_eaton3]

Is this an interview question?  I only ask because sum(profit+revenue) satisfies absolutely no functional requirement that I can think of!

Please demonstrate how you would want to see the output.

Michael

[Former Member]

yes actually I have hidden the actual measure names

The output should be if we consider the above tables should be

country	region id	measure(prof+revenue)
usa	600	300
usa	650	200

[michael_eaton3]

Well, if its an interview question, should you be asking for help?

It can be done using a join, filtering using a where clause and an aggregation.  Thats enough hints!

Michael

[gopinath_kolli]

Are there any combinations other than 700-600,650.

[Former Member]

Yes it may contain

[gopinath_kolli]

Actually it depends on the pattern by which the id joins, if there is no pattern and there are less number of values then we can hard code.