Issue with wildcard in parameter name.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with wildcard in parameter name.

Author

Topic

kranken
Starting Member

1 Post

Posted - 2010-03-20 : 10:15:43

Hello,

I wrote a following sql query and it works fine except for parameters like drying%Total, drying%1, drying%2. The reason is that in the name of parameters there is a wildcard used %(oracle DB). I know that I can use an escape command however I could not figure out how as I think that escape command only works with LIKE operator and not MAX. Could you please help me to resolve this problem?
Thank you in advance for your help.

SELECT c.numlotpharma AS lotnum , c.numof ,
MAX(DECODE(RTRIM(a.parametercode),'DRYING',numvalue,0)) AS dryingdata,
MAX(DECODE(RTRIM(a.parametercode),'DRYING%Total',numvalue,0)) AS dryingpercentageTotal,
MAX(DECODE(RTRIM(a.parametercode),'DRYING%1',numvalue,0)) AS dryingpercentage1,
MAX(DECODE(RTRIM(a.parametercode),'DRYING%2',numvalue,0)) AS dryingpercentage2,

FROM prodaccount.manufacturingdata a
INNER JOIN
(SELECT MAX(inputindex) AS inputindex,
parametercode ,
mancode
FROM prodaccount.manufacturingdata
WHERE parametercode IN ('DRYING')
GROUP BY parametercode, mancode) b
ON a.inputindex = b.inputindex
AND a.parametercode = b.parametercode
AND a.mancode = b.mancode
INNER JOIN (SELECT Numlotpharma, numof , codeproduit
FROM prodaccount.batchlotdetails d
WHERE d.codeproduit = ('Tablets')) c
ON a.mancode = c.numof
GROUP BY c.numlotpharma, c.numof
ORDER BY lotnum

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-20 : 11:01:37

i haven't worked with Oracle for a bit, but try using double quotes instead of single

"DRYING%Total"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources