Getting error while fetching queries from DB2 stage

[hargun]

hi,

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

the query is given below

Code: Select all

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;

Can someone help me on this.

[chulett]

Found this. Not a DataStage error but implies you may need to escape your single quotes.

[hargun]

thanks for reply chullet

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

[chulett]

FYI. Bold doesn't work inside code tags so I changed them to quote tags.

So... are you saying it works now? If so, what kind of advise are you looking for - why that change would have fixed it?

[cppwiz]

Code: Select all

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:

Code: Select all

WHERE TO_DATE(TBORMSTR.APP_ENTRD_SYS_DATE, 'YYYYMMDD') >= current_date- 93 days