Skip to Content

HANA Sqlscript Partitioned Outer Join like in oracle

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

Related questions

1 Answer

  • Best Answer
    Posted on Nov 29, 2020 at 05:38 PM

    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