Does SAP HANA Have similar function with SAP BEX QUERY DESIGNER selection function？

[zhang_jerry]

Dear Folks，

Coz I'm working in the very big HANA Development Project recently, I found a very stupid design mechinism especially when we need calculated some measures in one sql statement, such as :A - B + C = D. code like below:

select clientid,

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "A",

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(B) else 0 end) as "B",

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(C) else 0 end) as "C"

FROM TABLENAME

GROUP BY

clientid.

the interesting thing show, when we need calculated measures D, we need copy the very long sql to calculate.

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) - (case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(B) else 0 end)
+ (case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(C) else 0 end) AS D.

I knew some guys may suggest I can call another sql statement to nest above code aim to reduce code complexity.

code like below :

select clientid,"A",'B","C",(A-B+C) AS D FROM(

select clientid,

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "A",

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "B",

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "C"

FROM TABLENAME

GROUP BY

clientid

)

sure, I admit to above sql design is absolutely reasonable,but why not we compile sql code in one go?

I think if we can deign temp variant like bex query'selection function, it should be easier to deign code and compile in one go.

that's my thinking about above code, code like below:

select clientid,

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "A" @A,

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(B) else 0 end) as "B" @B,

(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(C) else 0 end) as "C" @C

@A - @B + @C AS "D"

FROM TABLENAME

GROUP BY

clientid.

It's much easier to read sql and compile the sql engine in one go..

it's just in my personal opinion, does have any sap hana experts share their professional suggestion?

[KonradZaleski · Accepted solution]

First of all this kind of mechanism is not a HANA feature, but this is how the syntax looks like in SQL which is standard. In this case you have no possibility to refer to aliases in single SELECT.

As you mentioned you need to use nested SELECTs or assign result set to the variable and then use this variable as a reference, like here:

DO
BEGIN

	CALC_RESULT =
	
			SELECT 
				clientid,
				(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "A",
				(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "B",
				(case when storagelocation in (1,2,3,4,5,6,7,8,9,10) AND movementtype = 'xx' AND specialInventoryflag = 'X' then sum(A) else 0 end) as "C"
			FROM 
				TABLENAME
			GROUP BY
				clientid
				;
				
	SELECT
		clientid, A, B, C, (A-B+C) 
	FROM
		:CALC_RESULT
		;

END