Need approach on design of implementing If-elseif content SQL query in Talend

Anonymous — Sat, 16 Nov 2024 04:03:42 GMT

Hi Team,

Can you please suggest approach to design below SQL query in Talend . There are different query based on condition check in If -else if.

Please suggest.

IF LOC_REC IS NULL AND (LOC_MAX_YEAR - LOC_MIN_YEAR <= 1) THEN

INSERT INTO VMRCTTA1.VMRRCUST_DETAILS(CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV)
SELECT CUST_CNTRY_CD ,
CUST_YEAR ,
CUST_GSSN_CD ,
CUST_ACCNT_CD ,
CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND (
(
CUST_YEAR = LOC_MAX_YEAR-1
AND CUST_PERIOD = (
SELECT MAX(CUST_PERIOD)
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_YEAR = LOC_MAX_YEAR-1
)
)
OR (
CUST_YEAR = LOC_MAX_YEAR
AND CUST_PERIOD = LOC_MAX_PERIOD
)
);----

---------------------------------------------------------------------------------
--------------------------------IF MIN YEAR IS SAME AS MAX YEAR-----------------
---------------------------------------------------------------------------------

ELSEIF LOC_MIN_YEAR = LOC_MAX_YEAR THEN

ELSEIF LOC_MIN_YEAR != LOC_MAX_YEAR AND LOC_REC1 IS NULL AND (LOC_MAX_YEAR - LOC_MIN_YEAR <= 1) THEN

INSERT INTO VMRCTTA1.VMRRCUST_DETAILS(CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV)
--------------------------------------------------------------------------------
SELECT DISTINCT CUST_CNTRY_CD ,
CUST_YEAR ,
CUST_GSSN_CD ,
CUST_ACCNT_CD ,
CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND CUST_YEAR = LOC_MIN_YEAR
--------------------------
MINUS
----------------------------
SELECT CUST_CNTRY_CD ,
CUST_YEAR ,
CUST_GSSN_CD ,
CUST_ACCNT_CD ,
CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR;----
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
INSERT INTO VMRCTTA1.VMRRCUST_DETAILS
(
CUST_CNTRY_CD,
CUST_YEAR,
CUST_GSSN_CD,
CUST_ACCNT_CD,
CUST_CURR_VISITS,
CUST_VEH_THRPT_METHD_TYP,
CUST_VEH_THRPT_METHD,
CUST_INACCESSBLE_POTENTL,
CUST_POTENTL_METHD,
CUST_MB_VEH_THRPT,
CUST_VEH_THRPT,CUST_#_PLATFRM,
CUST_MB_SHARE,CUST_NO_OF_WORKERS,
CUST_COMMNTS,
CUST_MKT_DIV,
CUST_VISIT_UPDT_DT
)
SELECT
CS.CUST_CNTRY_CD,
CS.CUST_YEAR+1,
CS.CUST_GSSN_CD,
CS.CUST_ACCNT_CD,
CD.CUST_CURR_VISITS,
CASE WHEN CD.CUST_VEH_THRPT_METHD_TYP IS NULL OR CD.CUST_VEH_THRPT_METHD_TYP = '' THEN 'MANUAL' ELSE CD.CUST_VEH_THRPT_METHD_TYP END,
CD.CUST_VEH_THRPT_METHD,
CD.CUST_INACCESSBLE_POTENTL,
CASE WHEN CD.CUST_POTENTL_METHD IS NULL OR CD.CUST_POTENTL_METHD ='' THEN 'WAREGROUP' ELSE CD.CUST_POTENTL_METHD END,
CD.CUST_MB_VEH_THRPT,
CD.CUST_VEH_THRPT,
CD.CUST_#_PLATFRM,
CD.CUST_MB_SHARE,
CD.CUST_NO_OF_WORKERS,
CD.CUST_COMMNTS,
CS.CUST_MKT_DIV,
CD.CUST_VISIT_UPDT_DT

FROM
(
SELECT DISTINCT CUST_CNTRY_CD ,
LOC_MIN_YEAR AS CUST_YEAR ,
CUST_GSSN_CD ,
CUST_ACCNT_CD,
CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND CUST_YEAR = LOC_MAX_YEAR
) CS LEFT JOIN VMRCTTA1.VMRRCUST_DETAILS CD
ON CS.CUST_CNTRY_CD = CD.CUST_CNTRY_CD
AND CS.CUST_YEAR = CD.CUST_YEAR
AND CS.CUST_GSSN_CD = CD.CUST_GSSN_CD
AND CS.CUST_ACCNT_CD = CD.CUST_ACCNT_CD
AND CS.CUST_MKT_DIV=CD.CUST_MKT_DIV
WHERE CS.CUST_CNTRY_CD = LOC_CNTRY
AND CS.CUST_YEAR = LOC_MIN_YEAR;----

UPDATE VMRCTTA1.VMRRCUST_DETAILS SET CUST_CURR_VISITS = 0 WHERE CUST_YEAR = LOC_MAX_YEAR AND CUST_CNTRY_CD = LOC_CNTRY;----

--------------------------------------------------------------------------------

ELSEIF LOC_MIN_YEAR != LOC_MAX_YEAR AND LOC_REC1 IS NOT NULL AND (LOC_MAX_YEAR - LOC_MIN_YEAR <= 1) THEN
INSERT INTO VMRCTTA1.VMRRCUST_DETAILS(CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV)
--------------------------------------------------------------------------------
SELECT DISTINCT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND CUST_YEAR = LOC_MIN_YEAR
--------------------------
MINUS
----------------------------
SELECT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR

UNION

SELECT DISTINCT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND CUST_YEAR = LOC_MAX_YEAR
--------------------------
MINUS
----------------------------
SELECT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MAX_YEAR

UNION

SELECT CUST_CNTRY_CD,LOC_MAX_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV ---recently added
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR

MINUS

SELECT CUST_CNTRY_CD,LOC_MAX_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MAX_YEAR

;----

ELSEIF (LOC_MAX_YEAR - LOC_MIN_YEAR )> 1 THEN
--------------------------------------------------------------------------------
WHILE LOC_MIN_YEAR < LOC_MAX_YEAR DO

UNION

SELECT DISTINCT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_SUMM
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_PERIOD_TYPE = 'Y'
AND CUST_YEAR = LOC_MIN_YEAR + 1
--------------------------
MINUS
----------------------------
SELECT CUST_CNTRY_CD,CUST_YEAR,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR + 1

UNION

SELECT CUST_CNTRY_CD,LOC_MIN_YEAR + 1,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV ---recently added
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR

MINUS

SELECT CUST_CNTRY_CD,LOC_MIN_YEAR + 1,CUST_GSSN_CD,CUST_ACCNT_CD,CUST_MKT_DIV
FROM VMRCTTA1.VMRRCUST_DETAILS
WHERE CUST_CNTRY_CD = LOC_CNTRY
AND CUST_YEAR = LOC_MIN_YEAR + 1 ;----

Re: Need approach on design of implementing If-elseif content SQL query in Talend

Anonymous — Thu, 21 Nov 2019 22:06:26 GMT

Can you give a little more context to this? Do you want this to be replicated in a Talend Job or do you want to simply run this in a Talend Job? Both are possible. To replicate this in a Talend Job will take a lot more information to help you do it. To be honest, it is something that might be quite tricky without having access to the data. To run this in a Talend component, you would simply use a tDBRow and add BEGIN and END statements. Well, there may be a little more required, but essentially the above is what you would do.

topic Need approach on design of implementing If-elseif content SQL query in Talend in Talend Studio

Need approach on design of implementing If-elseif content SQL query in Talend

Re: Need approach on design of implementing If-elseif content SQL query in Talend