Need approach on design of implementing If-elseif content SQL query in Talend
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
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_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;---- ------------------------------------------------------------------------------------------------------------------ -----------------IF MIN YEAR IS NOT SAME AS MAX YEAR-RECORD AND FOR LATEST YEAR DATA IS NOT AVAILABLE------------ --------------------------------------------------------------------------------------------------------------------
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
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_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 ;----
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.
