FW: sql -- scan for @ in field

Both of the responses below really helped. Sorry to take so long to
response but I appreciate the help!!

Thanks, Chuck

Chuck Sallee
CIS Manager
Lexington Division of Police

-----Original Message-----
From: beppecosta [mailto:beppecosta@xxxxxxxxxxx]
Sent: Thursday, November 13, 2008 3:54 AM
To: RPG programming on the AS400 / iSeries
Subject: R: sql -- scan for @ in field

Maybe this can help you on how LOCATE can be used ...

We have a db field called REQUEST that contains an Xml statement.

Between tags <ApplGroup> and </ApplGroup> there is the application group
name.

We want to count the activity grouped by application.

This is the SQL statement:

SELECT cast(substr(REQUEST,
cast(locate('<ApplGroup>', REQUEST, 1) as smallint) + 11,
cast(locate('</ApplGroup>', REQUEST, 3) as smallint) - 11 -
cast(locate('<ApplGroup>', REQUEST, 1) as smallint)
) as
char(20)) Application , count(*) Usage
from wpodtrpf where datelog = '2008-07-31'
and locate('<ApplGroup>', REQUEST, 1) > 0
group by
cast(substr(RICHIESTA,
cast(locate('<ApplGroup>', REQUEST, 1) as smallint) + 11,
cast(locate('</ApplGroup>', REQUEST, 3) as smallint) - 11 -
cast(locate('<ApplGroup>', REQUEST, 1) as smallint)
) as
char(20))
order by 2 desc

----------------
Giuseppe.


I haven't used them but the POSITION, POSSTR or LOCATE functions look
like they might do the trick.

Found in the DB2 for i5/OS SQL Reference - (From the Redbook site I
think)

Pete