INNER JOIN ERRO IN Crystal Reports

I have this SQL statement:

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as date
FROM  RTDETL
INNER JOIN
             (SELECT @RDCHT as chart, max(@RDDAT) as maxdate
              FROM RTDETL) as ChartRec
GROUP BY chart
ON chart# = chart and  date = Maxdate

I try to run it and I get this for an error
Failed to open rowset.
Details: 42000:[IBM]{iSeries Access ODBC Driver][DB@ UDB]SQL0199 - Keyword GROUP not expected. Valid tokens: ON LEFT CROSS INNER RIGHT EXCEPTION.

I not sure i understand this error can someone please explain what it means and help me figure out how to fix it.
jduclosjduclosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrb1senior developerCommented:
Do you want:

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as date
FROM  RTDETL
INNER JOIN
             (SELECT @RDCHT as chart, max(@RDDAT) as maxdate
              FROM RTDETL GROUP BY chart) as ChartRec
ON chart# = chart and  date = Maxdate
0
mlmccCommented:
DATE is quite often a reserved word.  Try using Adate or something a little more descriptive.

mlmcc
0
frodomanCommented:
I think both responses are correct.  DATE is probably a keyword and may cause you problems so you should rename it.  jrb1 is also correct - the GROUP BY clause needs to be inside the parenthesis that contain your aggregate function (MAX) - otherwise the SQL parses so you're trying to select a max without grouping and you're trying to group a non-grouping query.

frodoman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jduclosjduclosAuthor Commented:
ok, let give that a try.  My guess is that you are right.
0
jduclosjduclosAuthor Commented:
SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as adate
FROM  RTDETL
INNER JOIN
             (SELECT @RDCHT as chart, max(@RDDAT) as maxdate
              FROM RTDETL GROUP BY chart) as Chartrec
ON chart# = chart and adate = maxdate

ok i did some changes and now i get

failed to open rowset.
details: 42S22:[IBM]{iSeries Access ODBC Driver][DB2 UDB]SQL0206 - Column chart# not in specified tables.


0
mlmccCommented:
Put chart# in " " or [ ]

Do all the fields inthe tables have @ as the first letter?

mlmcc
0
jduclosjduclosAuthor Commented:
yes they do, the AS/400 has them list out this way in crystal reports.

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as adate
      FROM  RTDETL
      INNER JOIN
             (SELECT @RDCHT as ( it doesn't like this chart for some reason) -->chart, max(@RDDAT) as maxdate
              FROM RTDETL GROUP BY chart) as Chartrec
      ON chart# = chart and  adate = maxdate
0
jrb1senior developerCommented:
I doubt if you can refer to the alias in the query...only in the result set:

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as adate
FROM  RTDETL
INNER JOIN
             (SELECT @RDCHT as chart, max(@RDDAT) as maxdate
              FROM RTDETL GROUP BY chart) as Chartrec
ON @RDCHT  = chart and @RDDAT  = maxdate
0
jduclosjduclosAuthor Commented:
I am not sure this is going to work.  all I really want to do is get the first record base on the Chart#.  Since the table i am oulling from is really a journal entry of movement on a chart with in a clinic.
0
jrb1senior developerCommented:
I've not seen the "@" in the columns before, but give this a try.  It should pull the first record for each char#.

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as adate
FROM  RTDETL A
WHERE @RDDAT = (SELECT MIN(@RDDAT) FROM RTDETL
                                WHERE @RDCHT = A.@RDCHT)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jduclosjduclosAuthor Commented:
Yea, it comes off an AS/400 PM software package, that we are migrating from.
I used you select, switched it to MAX instead of MIN, since i am looking actually for the latest record, which happens to be the first record on the list so here is what is used.

SELECT @RDCHT as chart#, @RDLOC as location, @RDDAT as adate
FROM  RTDETL A
WHERE @RDDAT = (SELECT MAX(@RDDAT) FROM RTDETL
                                WHERE @RDCHT = A.@RDCHT)

and it works great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.