Solved

INNER JOIN ERRO IN Crystal Reports

Posted on 2007-04-09
11
1,117 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:jduclosjduclos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 18879323
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 18879750
DATE is quite often a reserved word.  Try using Adate or something a little more descriptive.

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18881781
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:jduclosjduclos
ID: 18883231
ok, let give that a try.  My guess is that you are right.
0
 

Author Comment

by:jduclosjduclos
ID: 18883287
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 18883708
Put chart# in " " or [ ]

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

mlmcc
0
 

Author Comment

by:jduclosjduclos
ID: 18883805
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
 
LVL 25

Expert Comment

by:jrb1
ID: 18883919
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
 

Author Comment

by:jduclosjduclos
ID: 18884906
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
 
LVL 25

Accepted Solution

by:
jrb1 earned 250 total points
ID: 18885222
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
 

Author Comment

by:jduclosjduclos
ID: 18900831
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

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question