i am getting error while fetching the query .i ran the same query in Data studio against the DB2 tables and ran fine however while using the same in DB2 stage getting the below error.
EDWSTG1_ORBIT_MASTER: DB2 reported: SQLSTATE = 42603: Native Error Code = -10: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0010N The string constant beginning with "') >= current date - 93 days then 2 else 5 end =2;" does not have an ending string delimiter. SQLSTATE=42603
select
case
WHEN TRIM(TBORMSTR.PRI_CUR_APT_NUM) <> ' '
THEN Trim(TBORMSTR.PRI_CUR_STREET_NUM) || ' '|| trim(TBORMSTR.PRI_CUR_STR_NAME) || ' '||
trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim (TBORMSTR.PRI_CUR_STREET_DIR)||' #'||
trim(TBORMSTR.PRI_CUR_APT_NUM)
when trim(TBORMSTR.PRI_CUR_APT_NUM)= ' '
THEN trim(TBORMSTR.PRI_CUR_STREET_NUM) || ' '|| trim(TBORMSTR.PRI_CUR_STR_NAME)
|| ' '||trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim (TBORMSTR.PRI_CUR_STREET_DIR)
END as PRI_STREET_ADDY
FROM EDWSTG1dv.ORBIT_NEW TBORMSTR
LEFT OUTER JOIN EDWSTG1dv.ORBIT_HNB_REPORTING TBHNBMST
ON TBORMSTR .APPLICATION_ID = TBHNBMST.APPLICATION_ID
LEFT OUTER JOIN EDWSTG1DV.ORBIT_DEALER_RESERVE TBDLRRSV
ON TBORMSTR .APPLICATION_ID=TBDLRRSV.APPLICATION_ID
LEFT OUTER JOIN EDWSTG1DV.ORBIT_SMALL_BUSINESS TBSMLBUS
ON TBORMSTR .APPLICATION_ID=TBSMLBUS.APPLICATION_ID
WHERE
Case
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') < current_date- 93 days then 0
when TBORMSTR.OWNING_BRANCH_CODE = '08888' then 1
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') >= current_date- 93 days then 2 else 5 end =2;
where i have to escape the quotes form the query .
i tried by another way by checking the proper spaces among the concatenation of the fields.The query given below i used below for same on column taking two times one with space and one without .
After removing the space among the concatenation of the fields job works fine.
i have marked bold where in done changes.
Please advise on this.
select
case
WHEN TRIM(TBORMSTR.PRI_CUR_APT_NUM) <> ' '
THEN Trim(TBORMSTR.PRI_CUR_STREET_NUM) || ' '|| trim(TBORMSTR.PRI_CUR_STR_NAME) || ' '||
trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim (TBORMSTR.PRI_CUR_STREET_DIR)||' #'||
trim(TBORMSTR.PRI_CUR_APT_NUM)
when trim(TBORMSTR.PRI_CUR_APT_NUM)= ' '
THEN trim(TBORMSTR.PRI_CUR_STREET_NUM) || ' '|| trim(TBORMSTR.PRI_CUR_STR_NAME)
|| ' '||trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim (TBORMSTR.PRI_CUR_STREET_DIR)
END as PRI_STREET_ADDY,
case WHEN TRIM(TBORMSTR.PRI_CUR_APT_NUM) <>' 'THEN
Trim(TBORMSTR.PRI_CUR_STREET_NUM) ||' '|| trim(TBORMSTR.PRI_CUR_STR_NAME) ||' '||trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim (TBORMSTR.PRI_CUR_STREET_DIR)||'#'||
trim(TBORMSTR.PRI_CUR_APT_NUM)
when trim(TBORMSTR.PRI_CUR_APT_NUM)=' ' THEN
trim(TBORMSTR.PRI_CUR_STREET_NUM) ||' '||
trim(TBORMSTR.PRI_CUR_STR_NAME)||' '||trim(TBORMSTR.PRI_CUR_STR_TYPE)||' '||trim(TBORMSTR.PRI_CUR_STREET_DIR)
END as PRI_STREET_ADDY1
FROM EDWSTG1dv.ORBIT_ORBIT_MASTER TBORMSTR
LEFT OUTER JOIN EDWSTG1dv.ORBIT_HNB_REPORTING TBHNBMST
ON TBORMSTR .APPLICATION_ID = TBHNBMST.APPLICATION_ID
LEFT OUTER JOIN EDWSTG1DV.ORBIT_DEALER_RESERVE TBDLRRSV
ON TBORMSTR .APPLICATION_ID=TBDLRRSV.APPLICATION_ID
LEFT OUTER JOIN EDWSTG1DV.ORBIT_SMALL_BUSINESS TBSMLBUS
ON TBORMSTR .APPLICATION_ID=TBSMLBUS.APPLICATION_ID
WHERE
Case
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') < current_date- 93 days then 0
when TBORMSTR.OWNING_BRANCH_CODE = '08888' then 1
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') >= current_date- 93 days then 2 else 5 end =2
WHERE
Case
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') < current_date- 93 days then 0
when TBORMSTR.OWNING_BRANCH_CODE = '08888' then 1
when TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') >= current_date- 93 days then 2 else 5 end =2;
Why do you have a CASE statement when you only want a single result? This could be simplified and re-written as: