| Author |
Message |
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
   Posted: 15 Oct 2012 at 4:52am |
Don't use the IN ... TO... syntax - instead try Between...and... -Dell
|
|
|
 IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 15 Oct 2012 at 10:08am |
when I make that change it gives me:
Failed to open a rowset.
Details: 42S02:[Microsoft][ODBC driver for Oracle][Oracle] ORA-00942: table or view does not exist
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 15 Oct 2012 at 10:42am |
table or view does not exist Either the table you're using in the database doesn't exist or the database login you're using for the report doesn't have access to it. I would use something like Toad or SQL*Plus to run the query and test it. In the database, instead of using the Crystal parameters ("{?...}") put sample dates in the following format: 'dd-MON-yyyy' - for example '01-JAN-2012' (Yes, you'll need to include the single-quotes!) This way you're running the query directly against the database. Tweak it until you have it working there and then paste it into your report and replace the sample dates with your parameters. -Dell
|
|
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 15 Oct 2012 at 10:46am |
Also, I would modify the query to something like this: SELECT rm.ROOM_ID,
pce.OR_NUM FROM ROOM as rm LEFT OUTER JOIN PATIENT_CARE_EVENT as pce ON pce.OR_NUM=rm.ROOM_ID AND pce.PROCEDURE_DATE_DT between {?Start Date} and {?End Date} -Dell |
|
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 16 Oct 2012 at 7:49am |
I am trying to get access to the PL/SQL developer we have on one of our servers... This will be my next step if I am able to get the needed access. Thanks...
Originally posted by hilfy
I would use something like Toad or SQL*Plus to run the query and test it. In the database, instead of using the Crystal parameters ("{?...}") put sample dates in the following format: 'dd-MON-yyyy' - for example '01-JAN-2012' (Yes, you'll need to include the single-quotes!) This way you're running the query directly against the database. Tweak it until you have it working there and then paste it into your report and replace the sample dates with your parameters.
-Dell
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 16 Oct 2012 at 7:50am |
When you have a second, could you explain the benefit of making this change? I just want to be able to follow everything and understand what I am doing... Thanks :)
Originally posted by hilfyAlso, I would modify the query to something like this:
SELECT
rm.ROOM_ID,
pce.OR_NUM
FROM ROOM as rm
LEFT OUTER JOIN PATIENT_CARE_EVENT as pce
ON pce.OR_NUM=rm.ROOM_ID AND
pce.PROCEDURE_DATE_DT between {?Start Date} and {?End Date}
-Dell
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 16 Oct 2012 at 8:21am |
A big part of it is the way you were using quotes - Crystal uses them by default, because some databases require them, but you really don't need them for Oracle. Also, I think a big part of your problem was in the way you had the table names set up in the From and Join statements. For example "ROOM"."ROOM" in Oracle means the "ROOM" table that is "owned" by the user "ROOM". By breaking it down and using "as" to alias the table, it makes it easier to read and corrects the invalid syntax from your query. -Dell
|
|
|
|
IP Logged |
|
|
|
