CASE WHEN in LEFT JOIN

[former_member762951]

HI Experts,

Please, tell me why did my query doesn't work if I use "CASE WHEN" in or after "LEFT JOIN"?

This is my query:

DO BEGIN




DECLARE bulan NVARCHAR(2) = '1'; --bulan
DECLARE tahun NVARCHAR(4) = '2020'; --tahun
--DECLARE kode_entitas NVARCHAR(6) = '110'; --kode entitas
DECLARE tgl_awal NVARCHAR(20) = '2020-01-01'; 
DECLARE tgl_akhir NVARCHAR(20) = '2020-01-31';

SELECT *, CAST(IFNULL("selisih" * "denda" / 100, 0) AS DECIMAL(18,4)) AS "jumlah"

FROM 
(
    SELECT a.*,
      	CAST(IFNULL(d."U_kpi",0) AS DECIMAL(6,2)) AS "kpi",
       	0 AS "denda"  
    FROM (
    -------------------------------------------------------------------------------
	SELECT 	
		SUM("target") AS "target", 
		SUM("realisasi") AS "realisasi",
		CASE WHEN (SUM("target") = 0)
		THEN 0
		ELSE
        CAST((SUM("realisasi")/SUM("target"))*100 AS DECIMAL(6,2)) 
        END 
        AS "persen",      
       	CASE WHEN(SUM("target")-SUM("realisasi") <= 0) THEN 0 
      	ELSE SUM("target")-SUM("realisasi") 
        END
        AS "selisih"      
        FROM (
			--================ Target Penjualan ==================
            SELECT 
               	IFNULL(CAST(SUM(IFNULL(e."U_Qty",0)*(IFNULL(e."U_Harga",0))) AS DECIMAL(18,4)),0) AS "target",
               	CAST(0 AS DECIMAL(18,4)) AS "realisasi"  
            FROM "@TB_M_TARGETDETAIL" e
            LEFT JOIN "@TB_M_TARGETGLOBAL" f ON
            	(f."U_Grp_item"=e."U_Grp_item" AND f."U_Kel_item"=e."U_Kel_item" AND f."U_Tahun"=:tahun)
            WHERE e."U_Tahun"=:tahun AND e."U_Bulan"=:bulan
            UNION ALL
            --================ Pencapaian Penjualan =================
            --================ Faktur Penjualan =====================
            SELECT CAST(0 AS DECIMAL(18,4)) AS "target", 
                   IFNULL(SUM(IFNULL(d."Price",0)),0) AS "realisasi"
            FROM INV1 d
            LEFT JOIN OINV m ON m."DocEntry"=d."DocEntry"
            LEFT JOIN OSLP s ON s."SlpCode"=m."SlpCode"
            LEFT JOIN OCRD c ON c."CardCode"=m."CardCode"
            LEFT JOIN OITM i ON i."ItemCode"=d."ItemCode"
            WHERE m."DocDate" BETWEEN :tgl_awal AND :tgl_akhir
               AND TRIM(LOWER(s."SlpName")) <> 'antar cabang'
               AND LOWER(c."U_TIPE") <> 'cabang'
               --AND LOWER(c."U_tipe2") <> 'grup' <INI HARUS DAN PENTING>
               --AND i."U_tipe"='Persediaan' <INI HARUS DAN PENTING>
            UNION ALL
            --================ Retur Penjualan =====================
            SELECT CAST(0 AS DECIMAL(18,4)) AS "target", 
                   IFNULL(SUM(IFNULL(d."Price",0))*-1,0) AS "jumlah"
            FROM RIN1 d
            LEFT JOIN ORIN m ON m."DocEntry"=d."DocEntry"
            LEFT JOIN OSLP s ON s."SlpCode"=m."SlpCode"
            LEFT JOIN OCRD c ON c."CardCode"=m."CardCode"
            LEFT JOIN OITM i ON i."ItemCode"=d."ItemCode"
            WHERE m."DocDate" BETWEEN :tgl_awal AND :tgl_akhir
               AND TRIM(LOWER(s."SlpName")) <> 'antar cabang'
               AND LOWER(c."U_TIPE") <> 'cabang'
               --AND LOWER(c.tipe2) <> 'grup'
               --AND i.tipe='Persediaan'
		)a
	----------------------------------------------------------------------------------------
	) a
LEFT JOIN "@TB_M_DENDA" d ON d."U_prioritas"='1' AND
		CASE WHEN d."U_operator" = 'Antara' THEN (a."persen" BETWEEN d."U_jml1" AND d."U_jml2")
			AND
			(CASE WHEN d."U_operator" = '>' THEN (d."U_jml1" < a."persen"  AND a."persen" > d."U_jml1")
				ELSE 
				(CASE WHEN d."U_operator" = '<' THEN (d."U_jml1" > a."persen"  AND a."persen" < d."U_jml1")
						ELSE 
						(CASE WHEN d."U_operator" = '>=' THEN (d."U_jml1" <= a."persen" AND a."persen" >= d."U_jml1")
								ELSE 
								(CASE WHEN d."U_operator" = '<=' THEN (d."U_jml1" >= a."persen" AND a."persen" <= d."U_jml1")
										ELSE 
										(CASE WHEN d."U_operator" = '=' THEN (d."U_jml1" = a."persen")
												ELSE
												TRUE
										END)
								END)
						END)
				END)
			END)
		END
) a;
END;

...and then this is message error from Hana DB Studio:

Could not execute 'DO BEGIN DECLARE bulan NVARCHAR(2) = '1'; --bulan DECLARE tahun NVARCHAR(4) = '2020'; --tahun ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "BETWEEN": line 74 col 56 (at pos 3298)

FYI. Before, I write this query using mysql rules, and that is work. But, while i trying this query on Hana rules (write the query in SAP Hana using Hana DB Studio), this is not work. How can do that? If you need the MySQL query, you can follow this link https://pastebin.com/VjN62ECa.
Thankyou.

[ThorstenHoefer · Accepted solution]

Hi k-sandi,

please check following between statement in your case statement:

A case statement define a return value dependent on a condition.

it should be :

            CASE <expression><br>            WHEN <expression> THEN <expression>
            [{ WHEN <expression> THEN <expression>}…]
            [ ELSE <expression>]
            END

CASE WHEN d."U_operator" = 'Antara' THEN (a."persen" BETWEEN d."U_jml1" AND d."U_jml2")



But you can use

where ...
and (
   ( d."U_operator" = 'Antara' and a."persen" BETWEEN d."U_jml1" AND d."U_jml2" )
or (d."U_operator" = '<' and d."U_jml1" > a."persen"  AND a."persen" < d."U_jml1")
or ( .... ) 
)

[former_member762951 · Accepted solution]

Thankyou xtrnhfo.
I have trying on my query. But, this still return message error:

Could not execute 'DO BEGIN DECLARE bulan NVARCHAR(2) = '1'; --bulan DECLARE tahun NVARCHAR(4) = '2020'; --tahun ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "BETWEEN": line 73 col 56 (at pos 3296)

This is part of my query :

...
LEFT JOIN "@TB_M_DENDA" d ON d."U_prioritas"='1' AND
		CASE WHEN d."U_operator" = 'Antara' THEN (a."persen" BETWEEN d."U_jml1" AND d."U_jml2")
		END
		
) a;
...

--------

Can you be more specific please? or tell/give me some example using my query?
Thankyou sir.

[ThorstenHoefer · Accepted solution]

Please try to replace it with :

LEFT JOIN"@TB_M_DENDA" d ON d."U_prioritas"='1' 

AND  ( ( d."U_operator" = 'Antara' and a."persen" BETWEEN d."U_jml1" AND d."U_jml2" )
   or (d."U_operator" = '<' and d."U_jml1" > a."persen"  AND a."persen" < d."U_jml1" )
)

[ghoshsayantan3 · Accepted solution]

Hi Kurnia,

I see an extra "and'" after your first case statement .Usually "and" is for appending conditions where as here the first case is giving output then your trying to add a case to it by using "and".

Please see if that can be the reason.

regards,

CASE WHEN d."U_operator" = 'Antara' THEN (a."persen" BETWEEN d."U_jml1" AND d."U_jml2")
			AND