INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Right Outer Join syntax error in "Command" object

Right Outer Join syntax error in "Command" object

(OP)
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

RE: Right Outer Join syntax error in "Command" object

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:

CODE

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/crystal-reports/blog/...

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Right Outer Join syntax error in "Command" object

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

RE: Right Outer Join syntax error in "Command" object

(OP)
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' }

RE: Right Outer Join syntax error in "Command" object

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
www.decisionfirst.com

RE: Right Outer Join syntax error in "Command" object

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

RE: Right Outer Join syntax error in "Command" object

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
www.decisionfirst.com

RE: Right Outer Join syntax error in "Command" object

(OP)
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.

RE: Right Outer Join syntax error in "Command" object

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/crystal-reports/blog/... 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
www.decisionfirst.com

RE: Right Outer Join syntax error in "Command" object

(OP)
Great! Thanks.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close