Printable View
I'm working with the Crystal Reports that ships with VB5 (Crystal Reports 4.6) and Oracle 8. I have a system DSN set up using the Oracle ODBC Driver v.8.00.05. When I go into Crystal Reports, complete the new report wizard and then try to preview the report that I just created, I get an error: ODBC error: [Oracle][ODBC][Ora] ORA-00928: missing SELECT keyword. When I look at the SQL Query, the select keyword is present and the rest of the SQL looks good.
This actually happens with all of the reports that I have tried to create so far, so I'm posting all the SQL's.
1.
SELECT
RESTORE_LOG."DATE_TIME", RESTORE_LOG."CONTACT_ID", RESTORE_LOG."NO_TAPES_REQ", RESTORE_LOG."EMERGENCY_Y_N", RESTORE_LOG."ENTERED_BY",
CONTACT."CONTACT_NAME",
SBU_LOOK."SBU_NAME"
FROM
"PHL_OWNER"."RESTORE_LOG" RESTORE_LOG,
"PHL_OWNER"."CONTACT" CONTACT,
"PHL_OWNER"."SBU_LOOK" SBU_LOOK
WHERE
RESTORE_LOG."CONTACT_ID" = CONTACT."CONTACT_ID" AND
CONTACT."SBU_NO" = SBU_LOOK."SBU_NO" AND
CONTACT."SBU_NO" = SBU_LOOK."SBU_NO"
ORDER BY
SBU_LOOK."SBU_NAME" ASC
2.
SELECT
RESTART_LOG."REST_JOB_NAME", RESTART_LOG."JOB_CLASS_NO", RESTART_LOG."SBU_NO", RESTART_LOG."RESOLVED_BY_NO",
RES_BY_LOOK."RESOLVED_BY_NAME",
JOB_CLASS_LOOK."JOB_CLASS_NAME",
SBU_LOOK."SBU_NAME"
FROM
"PHL_OWNER"."RESTART_LOG" RESTART_LOG,
"PHL_OWNER"."RES_BY_LOOK" RES_BY_LOOK,
"PHL_OWNER"."JOB_CLASS_LOOK" JOB_CLASS_LOOK,
"PHL_OWNER"."SBU_LOOK" SBU_LOOK
WHERE
RESTART_LOG."RESOLVED_BY_NO" = RES_BY_LOOK."RESOLVED_BY_NO" AND
RESTART_LOG."JOB_CLASS_NO" = JOB_CLASS_LOOK."JOB_CLASS_NO" AND
RESTART_LOG."SBU_NO" = SBU_LOOK."SBU_NO"
ORDER BY
RES_BY_LOOK."RESOLVED_BY_NAME" ASC,
JOB_CLASS_LOOK."JOB_CLASS_NAME" ASC,
SBU_LOOK."SBU_NAME" ASC
3.
SELECT
RESTART_LOG."REST_JOB_NAME", RESTART_LOG."JOB_CLASS_NO", RESTART_LOG."SBU_NO", RESTART_LOG."RESOLVED_BY_NO",
SBU_LOOK."SBU_NAME",
RES_BY_LOOK."RESOLVED_BY_NAME",
JOB_CLASS_LOOK."JOB_CLASS_NAME"
FROM
"PHL_OWNER"."RESTART_LOG" RESTART_LOG,
"PHL_OWNER"."SBU_LOOK" SBU_LOOK,
"PHL_OWNER"."RES_BY_LOOK" RES_BY_LOOK,
"PHL_OWNER"."JOB_CLASS_LOOK" JOB_CLASS_LOOK
WHERE
RESTART_LOG."SBU_NO" = SBU_LOOK."SBU_NO" AND
RESTART_LOG."RESOLVED_BY_NO" = RES_BY_LOOK."RESOLVED_BY_NO" AND
RESTART_LOG."JOB_CLASS_NO" = JOB_CLASS_LOOK."JOB_CLASS_NO"
ORDER BY
RES_BY_LOOK."RESOLVED_BY_NAME" ASC,
JOB_CLASS_LOOK."JOB_CLASS_NAME" ASC,
SBU_LOOK."SBU_NAME" ASC
I'd modify the first statement (to see if it works) like:
SELECT
A.DATE_TIME, A.CONTACT_ID, A.NO_TAPES_REQ, A.EMERGENCY_Y_N, A.ENTERED_BY, B.CONTACT_NAME, C.SBU_NAME
FROM
PHL_OWNER.RESTORE_LOG A,
PHL_OWNER.CONTACT B,
PHL_OWNER.SBU_LOOK C
WHERE
A.CONTACT_ID = B.CONTACT_ID AND
B.SBU_NO = C.SBU_NO
ORDER BY
C.SBU_NAME ASC
Getting rid of One condtional (that was duplicated) and all double quotes. If you're connecting (to the database) as someone who has read access to all three tables I'd also get rid of "PHL_OWNER." In the nickname section. I've also used a much shorter name, "A", in place of RESTORE_LOG because I find that table names, as a nickname, often make the SQL statement unreadable.
If it still doesn't work try each of the following to see which part is causing the problem.
1)SELECT
A.DATE_TIME, A.CONTACT_ID, A.NO_TAPES_REQ, A.EMERGENCY_Y_N, A.ENTERED_BY
FROM
PHL_OWNER.RESTORE_LOG A
2) SELECT
B.CONTACT_NAME
FROM
PHL_OWNER.CONTACT B
3)SELECT
C.SBU_NAME
FROM
PHL_OWNER.SBU_LOOK C
ORDER BY
C.SBU_NAME ASC
Post the query, there may be something wrong with the syntax...