"ODBC Error: SQL command not properly ended"

[kieso]

CR 7.0

Alright, now I know I'm not the brightest porch on the block when it comes to ODBC and SQL stuff, but gosh-darn-it, I spent the whole weekend going through online SQL tutorials, and the &*^%$#% repot still doesn't work. I think I get to ask my pain in the butt Crystal/SQL question now, even if it is a bit basic. )

I'm trying to link two table, each of which is in a different database. The AS400 table is
"CHICOPEE"."HSUSER"."RSSTOPBUN1"

The Oracle database created by UPS Logistic's Mobilecast is
"DMSDBA"."PRODUCTORDER" PRODUCTORDER

When I try to bring both databases into the same report, whether that be through a SQL statement, or by using the add database function in Crystal, I get the same error;

"ODBC Error: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended "

When I press okay, I get another error;
"Error detected by database DLL"

Note - I'm using CR 7.0 because these reports are destined to be run off of UPS's report viewer, which won't support reports from newer versions.

Some background....

I tried to run each database seperately, in it's own report and they run just fine. The following is the SQL queries from each of those reports;

report1
SELECT
RSSTOPBUN1."LOCATIONID", RSSTOPBUN1."P5TCON01"
FROM
"CHICOPEE"."HSUSER"."RSSTOPBUN1" RSSTOPBUN1



report2
SELECT
STOP."LOCATIONID",
PRODUCTORDER."PLANNEDSIZE1"
FROM
"DMSDBA"."STOP" STOP,
"DMSDBA"."PRODUCTORDER" PRODUCTORDER
WHERE
STOP."TRIPDATE" = PRODUCTORDER."TRIPDATE" AND
STOP."TRIPID" = PRODUCTORDER."TRIPID" AND
STOP."INTERNALSTOPID" = PRODUCTORDER."INTERNALSTOPID"

While includes lots of other links on the Oracle side, the following is the large SQL for the origional report. Using the "add database" function to add the HSUSER table causes errors, and when I go into the SQL statement, the HSUSER reference isn't even there. I created this SQL manually and got the above noted errors here as well;

report3
SELECT
STOP."LOCATIONID",
PRODUCTORDER."PLANNEDSIZE1"
FROM
"DMSDBA"."STOP" STOP,
"DMSDBA"."PRODUCTORDER" PRODUCTORDER,
"CHICOPEE"."HSUSER"."RSSTOPBUN1" RSSTOPBUN1
WHERE
STOP."TRIPDATE" = PRODUCTORDER."TRIPDATE" AND
STOP."TRIPID" = PRODUCTORDER."TRIPID" AND
STOP."INTERNALSTOPID" = PRODUCTORDER."INTERNALSTOPID" AND
STOP."LOCATIONID" = RSSTOPBUN1."LOCATIONID"

Interestingly, the process of adding the HSUSER table gets an error about how Outlook doesn't understand this request.

I hope that within this novella, I've given the one or two snippets of information that you all need to answer my (quite probably) stupid question. )

Thanks in advance

Rodger

 

[Crystalguru]

Rodger,

Found your post!

Have you thought about doing subreports for this? You already have your two queries/reports (one from AS400, one from Oracle).

later

 

[kieso]

You know what, C.G., I was actually going to e-mail you directly and admit to not having a stinking clue about Sub-reports. Truth is I've never tried one before.

I like the idea, though. If I understand them, I can take one report and stick it right onto another. I've read some posts about how summing and processing times can be a problem with sub-reports, but I don't think either should be an issue here.

Two questions...
1) I'm going to look at the FAQ's to see if there are some good basic tutorials on them. any suggestions there?
2) Once I learn how to do sub-reports, will I be able to show the bundle count from route 223 as shown in the
AS400 beside the other info of Route 223 from Oracle? That is, can I link a field from my main report to a subreport, or does a sub-report exist as a seperate entity, except that's printed on the same piece of paper? Sorry for the truly ignorant question, but I gotta start somewhere, right?

Thanks alot,

Rodger

P.S. Reebo, if you're out there.... post an answer and I promise no Scotish weather jokes..... really.... scouts honor.... I promise )

 

[Crystalguru]

Subreports aren't all that bad. Yes, they do have performance issues. Since I understand the data you are working with, I don't see that as a problem.

There are good tutorials out there. Keep looking!

Yes, you will be able to bundle the count from route 223 (AS400) beside the Route 223 (oracle).That's the beauty of subreports. There are two options to subreports- to link or not to link, that is your option...

later
crystalguru

PS. No Scotish weather jokes, ah shucks! The post isn't worth reading now....