× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Mike,

With the GROUP BY criteria you have and when "Minute(isotime) = 0", the
value in that single matching row will be 34, so max(data7) is 34 and that
fails the test "AND MAX(DATA7) = '0'", so no data is returned.
SQL statement is only returning the result you asked for.

If you change the minute test to "Minute(isotime) <> 0" you should pick up
the row where DATA7 = 0 and that company will show.
I don't know if that's the right answer in this situation, you'll have to be
the judge of that.

Elvis

Celebrating 10-Years of SQL Performance Excellence

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

Thanks Elvis
It is mostly working now. I still have 1 issue- maybe you can solve
with this sql statement.
Give the following data.
COMPANY SiteID
SiteID2 ISOTIME(HOUR) ISOTIME(MINUTE) DATA7
J&J Asphalt 35 0
2 0 34
J&J Asphalt 35 0
6 0 34
J&J Asphalt 35 0
1 0 34
J&J Asphalt 35 0
5 0 34
J&J Asphalt 35 0
3 0 34
J&J Asphalt 35 0
7 0 34
J&J Asphalt 35 0
10 0 34
J&J Asphalt 35 0
10 45 0
J&J Asphalt 35 0
8 0 34
J&J Asphalt 35 0
9 0 34
J&J Asphalt 35 0
4 0 34

This company picks up when running the query in Access, It does not pick
up when running on the Iseries. It seems to fall off when the max(data7)
= '0' is added to the query.

Any ideas?

Michael Smith
iSeries.mySeries.


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


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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.