Data Connectivity: Left Outer Join Drama

Print Page | Close Window

Left Outer Join Drama

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17739
Printed Date: 18 May 2024 at 11:02am

Topic: Left Outer Join Drama

Posted By: cmpgeek
Subject: Left Outer Join Drama
Date 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

Replies:

Posted By: DBlank
Date 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

Posted By: cmpgeek
Date 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

Posted By: DBlank
Date 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

Posted By: cmpgeek
Date Posted: 11 Oct 2012 at 10:15am

I am looking up more information about command objects now... thanks :)

-------------
Nomi
CR 10
Oracle 9i

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: DBlank
Date 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.

Posted By: cmpgeek
Date 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

Posted By: DBlank
Date 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.

Posted By: cmpgeek
Date 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

Posted By: hilfy
Date Posted: 15 Oct 2012 at 4:52am

Don't use the IN ... TO... syntax - instead try Between...and...

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: cmpgeek
Date 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

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: cmpgeek
Date 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

-Dell

-------------
Nomi
CR 10
Oracle 9i

Posted By: cmpgeek
Date 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 hilfy

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

-------------
Nomi
CR 10
Oracle 9i

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Print Page | Close Window