Parameter in SQL results in incorrect substitutions

[evandal]

Hi, I have this SQL with a parameter in the where clause. The result of the parameter works but the resulting SQL is messed up. For some reason every %P gets replaced by "Datastage" at run time. I tried adding APT_OSL_PARAM_ESC_SQUOTE but no change. Here is the original SQL:

select A.kcm_ACCT_ID,ACCT_NAME as Orig_ACCT_NAME,
replace(A.ACCT_NAME,'/',' ') as ACCT_NAME,
A.acct_type,
A.acct_subtype,
AD.ADDR_LINE1, AD.ADDR_CITY, AD.ADDR_PROV, AD.ADDR_POSTCODE
from
#PSI_Connection.PSIDW_SO_KCM#.account_master A, #PSI_Connection.PSIDW_SO_KCM#.relate_acct_address CA,
#PSI_Connection.PSIDW_SO_KCM#.address_master AD
where A.acct_status = 1
and A.kcm_acct_id = CA.KCM_ACCT_ID
and CA.KCM_ADDR_ID = AD.KCM_ADDR_ID
and CA.ACCT_ADDR_STATUS = 1
and AD.ADDR_STATUS = 1
and upper(a.acct_name) not like '%OPTOMETRY CLIN.%'
and upper(a.acct_name) not like '%ANIMAL HOSP.%'
and upper(a.acct_name) not like '%OPTICAL%'
and upper(a.acct_name) not like '%OPTIQUE%'
and upper(a.acct_name) not like '%OPHTHALMOLOGY%'
and upper(a.acct_name) not like '%PERIODONTAL%'
and upper(a.acct_name) not like '%EYEWEAR%'
#OUTLET_FILTER#

and my OULET_FILTER parameter is and upper(a.acct_name) not like '%PHARMACY%'

and the result at run time.

select A.kcm_ACCT_ID,ACCT_NAME as Orig_ACCT_NAME,
replace(A.ACCT_NAME,'/',' ') as ACCT_NAME,
A.acct_type,
A.acct_subtype,
AD.ADDR_LINE1, AD.ADDR_CITY, AD.ADDR_PROV, AD.ADDR_POSTCODE
from
Z_KCM.account_master A, Z_KCM.relate_acct_address CA,
Z_KCM.address_master AD
where A.acct_status = 1
and A.kcm_acct_id = CA.KCM_ACCT_ID
and CA.KCM_ADDR_ID = AD.KCM_ADDR_ID
and CA.ACCT_ADDR_STATUS = 1
and AD.ADDR_STATUS = 1
and upper(a.acct_name) not like '%OPTOMETRY CLIN.%'
and upper(a.acct_name) not like '%ANIMAL HOSP.%'
and upper(a.acct_name) not like '%OPTICAL%'
and upper(a.acct_name) not like '%OPTIQUE%'
and upper(a.acct_name) not like '%OPHTHALMOLOGY%'
and upper(a.acct_name) not like 'DataStageERIODONTAL%'
and upper(a.acct_name) not like '%EYEWEAR%'
and upper(a.acct_name) not like 'DataStageHARMACY%'.

Any ideas?

[ArndW]

Can you replace the "%" with "\%" to see if that makes a difference?

[evandal]

Can you replace the "%" with "\%" to see if that makes a difference? ...

Same problem. I get and upper(a.acct_name) not like '\DataStageERIODONTAL%'

I also tried CHR(37)||'PERIODONTAL%' and that seems to work but it's a messed up way to write SQL if I have to change every where clause not to use %P.

So what is %P? Some environment Variable?

[ArndW]

It isn't a documented parameter, but obviously there.

What about \%\P?

[evandal]

\%\P doesn't get replaced by "Datastage" ... but it also doesn't work in a like statement. So like '\%\PERIODONTAL%' returns no rows.

[ArndW]

Unfortunately this looks like a problem that needs to be addressed to your service provider and fixed by IBM and I can't think of an easy workaround that doesn't involve changing your SQL. I wonder if the parsing problem persists if you move the wildcard strings to an included SQL file?

[ray.wurlod]

You might like to read that section of the manual that deals with handling the special characters "%" and "#" for DB2 and Oracle.