formula

[Former Member]

{'2009_'.Order Date}<>maximum{'2009_'.Order Date}

I tried to use this formula to surpress details

It doesn't work

What am I doing wrong.

I only want to see the records with the most recent order date. I don't want to use grouping

[Former Member · Accepted solution]

The option I could think of is TOP N(Bottom N where N=1) using Group sort expert.

HTH

[Former Member · Accepted solution]

You can't use a summary function formula in record selection, so you have to push it out to a custom SQL command:

Select top 1 <row data>
from TABLE
where <conditions>
SORT BY dateobject DESC

This sorts your data at the database in order of most to least recent, then returns only the top or most recent row.

Fuskie

Who hopes this helps...

[Former Member · Accepted solution]

Thanks again for your help.

I tried the following:

SELECT top 1 `'2009_'`.`Order Date`

FROM `'2009$'` `'2009_'`

where `'2009_'`.`Order Date` is not null

SORT BY `'2009_'`.`Order Date` desc

I am getting an error message:

Database Connection Error 'DAO Error Code OxcO3

Source DAO.Database

Description : Syntax error (missing operator) in query expression

'`'2009_'.Order Date is not null

SORT BY `'2009_'.`Order Date ` desc'.'

Can you help?

Thanks

[Former Member · Accepted solution]

My fault. Try ORDER BY instead of SORT BY.

Fuskie

Who slaps himself 3 times...

[Former Member · Accepted solution]

Thanks again!

That helped.

I am using the add command to place the sql statement is that correct?

My result set still returns all of the records.

Not just the top 1

[Former Member · Accepted solution]

Yes, Add Command. You need to replace your existing table linking with the SQL Query, not just add it.

Fuskie

Who notes with the TOP 1, it should be impossible to get more than one row...

[Former Member · Accepted solution]

Thanks again

I guess I am missing something

The report return the most recent order for the last order placed.

I'd like to see the last order placed for each firm in desc order by date

[Former Member · Accepted solution]

OK, then use the custom SQL command to just replace the order table in the report. Assuming your ORDER table has a FIRM_ID, ORDER_ID and a ORDER_DATE, try this:

SELECT FIRM_ID, ORDER_ID, ORDER_DATE, <any other order information you need from the table>
FROM ORDER
WHERE ORDER_DATE = (select order_date from order_sub where (FIRM_ID = order_sub.firm_id)  and (ORDER_DATE = maximum(order_sub.order_date))

If I did this right, your SQL command runs a sub-query that determines the most recent order date for the specified firm, then limits the main query to rows for the firm with that order date. Now you will still get multiple rows if the firm has more than one order on its most recent order date. You can get around this by dealing with the Order # instead of date, since the Order ID should be unique.

SELECT FIRM_ID, ORDER_ID, ORDER_DATE, <any other order information you need from the table>
FROM ORDER
WHERE ORDER_DATE = (select order_date from order_sub where (FIRM_ID = order_sub.firm_id)  and (ORDER_ID = maximum(order_sub.order_id))

This should return the most recent order for a firm, period.

Fuskie

Who finds himself writing much more custom SQL these days than he did a dozen years ago when he was first starting with Crystal Reports...

[Former Member · Accepted solution]

Thanks once again for yor gracious assistance.

I Tried the following:

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` ,

`'2009_'`.`Appraiser `,

`'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub `'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`))

It returns the following error:

Description Missing ),} or item in query expression

'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub `'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`))'.'

what am I doing wrong?

[Former Member · Accepted solution]

>

> what am I doing wrong?

You listened to me. Looks like I left off a close parenthesis from my example:

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` ,

`'2009_'`.`Appraiser `,

`'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub `'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

Fuskie

Who notes the third close parenthesis to complete the select sub-query...

[Former Member · Accepted solution]

I do appreciate your help

I am still getting an error.

Syntax error in query expression

'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub `'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

[Former Member · Accepted solution]

I think you are missing a period after order_sub."2009_".'Firm ID':

'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub.`'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

Fuskie

Who notes proof reading queries is a good exercise to become more familiar with query development...

[Former Member · Accepted solution]

Thanks,

I may be getting closer

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` ,

`'2009_'`.`Appraiser `,

`'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

where

'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub.`'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

Syntax error (missing operator) in query expression ''2009_'`.`'.'

[Former Member · Accepted solution]

I tried adding a ` before the '2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from order_sub where (`'2009_'`.`Firm ID`=order_sub.`'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

Now I get this error

The Microsoft Jet database engine could not find the object 'order_sub'. Make sure the object exists and that spell its name and path correctly.'

[Former Member · Accepted solution]

That's because you did not specify where order_sub came from:

select `Order Date` from `'2009_'` as order_sub

Fuskie

Who notes you cannot specify an alias in a SQL query without identifying the table being aliased...

Edited by: Fuskie on Jan 25, 2010 4:44 PM

[Former Member · Accepted solution]

I want to award you 1000 points for your effort.

I am still getting an error:

This what I used

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` ,

`'2009_'`.`Appraiser `,

`'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

where

`'2009_'`.`Order Date`=(select `'2009_'`.`Order Date` from `'2009$'` `'2009_'` as order_sub where (`'2009_'`.`Firm ID`=order_sub.`'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))

Syntax error in query expression

'2009_'`.`Order Date` from `'2009$'` `'2009_'` as order_sub where (`'2009_'`.`Firm ID`=order_sub.`'2009_'`.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`'2009_'`.`Order Date`)))','

[Former Member · Accepted solution]

Try this.

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` , `'2009_'`.`Appraiser `, `'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`Order Date`=(select `order_sub.`Order Date` from `'2009$'` order_sub where (`'2009_'`.`Firm ID`=order_sub.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`Order Date`)))

Fuskie

Who hopes this works...

[Former Member · Accepted solution]

I am still getting an error.

when I used

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` , `'2009_'`.`Appraiser `, `'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`Order Date`=(select `order_sub.`Order Date` from `'2009$'` order_sub where (`'2009_'`.`Firm ID`=order_sub.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`Order Date`)))

Description Missing ),} or item in query expression `'2009_'`.`Order Date`=(select `order_sub.`Order Date` from `'2009$'` order_sub where (`'2009_'`.`Firm ID`=order_sub.`Firm ID`) and (`'2009_'`.`Order Date` = maximum(order_sub.`Order Date`)))

','

[Former Member · Accepted solution]

Try this.

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` , `'2009_'`.`Appraiser `, `'2009_'`.`City`, `'2009_'`.`Active`
FROM `'2009$'` `'2009_'`
WHERE `'2009_'`.`Order Date`=(select "order_sub".`Order Date` from `'2009$'` "order_sub" where (`'2009_'`.`Firm ID`="order_sub".`Firm ID`) and (`'2009_'`.`Order Date` = maximum("order_sub".`Order Date`)))

Fuskie

Who really hates reading SQL queries with quotes enabled...

[Former Member · Accepted solution]

I can't thank you enough for your help.

I am still getting an error.

I used

SELECT `'2009_'`.`Firm ID`, `'2009_'`.`Order #`, `'2009_'`.`Order Date` , `'2009_'`.`Appraiser `, `'2009_'`.`City`, `'2009_'`.`Active`

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`Order Date`=(select "order_sub".`Order Date` from `'2009$'` "order_sub" where (`'2009_'`.`Firm ID`="order_sub".`Firm ID`) and (`'2009_'`.`Order Date` = maximum("order_sub".`Order Date`)))

Returns the following error

Description : Invalid use of '.','!', or ()' in query expression 'order_sub". `Order Date`','

[Former Member · Accepted solution]

Try using MAX instead of Maximum.

Fuskie

Who refrequently confuses Crystal and SQL syntax, but if that is not the problem suggests a fresh pair of SQL eyes on the problem as he can't find anything else wrong...

[Former Member · Accepted solution]

Thanks for your effort.

I got the same error as before.

Thanks again. I wish I could get assistane from Business Objects. I feel that what I want to do should be much easier.

Just need to have to figure out how to ask the correct question.

[Former Member · Accepted solution]

The issue does not seem to be one of Crystasl Reports but of SQL Query syntax. That is where you need to seek additional assistance.

Fuskie

Who is sorry he could not do more...