I am still trying, and I have run the EXPLAIN function over the query and I
have received the following result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tbl_f4Granjas index FK_ProjectHolderId 255 5 (null)
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
What does it means?.
Thanks again,
Alvaro
----- Original Message -----
From: Alvaro Cobo
To: mysql@lists.mysql.com
Sent: Thursday, June 09, 2005 11:36 PM
Subject: CASE .. WHEN .. returns NULL
Hi guys:
I am here again asking for your generous and wise advise:
I have a table (tbl_f4Granjas) with the information of some farms, their whole
land extension (field: GraExtUPA) and the portion of the land they are managing
in an agro-ecological way (field: GraExtPredio).
What do I need is to get the percentage by organization (field:
FK_ProjectHolderId) of the land managed in the agroecological way. After that,
I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.).
I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL
result. What I am doing wrong?. I have searched everywhere, goggled it, check
the Mysql manual, but I couldnī't find the answer. My server is: Mysql 4.1.10
in a Debian Sarge box.
Following are the scripts.
Thanks in advance and best regards.
#TABLE DEFINITION
CREATE TABLE `tbl_f4Granjas` (
`ID_Granja` int(11) NOT NULL auto_increment,
`FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default
'' COMMENT 'Foreign Key which conects to counterparts table',
`GraExtUPA` int(11) default '0',
`GraExtPredio` int(11) default '0',
PRIMARY KEY (`ID_Granja`),
UNIQUE KEY `idGranjaFecha` (`IdGranjaFecha`),
KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Main
table for farms' AUTO_INCREMENT=6 ;
#INSERT SAMPLE DATA
INSERT INTO `tbl_f4Granjas` VALUES (1, 'org1', 1500, 600);
INSERT INTO `tbl_f4Granjas` VALUES (2, 'org1', 1700, 1300);
INSERT INTO `tbl_f4Granjas` VALUES (3, 'org2', 1900, 900);
INSERT INTO `tbl_f4Granjas` VALUES (4, 'org2', 4000, 1500);
INSERT INTO `tbl_f4Granjas` VALUES (5, 'org2', 5500, 1300);
#SCRIPT/QUERY
#This is the query which doesn't work :-(
SELECT a.FK_ProjectHolderId, (
SELECT CASE
WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) <=10 THEN 0
WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) >=11 AND
(SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))<=25 THEN 3
WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) >=26 AND
(SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))<=50 THEN 6
WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) >=51 AND
(SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))<=75 THEN 9
WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) >=76 AND
(SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))<=100 THEN 12
END) AS agroland,
#This query works, but I need a result with the sum from each organization in
the first stage
(
SELECT CASE
WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) <=10 THEN 0
WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) >=11 AND
((a.GraExtPredio)*100)/(a.GraExtUPA)<=25 THEN 3
WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) >=26 AND
((a.GraExtPredio)*100)/(a.GraExtUPA)<=50 THEN 6
WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) >=51 AND
((a.GraExtPredio)*100)/(a.GraExtUPA)<=75 THEN 9
WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) >=76 AND
((a.GraExtPredio)*100)/(a.GraExtUPA)<=100 THEN 12
END) AS agroland_wrong,
#test. This query works, but outside the CASE .. WHEN .. THEN function :-(
(SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) AS test
FROM tbl_f4Granjas AS a
GROUP BY a.FK_ProjectHolderId
