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?
Parameter in SQL results in incorrect substitutions
Moderators: chulett, rschirm, roy
Parameter in SQL results in incorrect substitutions
Eric Vandal - CGI
Can you replace the "%" with "\%" to see if that makes a difference?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Same problem. I get and upper(a.acct_name) not like '\DataStageERIODONTAL%'ArndW wrote:Can you replace the "%" with "\%" to see if that makes a difference? ...
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?
Eric Vandal - CGI
It isn't a documented parameter, but obviously there.
What about \%\P?
What about \%\P?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: