Skip to Content
0
Jul 17 at 06:23 AM

CASE WHEN in LEFT JOIN

360 Views

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.