Post #204,196
4/21/05 12:02:47 AM
|
How to do without subqueries
You have an order table where ordernum is the primary key.
You have a detail table listing escalations with foreign key to the ordernum. There can be multiple escalations per order.
Escalation table has a status column, enum('closed','open'). (There are actually more values, but these two are enough to demonstrate the issue.)
You need to run a report with one row per order, and a column that shows a checkmark to indicate if the order has any open escalations. Each order can have multiple escalations, or none. It may have some open and some closed. You want to be able to order the results by the value of that column.
You are using MySQL 4.0.18, which doesn't support subqueries.
We've come up with three ways to do this, none of which are good. All involve multiple passes through the data. Does anyone know a better way?
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #204,206
4/21/05 1:26:10 AM
|
SELECT DISTINCT t1.* FROM t1, t2 ...?
[link|http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html|http://dev.mysql.com...g-subqueries.html]
|
Post #204,210
4/21/05 2:21:36 AM
|
I don't understand your description
Order by what column? The number of open escalations? The number of open and closed escalations? Or some weird foreign key? In any case the problem sounds like an outer join that uses the fact that ORDER BY happens after a GROUP BY. I always have to look up the syntax for that. In Oracle I think it would be something like this: \n SELECT\n count(open.status) as count_escalations,\n count(closed.status) as count_closed,\n order.ordernum\n FROM tblorder order,\n tblescalation open,\n tblescalation closed\n WHERE order.ordernum = open.ordernum(+)\n AND order.orternum = closed.ordernum(+)\n AND open.status = 'Open'\n AND closed.status = 'Closed'\n GROUP BY order.ordernum\n ORDER BY count(open.status) DESC,\n count(closed.status) DESC,\n order.ordernum\n Oracle has a nonstandard outer join syntax that I'm using (and may have backwards). Use the official outer join syntax that I never remember and it should work in MySQL. If you have access to the equivalent of CASE in the SQL standard, then you only need to do the outer join once, and you can sum a CASE to do the calculations. The messy sum of a CASE may have to appear multiple places though. (In the order and in the select.) Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,246
4/21/05 9:34:14 AM
|
Reply from Drew
Hi. I'm Meerkat, I'll be your Drew-proxy-zIWT-poster this evening. This way he doesn't get caught up in Net Nanny sillines. Here's his reply:
\ufffd\ufffd\ufffdtbl_order
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffdorderdate | ordernum |
\ufffd\ufffd\ufffd
|---|
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd2005-01-01 | 123 |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd2005-01-02 | 124 |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd2005-01-03 | 125 |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd2005-01-04 | 126 |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffdtbl_escalation
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffdordernum | status |
\ufffd\ufffd\ufffd
|---|
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd124 | open |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd125 | closed |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd126 | open |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd126 | closed |
\ufffd\ufffd\ufffd
desired output:
\ufffd\ufffd\ufffdescalation status
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffdordernum | orderdate | open escalations |
\ufffd\ufffd\ufffd
|---|
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd124 | 2005-01-02 | X |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd126 | 2005-01-04 | X |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd123 | 2005-01-01 | |
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd125 | 2005-01-03 | |
\ufffd\ufffd\ufffd
Two out of three people wonder where the other one is.
|
Post #204,248
4/21/05 9:52:49 AM
4/21/05 9:59:22 AM
|
Re: Reply from Drew
select max(o.ordernum) ordernum,\n max(o.orderdate) orderdate,\n decode(max(e.status),NULL,' ','X') open_excalations\n from tbl_order o,\n tbl_escalation e\n where e.ordernum(+) = o.ordernum\n e.status(+) = 'open'\n group by o.ordernum, o.orderdate\n order by open_excalations desc I've tested this in Oracle. Darrell Spice, Jr. [link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass]\n[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
Edited by SpiceWare
April 21, 2005, 09:56:20 AM EDT
Edited by SpiceWare
April 21, 2005, 09:57:50 AM EDT
Edited by SpiceWare
April 21, 2005, 09:59:22 AM EDT
|
Post #204,378
4/21/05 8:37:47 PM
|
DECODE is Oracle-specific.
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,396
4/22/05 9:29:35 AM
|
It is, but it is easy to clone
I cloned it in postgres on a porting project awhile back.
However, it is not only Oracle specific, but in most cases it is EVIL since people abuse it to get out of creating reference tables.
"Whenever you find you are on the side of the majority, it is time to pause and reflect" --Mark Twain
"The significant problems we face cannot be solved at the same level of thinking we were at when we created them." --Albert Einstein
"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses." --George W. Bush
|
Post #204,399
4/22/05 9:54:09 AM
|
Are you sure? I checked MySQL before posting
I put decode in the search box at [link|http://www.mysql.com/|MySQL.com]. The first result was [link|http://dev.mysql.com/doc/maxdb/en/cf/63316bc03511d2a97100a0c9449261/content.htm|DECODE(x,y(i),...,z)] with sample code SELECT hno, price, DECODE (type,\n'single', 1, 'double', 2, 'suite', 3) room_code\n FROM room\n WHERE hno IN (40,50,60)
As such, I figured it supported decode and posted my example for Drew. Darrell Spice, Jr. [link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass]\n[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #204,416
4/22/05 11:16:56 AM
|
They may have added it but it is not standard
If they did add it, I don't know which version it would have been.
I know that when I went searching for it, I was pointed at a conversion from Oracle's DECODE to MySQL's CASE. (Which doesn't seem like it quite matches the SQL-92's standard for CASE either. Oh well.)
Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,423
4/22/05 11:35:10 AM
|
Seems to be specific to MaxDB
MaxDB is the databsse MySQL got when they partnered with SAP. It does have an Oracle like decode. MySQL has a function called decode, but it an encryption function that works opposite encode.
Jay
|
Post #204,435
4/22/05 12:14:55 PM
|
That's rather confusing
odd that the MaxDB version of Decode would come up first on a MySQL site search. Oh well.
Darrell Spice, Jr. [link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass]\n[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #204,255
4/21/05 10:25:00 AM
|
One solution
(SELECT distinct O.orderdate, O.ordernum, 'x' as OpenEscalations
from tbl_order O
INNER JOIN tbl_escalation E on O.ordernum = E.ordernum
where E.escalation = 'open')
UNION ALL
(SELECT distinct O.orderdate, O.ordernum, ' '
from tbl_order O
LEFT JOIN tbl_escalation E on O.ordernum = E.ordernum and E.escalation = 'open'
where E.escalation IS NULL)
order by OpenEscalations desc, ordernum
Not the neatest solution, but as long as the data you are looking at is not huge it should be fast.
Jay
|
Post #204,380
4/21/05 8:49:39 PM
|
FWIW this was the best I saw for our needs
The union did in fact get the exact right results. And was the clearest in what it was doing. Then someone observed that the main table was already an aggregate of results from several other tables, built just for running this one report. There are no other joins against it. The right way for us to do this is check and update (if necessary) the value of a flag in the main table every time we update an escalation.
All the other solutions suffer from the same problem. In order to get the correct data we'd have to order a specific way before grouping. But if that isn't the way we want the output ordered, we end up re-sorting in code anyway.
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #204,258
4/21/05 10:32:29 AM
|
Untested, googling for supported syntax
What I saw suggests that MySQL lacks a proper CASE statement, but has one similar to Oracle's DECODE. Assuming that I can nest them, something like this may work: \n SELECT\n o.ordernum,\n o.orderdate,\n CASE SUM(\n CASE e.status\n WHEN 'open' THEN 1\n ELSE 0\n END\n )\n WHEN 0 THEN 0\n ELSE 1 END as has_escalation\n FROM tbl_order AS o\n LEFT JOIN tbl_escalation AS e\n ON o.ordernum = e.ordernum\n GROUP BY o.ordernum, o.orderdate\n ORDER BY\n CASE SUM(\n CASE e.status\n WHEN 'open' THEN 1\n ELSE 0\n END\n )\n WHEN 0 THEN 0\n ELSE 1 END DESC, o.ordernum\n If this doesn't work, then something like it has a good chance. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,376
4/21/05 8:17:36 PM
|
Re: Reply from Drew
How about something like
select o.ordernum, count(e.status)
from tbl_order o left outer join tbl_escalation e
on o.ordernum = e.ordernum and e.status = 'open'
group by o.ordernum
order by o.ordernum
You can then wrap count(e.status) in whatever formatting functions are available.
|
Post #204,377
4/21/05 8:36:52 PM
|
You're ordering by the wrong thing
Yes, you can order by the count(*), but you're now subordering by the number of escalations and may want a different sort.
That is why I did all of the CASE garbage in my solution.
Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,436
4/22/05 12:38:23 PM
|
Where do you see me ordering by count(*) ?
|
Post #204,487
4/22/05 2:08:36 PM
|
You weren't, clarification
He wanted it sorted by whether any tickets were open. You weren't sorting by that, you were sorting by ordernum. Hence the title of my post.
I suggested that you could fix that to get something closer to what he wanted by sorting on count(*), but it doesn't support exactly the sort that I think he would eventually decide that he wants.
Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #204,514
4/22/05 4:32:31 PM
|
Re: You weren't, clarification
Damn. You're right. I skipped over the ordering requirement.
|
