Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INNER JOIN ERRO IN Crystal Reports

Posted on 2007-04-09
11
Medium Priority
?
1,130 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
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 1000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

577 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