| Author |
Message |
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
   Topic: Left Outer Join Drama
Posted: 11 Oct 2012 at 8:37am |
I am writing a report that is like a daily schedule. It shows the name of the room and tells you when there are appts already scheduled in that room by the hour. The problem is that if there is a room with no appts at all scheduled - the room does not show up on the report. I expected this to happen; but would still like to find away around it if possible.
I created a Left Outer Join from the room table to the appt table, but it is not working correctly because I also need to specify a date range which is from a field in the appt table.
I have been searching all over the web for an answer and I saw one where someone said to adjust the SQL statement, but I don't know how to do that. I have been working with Crystal for over 12 years, but my exposure to actual SQL language is very new and I don't even know where to start with this.
Any help would be greatly appreciated!
CR 10 / Oracle 9i
|
|
Nomi
CR 10
Oracle 9i
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 9:52am |
if you can use a command object you move your where clause into the join as an and statement
How it is now...
select a.field, b.field
from a left outer join b.room =a. room
where b.date in ?start to ?stop
Change it to...
select a.field, b.field
from a left outer join b.room =a. room and b.date in ?start to ?stop
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 11 Oct 2012 at 9:56am |
Are you talking about the Report Selection area of Crystal? That is the only place I know to write this coding... This is what I have now:
{PCE.PROC_DATE_DT} in {?Start Date} to {?End Date}
AND {PCE.SITE_ID} IN ["1","3"]
AND {ROOM.ROOM_TYPE} = "OR"
AND NOT({ROOM.ROOM_NAME} IN ["ENDO 01","ENDO 02","ENDO 03","ENDO 04","ENDO 05","MRI","Misc Room"])
There are SQL Expressions used in the report, but I am guessing that is not the same as a command object...
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 10:13am |
you would have to write a command object to alter the join type
A command object takes the place as the source table
Edited by DBlank - 11 Oct 2012 at 10:14am
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 11 Oct 2012 at 10:15am |
|
I am looking up more information about command objects now... thanks :)
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 12 Oct 2012 at 3:49am |
Or you can change your selection criteria to account for possible null values in the schedule table. It would look something like this: (IsNull({PCE.PROC_DATE_DT}) or ({PCE.PROC_DATE_DT} in {?Start Date} to {?End Date}
AND {PCE.SITE_ID} IN ["1","3"]))
AND {ROOM.ROOM_TYPE} = "OR"
AND NOT({ROOM.ROOM_NAME} IN ["ENDO 01","ENDO 02","ENDO 03","ENDO 04","ENDO 05","MRI","Misc Room"]) Note where I put the red parenteses - those are important to making this work correctly. -Dell
|
|
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Oct 2012 at 4:59am |
|
I could be wrong but I think this solution with the "isnull or" option will only work if the rooms that you wanted to include did not have any scheduled appoitments outside the range. Once the join finds it the there would be no null instance in the data set.
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 12 Oct 2012 at 7:22am |
I just tried hilfy's suggestion, but it did not change anything...
DBlank - where would I put the command object?
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Oct 2012 at 7:38am |
|
in the data base , database expert, in your report source type you have the option of add command.. double click on it and this is where you write your sql statment to replace (or add to) your report data set source.
|
|
IP Logged |
|
cmpgeek
Newbie
Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
|
Posted: 12 Oct 2012 at 8:07am |
I keep getting this error:
crystal reports failed to open a rowset details: 42000:[Microsoft][ODBC driver for Oracle][Oracle]ORA-00933: SQL command not properly ended
This is what I have entered:
SELECT "ROOM"."ROOM_ID",
"PATIENT_CARE_EVENT"."OR_NUM"
FROM
"ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT"
ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
I know it has to be exact - do I have something out of place that I am not seeing?
|
|
Nomi
CR 10
Oracle 9i
|
|
IP Logged |
|
|
|
