Ranking in stored procedure

[Gragi]

I'm trying to write a stored procedure in informix
I got the output like this

Name TotalSale
fff 3456.00
aaa 12345.00
ccc 3000.00
bbb 15800.00

now I want my out like

Name TotalSale Rank
aaa 12345.00 2
bbb 15800.00 1
ccc 3000.00 4
fff 3456.00 3

I did like
CREATE PROCEDURE "informix".totSales()
RETURNING char(60), DECIMAL, INTEGER;

define Name like a.name;
define TotalSale like b.amt;

FOREACH
SELECT TRIM(a.name), SUM(b.amt * b.qty)
INTO Name, TotalSale
FROM table a, table b, table c
WHERE c.link = b.link
AND c.id = a.id
GROUP BY 1
RETURN Name, TotalSale WITH RESUME;
END FOREACH;
End Procedure;

--- Executed like
EXECUTE PROCEDURE totSales()

/* Tried like this for ranking

FOREACH Rankcursor FOR but didn't work
let Rank = Rank + 1;
SELECT TRIM(a.name), SUM(b.amt * b.qty), Rank
INTO Name, TotalSale, Rank
FROM table a, table b, table c
WHERE c.link = b.link
AND c.id = a.id
GROUP BY 1
ORDER BY 2 DESC
RETURN Name, TotalSale, Rank WITH RESUME;
END FOREACH;
*/

Help would be appreciated.

Thank You,
Gragi

 

[Gragi]

Friends,
I got the solution thought it might help somebody else by sharing the solution.

CREATE PROCEDURE "informix".totSales()
RETURNING char(60), DECIMAL, INTEGER;

define Name like a.name;
define TotalSale like b.amt;
define Rank INTEGER;
let Rank = 0;

FOREACH Rankcursor FOR
let Rank = Rank + 1;
SELECT TRIM(a.name), SUM(b.amt * b.qty)
INTO Name, TotalSale
FROM table a, table b, table c
WHERE c.link = b.link
AND c.id = a.id
GROUP BY 1
ORDER BY 2 DESC
let Rank = Rank + 1;
RETURN Name, TotalSale, Rank WITH RESUME;
END FOREACH;

--- Executed like
EXECUTE PROCEDURE totSales()

Then I used this stored procedure as a input to my report(crystal) and then used Record sort expert on Expression 1(Name).

Gragi