CompEng   600 pts.  |   Oct 27 2009  4:19PM GMT

This is where I added the max and the count. When I ran it, it give me the error [IBM][System i Access ODBC Drive][DB2 for i5/os]SQL0140-Token MAX was not valid. Valid tokens: , FROM INTO (#104)
How would this be set up so that there would be no duplication in the Part Number (INMSP300.M3PRD as “PN”) field. I only need one for any group or set of “OE nubmers or Part Numbers”, Like mentioned above the part number may have multiple “OENo” numbers to one part number. I need a list of all the part numbers as to what is associated to them, if could be any one of the OE numbers in the list.. I need a count of all the different part numbers regardless of the OE numbers

Select
INMSP300.M3OE# as “OENo”,
INMSP300.M3OEC as “ComprsOE”,
INMSP300.M3PRD as “PN”,
INMSP300.M3CLS as “PrdLine”,
INMSP100.MSAPP as “Application”,
INMSP100.MSPRD as “PartNo”,
INMSP100.MSRDTM as “rtd-Month”,
INMSP100.MSRDTD as “rdc-Day”,
INMSP100.MSRDTY as “rtc-Year”,
INMSP100.MSMPC as “Phase”
Max(INMSP300.M3OE# as “OENo”),
Count(INMSP300.M3PRD as “PN”) as numbers
From
rdb.CARF1.INMSP300 INMSP300
join rdb.
CARF1.INMSP100 INMSP100
on M3PRD = MSPRD
Where
INMSP300.M3CLS =’78′
and INMSP100.MSRDTY = ‘09′
and INMSP100.MSRDTM = ‘11′
order by INMSP100.MSMPC
Group by INMSP300.M3PRD as “PN”

CompEng   600 pts.  |   Oct 27 2009  4:21PM GMT

I have been doing some research on this and it seem like I might need a “Have” ralther then a “Where” is that so?

CompEng   600 pts.  |   Oct 27 2009  4:22PM GMT

Would I need to nest a select statement or not?

CompEng   600 pts.  |   Oct 27 2009  4:24PM GMT

Is there ever a time that you can use both the a “Have” and a “Where” in the same query. If so, is this the time that something like that would need to be used. The reason I ask that is due to the filter like “Where” statements that I are used at the end of my query statement?

Carlosdl   29795 pts.  |   Oct 27 2009  9:58PM GMT

You are getting that error because a comma (,) is missing before the MAX keyword.

As for your last questions, it is HAVING, not HAVE, and it is used when you want to filter the results based on the aggregate functions (i.e. sum, max, etc…).

And yes, you can have both, ‘where’ and ‘having’ in the same command.

Carlosdl   29795 pts.  |   Oct 27 2009  10:15PM GMT

Also, you will get errors if you don’t GROUP BY all the columns you include in your select list.

Why did you need to modify the query provided by Phil ?
Is there some different requirment from the one you posted in your original question ? could you please provide more details about that ?

Philpl1jb   24540 pts.  |   Oct 28 2009  12:03AM GMT

We were making a summary query .. necessary to get the count.
In that type of query every column needs to be a summary function (max, min, count, etc) or a group by field. And you were missing a comma.
Select
max(INMSP300.M3OE#) as “OENo”,
max(INMSP300.M3OEC) as “ComprsOE”,
max(INMSP300.M3PRD) as “PN”,
max(INMSP300.M3CLS) as “PrdLine”,
max(INMSP100.MSAPP) as “Application”,
max(INMSP100.MSPRD) as “PartNo”,
max(INMSP100.MSRDTM) as “rtd-Month”,
max(INMSP100.MSRDTD) as “rdc-Day”,
max(INMSP100.MSRDTY) as “rtc-Year”,
max(INMSP100.MSMPC) as “Phase”,
INMSP300.M3OE# as “OENo”,
Count(INMSP300.M3PRD ) as numbers
From
rdb.CARF1.INMSP300 INMSP300
join rdb.
CARF1.INMSP100 INMSP100
on M3PRD = MSPRD
Where
INMSP300.M3CLS =’78′
and INMSP100.MSRDTY = ‘09′
and INMSP100.MSRDTM = ‘11′
order by INMSP100.MSMPC
Group by INMSP300.M3PRD

Philpl1jb   24540 pts.  |   Oct 28 2009  12:05AM GMT

The Where clause is used to determine which records are used from the two files
A having clause would be used against a summary column
ie having count(*) > 4

Hope this gets you near your answer
I think a number of the columns maybe unnessary in the select.
Phil

CompEng   600 pts.  |   Oct 28 2009  11:37AM GMT

The initial statement that I posted was a genernic and the specific was what I recently posted. The reason I posted the generic was for me to give an example I did have enough space to show the specifics data. So I made up a general. Then I tried to apply the general statement to specific query and then I got the error that I just posted.

Now I pasted what Phil posted and now I am getting an error
[IBM]Sytem i Access ODBC Driver][DB2for i5/OS]SQL0122 - -Column MSMPC or expression oin the SELECT list not vaild. I reviewed all the feilds and as for as I can see they are OE

Here is the statement
Select
max (INMSP300.M3OE#) as “OENo”,
max (INMSP300.M3OEC) as “ComprsOE”,
max (INMSP300.M3PRD) as “PN”,
max (INMSP300.M3CLS) as “PrdLine”,
max (INMSP100.MSAPP) as “Application”,
max (INMSP100.MSPRD) as “PartNo”,
max (INMSP100.MSRDTM) as “rtd-Month”,
max (INMSP100.MSRDTD) as “rdc-Day”,
max (INMSP100.MSRDTY) as “rtc-Year”,
max (INMSP100.MSMPC) as “Phase”,
Count (INMSP300.M3PRD) as “numbers”
From
rdb.CARF1.INMSP300 INMSP300
join rdb.
CARF1.INMSP100 INMSP100
on M3PRD = MSPRD
Where
INMSP300.M3CLS =’79′
and INMSP100.MSRDTY = ‘09′
and INMSP100.MSRDTM = ‘11′
order by INMSP100.MSMPC

CompEng   600 pts.  |   Oct 28 2009  11:59AM GMT

So what is wronge with the above statement? if the files and fields are fine what eles could be the problem?

CompEng   600 pts.  |   Oct 28 2009  12:02PM GMT

The OE in the last sentance of the posting of the query statement should be OK.. “I reviewed all the feilds and as for as I can see they are OE ” should be OK not OE

Philpl1jb   24540 pts.  |   Oct 28 2009  11:18PM GMT

Select
INMSP300.M3PRD as “PN”, Count(INMSP300.M3PRD ) as numbers
From rdb.CARF1.INMSP300 INMSP300
join rdb.CARF1.INMSP100 INMSP100
on M3PRD = MSPRD Where
INMSP300.M3CLS =’78′
and INMSP100.MSRDTY = ‘09′
and INMSP100.MSRDTM = ‘11′
Group by INMSP300.M3PRD

Start with this, if it does what you want then add back the max of one column at a time, continuing to retest each time.
Good luck Phil

CompEng   600 pts.  |   Oct 29 2009  9:51AM GMT

This is bizzar…I pasted the query and in doing so there were a few charactors in the pasting changed. So I corrected them, but when I ran the query the error I got was… of course all the AS400 query infor but the just of the error was an error in the “From Statement” when I clicked the “OK” in the query SQL page it highlighted the word “join”. So I typed complete statements into the SQL area and then ran the query and the same error accured. So what that all about?

Kccrosser   1850 pts.  |   Oct 29 2009  3:44PM GMT

The Join statement doesn’t qualify the columns with the table aliases - if either of the columns in the join is in both tables, then the join is ambiguous:

From rdb.CARF1.INMSP300 INMSP300
join rdb.CARF1.INMSP100 INMSP100
on M3PRD = MSPRD

You may need to use:

From rdb.CARF1.INMSP300 INMSP300
join rdb.CARF1.INMSP100 INMSP100
on INMSP300.M3PRD = INMSP100.MSPRD

or something similar (I am not sure if I associated the correct table/columns).

Philpl1jb   24540 pts.  |   Oct 29 2009  10:57PM GMT

So .. have you tried the suggestion to qualify the join on field?
Since our last try failed, I would try something simplier until we get it to work..perhaps like this.
Phil

Select
INMSP300.M3PRD as “PN”, Count(INMSP300.M3PRD ) as numbers
From rdb.CARF1.INMSP300 INMSP300
Where INMSP300.M3CLS =’78′
Group by INMSP300.M3PRD

CompEng   600 pts.  |   Oct 30 2009  1:49PM GMT

Yes I ran the query and it worked. It is the little things that get you..It seems like sometimes when you past for the sites to a query page in access some of the grammar signs seem to take on a different form. Then when I run the query it sees the symbol as incorrect. In this case there was a single quotation mark that needed to be a double quotation mark (‘ to “). Then I added the INMSP300 to the part of the WHERE statement and the query ran just great. Thanks again for all the help. Great Job!!!!!!!!