Right Outer Join syntax error in "Command" object

[capronton]

I'm trying to pull all the records in the headway_log table whether there is a matching record in incident_log table or not.

Can someone tell me why I get an error in the right outer join below. Thanks for any assistance.

select a.sched_version, b.sched_version,incident_log_id,a.transit_date_time,a.incident_date_time,a.sched_time,
g.incident_name,g.incident_desc,h.direction_description, hl.scheduled_headway
from incident_log a, trip_timepoint b, trip c,vehicle_schedule d, timepoint_name e, timepoints f,incident_types g,
direction_codes h, headway_log hl
where
a."incident_date_time" >= {ts '2016-03-01 04:00:00' }
AND a."incident_date_time" < {ts '2016-12-03 04:00:00' }
and a.sched_version <> 0
--and a.sched_version <> b.sched_version
and a.trip_id = b.trip_id
and a.tp_id = b.tp_id
and a.trip_id = c.trip_id
and b.sched_version = c.sched_version
--and a.sched_version <> c.sched_version
and a.route_id = c.route_id
and (a.incident_log_id % 2) = 0
and a.sched_version = d.sched_version
and a.tp_id = e.tp_id
and f.tp_id = e.tp_id
and a.incident_type = g.incident_type
and c.end_tpid <> a.tp_id
and h.direction_code_id = a.direction_code_id
and RIGHT OUTER JOIN hl on a.incident_log_id = hl.headway_log_id
ORDER BY a.transit_date_time, a.current_route_id, h.direction_description, b.seq_num, a.tp_id, a.incident_date_time

 

[hilfy]

First off, your syntax is bad - you are trying to combine doing joins in the where clause with a specified outer join, which can't be in the where clause. If you're going to use an outer join, you have to use the explicit join language for all of the joins.

Secondly, what you're trying to do with the right-outer join will not give you what you're looking for. I would make the headway_log table the main table of the query and then left join everything from there. I also tend to put a lot of what would be the where criteria into the joins - it tends to make the queries faster - but most databases will only let you use one pair of tables in the individual join clauses. I've rewritten your query using these rules and it comes out like this:

select 
  a.sched_version, 
  b.sched_version,
  incident_log_id,
  a.transit_date_time,
  a.incident_date_time,
  a.sched_time,
  g.incident_name,
  g.incident_desc,
  h.direction_description, 
  hl.scheduled_headway
from headway_log hl
  left outer incident_log a 
    on hl.headway_log_id = a.incident_log_id
      and (a.incident_log_id % 2) = 0
      and a.sched_version <> 0
      and a."incident_date_time" >= {ts '2016-03-01 04:00:00' }
      and a."incident_date_time" < {ts '2016-12-03 04:00:00' }
  left outer join trip_timepoint b 
    on a.trip_id = b.trip_id
      and a.tp_id = b.tp_id
  left outer join trip c
    on a.trip_id = c.trip_id
      and a.route_id = c.route_id
	  and a.tp_id = c.end_tpid
  left outer join vehicle_schedule d 
    on a.sched_version = d.sched_version
  left outer join timepoint_name e 
    on a.tp_id = e.tp_id
  left outer join timepoints f
    e.tp_id = f.tp_id
  left outer join incident_types g
    on a.incident_type = g.incident_type
  left outer join direction_codes h
    on a.direction_code_id = h.direction_code_id
where b.sched_version = c.sched_version
ORDER BY 
  a.transit_date_time, 
  a.current_route_id, 
  h.direction_description, 
  b.seq_num, 
  a.tp_id, 
  a.incident_date_time

Also, for more information about working with commands and parameters, please see my blog post here:

http://scn.sap.com/community/crysta...ices-when-using-commands-with-crystal-reports

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[capronton]

Thank you Dell for this additional information on how to make joins more efficient.

 

[capronton]

Currently my view pulls the entire data set even if the user only queries for one day of data and it takes about 10 minutes to run the report.
Is there a way I can set the view up so that it does not go through every record in the data set just to pull one days worth of data. The statement below
pulls the entire data set even if the user only queries for one day. Thanks for any assistance.

a."incident_date_time" >= {ts '2016-03-01 04:00:00' }
AND a."incident_date_time" < {ts '2016-12-03 04:00:00' }

 

[hilfy]

Does the headway_log table have any date fields in it that you can use for filtering?

Also, take out the explicit time-stamps that you have and replace them with parameters. In order for this to work correctly, you MUST create the parameters in the Command Editor. Your syntax would then be something like this (you don't need the quotes - I forgot to take them out above...):

a.incident_date_time >= {?Start Date}
and a.incident_date_time < {?End Date}

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[capronton]

Thanks, but I am still pulling in all the records rather than what the User sets in the Date parameter.

 

[hilfy]

All of the incident_log records? Or is it something else?

You indicated in your first post that you want all of the headway_log records, regardless of whether there are incident_log records in the requested time frame. That's what this should give you.

Did you change the command to use the date parameters?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[capronton]

Yes, I took the date filter out of the Command Object and added to my Select Expert parameters as shown below.

({Command.direction_description} in {?Direction} or " ALL" in {?Direction}) and
({Command.tp_id} in {?tpNumber} or -99 in {?tpNumber}) and
{Command.current_route_id} = {?CurrentRoute} and
{Command.incident_date_time} = {?IncidentDate} and
{Command.description} = {?ServiceType}

Yes, I need all even number incident_log records between the incident_date_time that the user selects in the parameter above.

 

[hilfy]

If you put the filter in the Select Expert, then it will NOT get pushed down to the database. The filter criteria MUST be in the command in order for it to be pushed down to the database for processing. See

http://scn.sap.com/community/crysta...ices-when-using-commands-with-crystal-reports

for more information about how commands work and how to use parameters with commands.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[capronton]

Great! Thanks.