I am encountering some strange behavior with the results returned from a
query on a table in MySQL. The query is:
select
t.lu_cn as cn,
sum(if(strcmp(t.work_item_type, 'OPERATIONS'),
t.reference_dollars_labor +
t.reference_dollars_equip +
t.reference_dollars_travel +
t.reference_dollars_matl +
t.reference_dollars_service, 0)) as ROPC,
sum(if(strcmp(t.work_item_type, 'OPERATIONS'),
t.needed_dollars_labor +
t.needed_dollars_equip +
t.needed_dollars_travel +
t.needed_dollars_matl +
t.needed_dollars_service, 0)) as NOPC,
sum(if(strcmp(t.work_item_type, 'ANNUAL MAINTENANCE'),
t.reference_dollars_labor +
t.reference_dollars_equip +
t.reference_dollars_travel +
t.reference_dollars_matl +
t.reference_dollars_service, 0)) as RAMC,
sum(if(strcmp(t.work_item_type, 'ANNUAL MAINTENANCE'),
t.needed_dollars_labor +
t.needed_dollars_equip +
t.needed_dollars_travel +
t.needed_dollars_matl +
t.needed_dollars_service, 0)) as NAMC,
sum(if(strcmp(t.work_item_type, 'DEFERRED MAINTENANCE'),
t.reference_dollars_labor +
t.reference_dollars_equip +
t.reference_dollars_travel +
t.reference_dollars_matl +
t.reference_dollars_service, 0)) as RDMC,
sum(if(strcmp(t.work_item_type, 'DEFERRED MAINTENANCE'),
t.needed_dollars_labor +
t.needed_dollars_equip +
t.needed_dollars_travel +
t.needed_dollars_matl +
t.needed_dollars_service, 0)) as NDMC,
sum(if(strcmp(t.work_item_type, 'CAPITAL IMPROVEMENTS'),
t.reference_dollars_labor +
t.reference_dollars_equip +
t.reference_dollars_travel +
t.reference_dollars_matl +
t.reference_dollars_service, 0)) as RCIC,
sum(if(strcmp(t.work_item_type, 'CAPITAL IMPROVEMENTS'),
t.needed_dollars_labor +
t.needed_dollars_equip +
t.needed_dollars_travel +
t.needed_dollars_matl +
t.needed_dollars_service, 0)) as NCIC
from ii_tasks t
group by t.lu_cn
;
The results of the query return the following view (shortened for brevity):
+-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+|
+ cn | ROPC | NOPC | RAMC | NAMC | RDMC
| NDMC | RCIC | NCIC
||+-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+|
| 5016.003441 | 6482.73 | 900.21 | 16295.50 | 6633.80 | 22778.23
| 7534.01 | 22778.23 | 7534.01
||+-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+60
| rows in set (2.40 sec)
The problem I am encountering is that the figures for ROPC and NOPC
*should* be in the RAMC and NAMC columns respectively, and vice-versa.
If I run this query:
select sum(t.reference_dollars_labor + t.reference_dollars_travel +
t.reference_dollars_matl + t.reference_dollars_service +
t.reference_dollars_equip) as ROPCfrom ii_tasks t
where t.work_item_type = 'OPERATIONS' and t.lu_cn = '5016.003441'
group by t.lu_cn;
I get this:
+----------+
| ROPC |
+----------+
| 16295.50 |
+----------+
1 row in set (0.01 sec)
I've checked this in four(!) different MySQL installations, all version
3.23.51-log as well as MySQL-4.0.2-alpha and I still get the same results.
The data is stored in the table properly, and it doesn't appear to be an
issue with MySQL, so the problem is the query.
Is this the correct behavior or am I missing something?
Any help is greatly appreciated.
...scb
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
