Help Ranking Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help Ranking Results

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2010-01-15 : 11:10:53

Hello all!

This is probably going to seem like one of those "why do it that way?" questsion. I'm writing a query to pull data into Crystal Reports (version 10), and for some reason, Crystal coughs up an error whenever I try to use a CASE in my ORDER BY clause. So, I'm having to come up with a workaround...

I have a data table named HISTORY with the following fields:
VENDORKEY
VENDORNAME
INVOICENUMBER
INVOICEDATE
LINENUMBER
LINEAMOUNT

I would like to do it this way:


SELECT VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE, SUM(LINEAMOUNT)
FROM HISTORY
GROUP BY VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE
ORDER BY CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
ELSE SUM(HISTORY.LINEAMOUNT) DESC
END

However, as I said, Crystal coughs up an error message whenever I try to do this. Is there a way to add 3 result fields to the SELECT query so I get the VENDORKEY, VENDORNAME and SUM(HISTORY.LINEAMOUNT) ranked as integers? I know there must be a way to create rankings, but I don't know if there's a good way to rank 3 fields in different orders.

Also, if anyone knows how to get Crystal to simply run a query where the sort order is set by a CASE, I'm open to suggestions. When I try to ORDER BY (CASE CLAUSE) HISTORY.VENDORKEY or (CASE CLAUSE) HISTORY.VENDORNAME, it tells me the column isn't in my GROUP BY clause (when it clearly is). When I try to ORDER BY the SUM statement, it says "Expression not row based in ORDER BY".

Finally, the SUM(HISTORY.LINEAMOUNT) I'm trying to group by needs to be at the VENDORKEY level (all the line amounts for all the invoices for a particular vendor... I'm not trying to rank the individual invoices, just the total by vendor).

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-01-15 : 11:36:38

How about the RANK() function in SQL 2005.

Are you writing the queries in Crystal? Why not create stored procs and then all Crystal ever sees is a SP call?

SBLatta
Starting Member

33 Posts

Posted - 2010-01-15 : 11:51:03

My only connection to the database is via ODBC, so writing anything which is stored on the server isn't going to be an option. I'm just a semi-advanced user, not an admin.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

SBLatta
Starting Member

33 Posts

Posted - 2010-01-15 : 11:54:39

Rick - Regarding RANK(), I just tried it and Crystal said I was trying to use an unrecognized scalar or user-defined function. The database is running Pervaisive SQL, so not all the same functions as MSQL are there.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 12:11:42

I would expect
"ORDER BY CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
ELSE SUM(HISTORY.LINEAMOUNT) DESC"
to cause SQL, let along Crystal!, to give you an error for this. You CASE has (presuambly) VENDORKEY & VENDORNAME as string data and LINEAMOUNT as a numeric.

Can you do


ORDER BY CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
ELSE 'xxx'
END,
SUM(HISTORY.LINEAMOUNT) DESC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 12:15:08

And..
CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
is valid??

I always thought we needed to use WHEN??

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 12:50:00

Well spotted! But if this is Pervaisive SQL I suppose we may all be way off beam?!

SBLatta
Starting Member

33 Posts

Posted - 2010-01-15 : 13:07:31

quote:
Originally posted by vijayisonly

And..
CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
is valid??

I always thought we needed to use WHEN??

WHEN is correct. One of these days I'll learn to copy & paste to the forum instead of trying to type from memory.

SBLatta
Starting Member

33 Posts

Posted - 2010-01-18 : 08:11:55

quote:

Originally posted by Kristen

Can you do


ORDER BY CASE
WHERE {?Sortby} = "K" THEN HISTORY.VENDORKEY
WHERE {?Sortby} = "N" THEN HISTORY.VENDORNAME
ELSE 'xxx'
END,
SUM(HISTORY.LINEAMOUNT) DESC

I thought that one was going to work, but alas... it is still giving me the "Expression Not Row Based In Order By" when I add the SUM line.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 09:29:50

OK, next attempt


SELECT VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE, SUM(LINEAMOUNT),
       {?Sortby} AS MySortBy
FROM HISTORY
GROUP BY VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE
ORDER BY CASE
WHEN MySortBy = "K" THEN HISTORY.VENDORKEY
WHEN MySortBy = "N" THEN HISTORY.VENDORNAME
ELSE 'xxx'
END,
SUM(HISTORY.LINEAMOUNT) DESC

you might need to add {?Sortby} to the GROUP BY clause too.

Do you need quotes around {?Sortby} ? I don't know whether Crystal puts them in for you, or not.

This will also output {?Sortby} in the resultset. If that's a problem we'll have to put a wrapping SELECT statement around the whole lot.

SBLatta
Starting Member

33 Posts

Posted - 2010-01-18 : 09:41:12

Still no luck...

When the SUM list left in, it tells me "Expression Not Row Based In Order By" again, and now with that left off, it tells me I have "Invalid Column Name: 'N'"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 09:48:19

What happens when you try this?

SELECT * FROM
(
SELECT VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE, SUM(LINEAMOUNT) as LINEAMOUNT,
       {?Sortby} AS MySortBy
FROM HISTORY
GROUP BY VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE
) AS T
ORDER BY CASE
WHEN MySortBy = "K" THEN HISTORY.VENDORKEY
WHEN MySortBy = "N" THEN HISTORY.VENDORNAME
ELSE 'xxx'
END,
LINEAMOUNT DESC

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 09:50:33

It is permitted to give a calculated column (in the Select list) an Alias Name, and use that name in the Order By - in case that helps you; I'm clutching at straws, but its hard to know what "mangling" Crystal is doing


SELECT ... SUM(LINEAMOUNT) AS MyTotal, ...
...
ORDER BY ... MyTotal ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 10:04:25

quote:
Originally posted by Kristen

It is permitted to give a calculated column (in the Select list) an Alias Name, and use that name in the Order By - in case that helps you; I'm clutching at straws, but its hard to know what "mangling" Crystal is doing

SELECT ... SUM(LINEAMOUNT) AS MyTotal, ...
...
ORDER BY ... MyTotal ...

You can use that without CASE expression. If you have decided to use CASE expression over the alias name, either use derived table or the expression itself. You can't use it at the way OP wanted

Madhivanan

Failing to plan is Planning to fail

SBLatta
Starting Member

33 Posts

Posted - 2010-01-18 : 10:19:12

quote:

Originally posted by madhivanan

What happens when you try this?

SELECT * FROM
(
SELECT VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE, SUM(LINEAMOUNT) as LINEAMOUNT,
       {?Sortby} AS MySortBy
FROM HISTORY
GROUP BY VENDORKEY, VENDORNAME, INVOICENUMBER, INVOICEDATE
) AS T
ORDER BY CASE
WHEN MySortBy = "K" THEN HISTORY.VENDORKEY
WHEN MySortBy = "N" THEN HISTORY.VENDORNAME
ELSE 'xxx'
END,
LINEAMOUNT DESC

"Expression Not Row Based In Order By" happens.

I found a T-SQL article on MSDN that seemed to indicate


ORDER BY
...
COMPUTE SUM(HISTORY.LINEAMOUNT) BY VENDORKEY DESC

might be an option, however COMPUTE doesn't seem to be a valid command in Pervaisive SQL. I'm looking at the Pervaisive documentation now for an alternative/alternate command name. I figured I'd post the above anyway since no one had mentioned using COMPUTE in the ORDER BY clause and it seems like it would be handy.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 10:21:36

I think we had decided to use the SUM(LINEAMOUNT) in the ORDER BY as a separate column?

(given the error "Expression Not Row Based In Order By" I thought maybe an Alias would work around whatever mangling Crystal is doing).

I wasn't a big fan of Crystal 10 years ago. Not used it since, but I'm still just as jaundiced. Thought I'd just mention that here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources