Skip to Content
author's profile photo Destine DINGA
0
Destine DINGA

HANA Sqlscript Partitioned Outer Join like in oracle

Posted on Nov 29, 2020 at 04:30 PM 42 Views Last edit Nov 29, 2020 at 04:59 PM 7 rev.

Dear community,

I am struggling with a case where I need to fill gaps when data on particular period is missing.

In my case for instance, when user selects data from periods between 201906 to 201912, we can get gaps we would like to fill:

Data in my table (GUID column is the key per period):

Desired output (missing columns would be populated from the previous record):

After having done some research, I have found that Oracle enables partitioned outer joins has explained here.

So I tried below query but HANA does not allow PARTITION BY to be used before an outer join condition. Only use of PARITION BY would be with window functions.

SELECT 
        t."CALMONTH",
        "BDP", 
        "GUID",
        "QUANTITY"
    FROM (
        SELECT 
            b."CALMONTH",",
            a."BDP", 
            a."GUID",
            a."QUANTITY"
        FROM :T_final AS a, :lt_periods AS b
        WHERE a.calmonth = b.calmonth
        GROUP BY b."CALMONTH",
                a."BDP", 
                a."GUID",
                a."QUANTITY"  
        ) AS v
    PARTITION BY (v.GUID)
    RIGHT OUTER JOIN 
        ( SELECT calmonth
          FROM :lt_periods
            ) AS t
    ON (v.calmonth = t.calmonth)
    ORDER BY t.calmonth;

Local table lt_periods contains all periods required for the data analysis.

Has anybody been able to achieve such requirement?

I have tried something like this:

        SELECT 
            b."CALMONTH",
            rank() OVER(PARTITION BY guid ORDER BY b.CALMONTH) AS "RANK",
            a."BDP", 
            a."GUID",
            a."QUANTITY"
         FROM :T_final AS a 
        RIGHT OUTER JOIN :lt_periods AS b
        ON a.calmonth = b.calmonth
        ORDER BY GUID, CALMONTH

And corresponding result (here missing periods are 201907, 20910, 201911 for both GUID):

Could you please throw me some light?

Regards,

Destiné.

03ozm.png (6.8 kB)
xpu28.png (10.7 kB)
capture.png (15.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Konrad Zaleski
    Konrad Zaleski
    Posted on Nov 29, 2020 at 05:38 PM
    1

    One of the option is create calculated column which will help to generate month range for each record (CALMONTH_FROM and CALMONTH_TO). Once it is generated you can perform join to the periods table based on that range (CALMONTH from period table between CALMONTH_FROM and CALMONTH_TO from final table). See the code below:

    DO
MONTH_RANGE=
	SELECT
		"BDP",
		"GUID",
		"QUANTITY",
		"CALMONTH" AS "CALMONTH_FROM",
		CASE 
			WHEN "NEXT_CALMONTH" IS NULL THEN "CALMONTH"
			WHEN TO_DATE("CALMONTH") = ADD_MONTHS(TO_DATE("NEXT_CALMONTH"), -1) THEN "CALMONTH" 
			ELSE TO_VARCHAR(ADD_MONTHS(TO_DATE("NEXT_CALMONTH"), -1), 'YYYYMM') END
		AS "CALMONTH_TO"
	FROM
	(	
		SELECT
			"BDP",
			"GUID",
			"QUANTITY",
			"CALMONTH",
			LEAD("CALMONTH") OVER (PARTITION BY "GUID" ORDER BY "CALMONTH") "NEXT_CALMONTH"
		FROM
			:T_FINAL
	);


SELECT 
	LT_P."CALMONTH",
	LT_MR."BDP",
	LT_MR."GUID",
	LT_MR."QUANTITY"
FROM
	:LT_PERIODS LT_P
	JOIN :MONTH_RANGE LT_MR ON LT_P."CALMONTH" BETWEEN LT_MR."CALMONTH_FROM" AND LT_MR."CALMONTH_TO"
	;
	
END

    And here is the output


    out.png (9.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded