Solved

INNER JOIN ERRO IN Crystal Reports

Posted on 2007-04-09
11
1,109 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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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 100

Expert Comment

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

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
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
 

Author Comment

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

Author Comment

by:jduclosjduclos
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Put chart# in " " or [ ]

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

mlmcc
0
 

Author Comment

by:jduclosjduclos
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now