IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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]
New SELECT DISTINCT t1.* FROM t1, t2 ...?
[link|http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html|http://dev.mysql.com...g-subqueries.html]
New 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)
New 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\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
tbl_order
orderdateordernum
2005-01-01123
2005-01-02124
2005-01-03125
2005-01-04126



\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
tbl_escalation
ordernumstatus
124open
125closed
126open
126closed


desired output:

\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd
\ufffd\ufffd\ufffd
escalation status
ordernumorderdateopen escalations
1242005-01-02X
1262005-01-04X
1232005-01-01 
1252005-01-03 
Two out of three people wonder where the other one is.
New 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
Expand Edited by SpiceWare April 21, 2005, 09:56:20 AM EDT
Expand Edited by SpiceWare April 21, 2005, 09:57:50 AM EDT
Expand Edited by SpiceWare April 21, 2005, 09:59:22 AM EDT
New 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)
New 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
New 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
New 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)
New 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
New 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
New 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
New 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]
New 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)
New 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.
New 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)
New Where do you see me ordering by count(*) ?
New 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)
New Re: You weren't, clarification
Damn. You're right. I skipped over the ordering requirement.
     How to do without subqueries - (drewk) - (18)
         SELECT DISTINCT t1.* FROM t1, t2 ...? - (FuManChu)
         I don't understand your description - (ben_tilly) - (16)
             Reply from Drew - (Meerkat) - (15)
                 Re: Reply from Drew - (SpiceWare) - (6)
                     DECODE is Oracle-specific. -NT - (ben_tilly) - (5)
                         It is, but it is easy to clone - (tuberculosis)
                         Are you sure? I checked MySQL before posting - (SpiceWare) - (3)
                             They may have added it but it is not standard - (ben_tilly)
                             Seems to be specific to MaxDB - (JayMehaffey) - (1)
                                 That's rather confusing - (SpiceWare)
                 One solution - (JayMehaffey) - (1)
                     FWIW this was the best I saw for our needs - (drewk)
                 Untested, googling for supported syntax - (ben_tilly)
                 Re: Reply from Drew - (dws) - (4)
                     You're ordering by the wrong thing - (ben_tilly) - (3)
                         Where do you see me ordering by count(*) ? -NT - (dws) - (2)
                             You weren't, clarification - (ben_tilly) - (1)
                                 Re: You weren't, clarification - (dws)

I think its a fine solution to the problem that you probably shouldn't have.
156 ms