RE: Converting Microsft access sql statement to an Iseries sql statement

You don't list the error you're getting. That would help distinguish
between syntax or some other type of problem.

Assuming it's a syntax problem, I think "hour(isotime" ending parenthesis is
in a wrong spot. Try this:

SELECT Company, SiteId, SiteId2 , Hour(isotime) as h, minute(isotime) as m
FROM RGCUSTOMD.Merccomp00 INNER JOIN RGCUSTOMD.Mercaud01 ON Meter = SiteId2
AND AISNumb = SiteId
WHERE (((IsoDate) Between (Current_date-4 days) And (Current_date-1 days)))

GROUP BY Company, SiteId, SiteId2, hour(isotime), minute(isotime)
HAVING (siteid <> 22) and max(data7) = '0' and hour(isotime) = 10 and
Minute(isotime) = 0

Hth

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: Converting Microsft access sql statement to an Iseries sql
statement

I have an ACCESS database that I'm trying to replace with a db2
database. I'm currently having difficulties with a query on ACCESS that
I'm trying to duplicate on the Iseries.

The access sql statement is as follows.
SELECT [Company index].Company, AuditTrail.SiteId, AuditTrail.SiteId2
FROM [Company index] INNER JOIN AuditTrail ON ([Company index].[AIS
Number] = AuditTrail.SiteId) AND ([Company index].Meter =
AuditTrail.SiteId2)
WHERE (((AuditTrail.LogDate) Between (Now()-4) And (Now()-1)))
GROUP BY [Company index].Company, AuditTrail.SiteId, AuditTrail.SiteId2,
Hour([logdate]), Minute([logdate])
HAVING (((AuditTrail.SiteId)<>22) AND ((Hour([logdate]))=10) AND
((Minute([logdate]))=0) AND ((Max(AuditTrail.Data7))="0"));


I cannot duplicate the having clause on the iseries. I'm not sure if
it's a syntax problem or what.
Here is my query. Given file names have changed and I have parsed the
date/time field in Access to date and Time fields in db2.

SELECT Company, SiteId, SiteId2 , Hour(isotime) as h, minute(isotime) as
m FROM RGCUSTOMD.Merccomp00 INNER JOIN RGCUSTOMD.Mercaud01 ON Meter =
SiteId2 AND AISNumb = SiteId WHERE (((IsoDate) Between (Current_date-4
days) And (Current_date-1 days))) GROUP BY Company, SiteId, SiteId2,
hour(isotime), minute(isotime) having (siteid <> 22) and max(data7) =
'0'
and hour(isotime = 10)
and Minute(isotime) = 0 ;

Any idea how to duplicate the having clause?

Michael Smith