Crystal Reports 8.5 Stored Procedures

[cmetz]

I have written a procedure and I need some help.
I want to list a bunch of routes but also
have two different destination locations.
Here is part of the procedure:

routestops.routecode = 'NEC5-1'or routestops.routecode = 'NEC2-1' or routestops.routecode = 'NEC3-1'
or routestops.routecode = 'NEC7-1')
and customernumber='2086' and sequence <> '1'

I want to add customernumber='BOA014' so I want it to look like this:

and customernumber='2086' and customernumber='BOA014' and sequence <>'1'

It is not working, do I need brackets or something?

Thanks

 

[morriskc]

try:

routestops.routecode in ('NEC5-1','NEC2-1','NEC3-1','NEC7-1')
and customernumber in ('2086','BOA014')
and sequence <>'1'

 

[cmetz]

Thanks so much, that worked perfectly.

 

[cmetz]

What would I write in the procedure to then
group by the customer numbers?

Thanks

 

[morriskc]

In your procedure, you will need to add a group by statement.

Group By customernumber

Keep in mind that the other fields in your proc's select statement will either need to be include in the group by or have some kind of compute (MAX, MIN, SUM, AVG) performed within your select statement.

For example:

Select
customernumber
,MAX(customername) customername
,SUM(miles) miles
,routecode
FROM table
WHERE
Group BY customernumber, routecode

This example will actually return a row for each customer number and routecode. I MAX on customername because that should not change from trip to trip.

Hope this helps.

KCM