Crystal 8.5: SQL Expression: ORDER BY 1

[greengo204]

HI,

I'm currently trying to combine 2 crystal reports, One report had a load of subreports on and the other has a crosstab. So far i've managed to replace all the sub reports and add them to the crosstab as SQL Expressions except one.

Problem:

The subreport that im trying to replace has the following properties:

Link:
po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id

Value Displayed:
po_fi_cnrct.cnrct_amnt

Subreport Sort:
po_cmpny.cmpny_cd ASC,
po_fi_cnrct.start_dt DESC

So far i have the below SQL Expression:

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id
)

which works fine but with out the sort it does not consistently give me the latest contract value. When i add the ORDER BY section i get errors, does the mean that you cannot use the ORDER BY property in SQL Expressions or is my syntax incorrect?

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id
ORDER BY
    po_cmpny.cmpny_cd ASC,
    po_fi_cnrct.start_dt DESC
)

Could anyone confirm if the ORDER BY property can be used in SQL Expressions or if my syntax is incorrect. Any other suggestion are also welcomed!


Cheers.

 

[Madawc]

Try leaving out the ASC. I think ascending is the default and need not be specified.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[greengo204]

thanks for the suggestion i have previously tried:

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id
ORDER BY
    po_fi_cnrct.start_dt DESC
)

with the same syntax error.

alternatively i could link the 'popesopesdba.po_fi_cnrct po_fi_cnrct' table in the main SQL and use a formula instead, but when i have attempted this method Crystal has not allowed me to use the formula in the crosstab. Dont suppose anyone could suggest a way to use a formula to replicate what my SQL Expression? (I have to be able to use the formula in the crosstab.)

I would like to use the SQL Expression method but i would take any suggestion at the moment...

 

[Madawc]

What syntax error? It definitely is possible to used 'Order By', I've done it lots of times.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Please clarify whether you are using SQL expressions or commands. A SQL expression returns only one value (one value per faux group condition, at least), and therefore an "Order By" clause doesn't make sense.

-LB

 

[lbass]

Oops, I see you are using 8.5, so you must be referring to a SQL expression--unless you are modifying the "show SQL query" directly.

-LB

 

[greengo204]

Madwc: syntax error "A syntax error has occured"

I'm using an Informix database if that makes a difference.

lbass: SQL Expressions. I think you are right about the one value per faux group condition, as i think my results are returning more than one value.

Maybe if i give more detail it may help. I am trying to pull the most recent contract value (by start date) from the database for the customer. what may be causing a problem is that a customer can have more than one contract running at the same time, with the same start date, in which case i would want to return the latest contract id.

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id
ORDER BY
    po_fi_cnrct.start_dt DESC,
    po_fi_cnrct.cnrct_id DESC
)

but i think the above results will not scalar as the SQL is still going to return multiple values, does anyone know what function/condition in the SELECT or WHERE part can i use to select the 1st record of the returned results? Something like the 'next' function in crystal would be useful...

i'm trying something like:

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id,
    po_fi_cnrct.start_dt = max(po_fi_cnrct.start_dt),
    po_fi_cnrct.cnrct_id = max(po_fi_cnrct.cnrct_id) 
ORDER BY
    po_fi_cnrct.start_dt DESC,
    po_fi_cnrct.cnrct_id DESC
)

As you could have geussed this retuned a Syntax Error. My understanding of SQL is that my above WHERE clause is querying the entire DB and not just the customers records? Is it possible to adapt the above WHERE clase to only query the customers records?

I currently have both 8.5 and XI on my machine so if it is staight forward to to do this with XI SQL commands, i will attempt using that as an alternative, but a solution in 8.5 would be alot better as the majority of people at my work run 8.5.

 

[lbass]

Instead, use SQL expressions to return the maximum date and the maximum ID, and then use them to limit the data in your main report.

-LB

 

[greengo204]

Was not entirely sure what you suggestion meant as i would be running this report for a large amount of customers so i was a bit unsure how to pass the MAX's to limit the data in the report.

I have found a SQL solution though which is working in the Crosstab:

(
SELECT
    po_fi_cnrct.cnrct_amnt
FROM
    popes:popesdba.po_fi_cnrct po_fi_cnrct
WHERE
    po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id AND
    po_fi_cnrct.cnrct_id in (
                            SELECT
                                max(po_fi_cnrct_1.cnrct_id)
                            FROM
                                popes:popesdba.po_fi_cnrct po_fi_cnrct_1
                            WHERE
                                po_cmpny.cmpny_id = po_fi_cnrct_1.cmpny_id
                            )
)

Although the above SQL does not mention the MAX date, the Max contract ID is working sufficiently

Thanks for the suggestions.