>Description:
i got wrong values from a select statement using a
case value when .. then .. else .. end instruction
i made a select statement from this two tables
corriere
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| ID | tinyint(3) unsigned | | PRI | NULL | auto_increment |
| sigla | char(20) | | | | |
| id | sigla |
+----+--------------------+
| 1 | Executive |
| 2 | TNT Global Express |
| 3 | Borghi |
| 4 | DHL |
upacco
| Field | Type | Null | Key |
Default | Extra |
+---------------+-------------------------------------------+------+-----+--
-------+----------------+
| id | mediumint(8) unsigned | | PRI |
NULL | auto_increment |
| rif_ddt | varchar(20) | YES | |
NULL | |
| qty | smallint(5) unsigned | | | 0
| |
| addr_shipp | smallint(5) unsigned | | | 0
| |
| awb | varchar(20) | YES | |
NULL | |
| id_corriere | tinyint(3) unsigned | YES | |
NULL | |
| pack_details | varchar(20) | YES | |
NULL | |
| data_consegna | date | YES | |
NULL | |
| status | enum('APERTO','REC','SH-W','SH-C','SH-L') | | |
APERTO | |
| id | rif_ddt | qty | addr_shipp | awb | id_corriere | pack_details |
data_consegna | status |
+----+---------+-----+------------+----------+-------------+--------------+-
--------------+--------+
| 12 | gf | 0 | 29 | pacco 12 | 3 | asdf |
2002-06-07 | APERTO |
| 16 | ddt8 | 1 | 9 | pacco 16 | 3 | asdf |
2002-06-10 | SH-C |
| 17 | NULL | 3 | 24 | pacco 17 | 2 | xyz |
NULL | REC |
| 18 | NULL | 0 | 33 | pacco 18 | 3 | asdf |
NULL | APERTO |
| 19 | NULL | 0 | 5 | NULL | NULL | NULL |
NULL | APERTO |
| 20 | NULL | 4 | 24 | NULL | NULL | NULL |
NULL | REC |
| 21 | NULL | 1 | 50 | NULL | NULL | NULL |
NULL | APERTO |
| 22 | ddt8 | 4 | 23 | pacco 22 | 3 | pacco 22 |
2002-06-10 | SH-C |
| 23 | NULL | 0 | 24 | NULL | NULL | NULL |
NULL | APERTO |
| 24 | NULL | 0 | 24 | NULL | NULL | NULL |
NULL | APERTO |
this select statement retrives wrong values in field status:
SELECT DISTINCT up.id as id,IFNULL(up.awb,'') as awb
,IF(up.id_corriere,c.sigla,'') as corriere
,IFNULL(up.pack_details,'') as pack_details
,up.data_consegna as data_consegna
,(CASE up.status
WHEN 'REC' THEN 'CHIUSO'
WHEN 'SH-W' THEN 'FERMO'
WHEN 'SH-C' THEN 'SPEDITO'
WHEN 'SH-L' THEN 'SPEDITO'
ELSE up.status END) as status
FROM upacco up, corriere c
WHERE (c.id = up.id_corriere or up.id_corriere IS NULL)
AND (up.rif_ddt='ddt8' OR up.rif_ddt IS NULL)
ORDER BY up.rif_ddt,up.id;
| id | awb | corriere | pack_details | data_consegna | status
| expected value is
+----+----------+--------------------+--------------+---------------+-------
-+
| 17 | pacco 17 | TNT Global Express | xyz | NULL | APERTO
| <- CHIUSO
| 18 | pacco 18 | Borghi | asdf | NULL | APERTO
|
| 19 | | | | NULL | APERTO
|
| 20 | | | | NULL | APERTO
| <- CHIUSO
| 21 | | | | NULL | APERTO
|
| 23 | | | | NULL | APERTO
|
| 24 | | | | NULL | APERTO
|
| 16 | pacco 16 | Borghi | asdf | 2002-06-10 | APERTO
| <- SPEDITO
| 22 | pacco 22 | Borghi | pacco 22 | 2002-06-10 | APERTO
| <- SPEDITO
her i removed the case statement to se what is the real value an it's ok
SELECT DISTINCT up.id as id,IFNULL(up.awb,'') as awb
,IF(up.id_corriere,c.sigla,'') as corriere
,IFNULL(up.pack_details,'') as pack_details
,up.data_consegna as data_consegna
,up.status as status
FROM upacco up, corriere c
WHERE (c.id = up.id_corriere or up.id_corriere IS NULL)
AND (up.rif_ddt='ddt8' OR up.rif_ddt IS NULL)
ORDER BY up.rif_ddt,up.id;
| id | awb | corriere | pack_details | data_consegna | status
|
+----+----------+--------------------+--------------+---------------+-------
-+
| 17 | pacco 17 | TNT Global Express | xyz | NULL | REC
|
| 18 | pacco 18 | Borghi | asdf | NULL | APERTO
|
| 19 | | | | NULL | APERTO
|
| 20 | | | | NULL | REC
|
| 21 | | | | NULL | APERTO
|
| 23 | | | | NULL | APERTO
|
| 24 | | | | NULL | APERTO
|
| 16 | pacco 16 | Borghi | asdf | 2002-06-10 | SH-C
|
| 22 | pacco 22 | Borghi | pacco 22 | 2002-06-10 | SH-C
|
i use also other statements with to tables and exactly the same case
instruction
and it works well for example
SELECT up.id as 'N°Pacco'
,IFNULL(up.rif_ddt,'') as rif_ddt
,(CASE up.status
WHEN 'REC' THEN 'CHIUSO'
WHEN 'SH-W' THEN 'FERMO'
WHEN 'SH-C' THEN 'SPEDITO'
WHEN 'SH-L' THEN 'SPEDITO'
ELSE up.status END) as status
,ind.nome as Destinazione
FROM upacco up, indirizzi ind
WHERE up.status in ('REC','SH-W','SH-C','SH-L')
AND ind.id = up.addr_shipp
AND (up.rif_ddt='ddt8' OR up.rif_ddt IS NULL)
ORDER BY up.rif_ddt,up.id;
| N°Pacco | rif_ddt | status | Destinazione |
+---------+---------+---------+--------------+
| 17 | | CHIUSO | ALPITEL |
| 20 | | CHIUSO | ALPITEL |
| 16 | ddt8 | SPEDITO | WIND |
| 22 | ddt8 | SPEDITO | SIRTI MI |
table indirizzi is made like this
| Field | Type | Null | Key | Default | Extra
|
+--------------+-----------------------+------+-----+---------+-------------
---+
| id | mediumint(8) unsigned | | PRI | NULL |
auto_increment |
| nome | varchar(50) | | | |
|
>How-To-Repeat:
CREATE TABLE upacco (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
rif_ddt CHAR(20) NULL,
qty SMALLINT UNSIGNED NOT NULL,
addr_shipp SMALLINT UNSIGNED NOT NULL,
awb VARCHAR(15) NULL,
id_corriere TINYINT UNSIGNED NULL,
pack_details VARCHAR(20) NULL,
data_consegna DATE NULL,
status enum('APERTO','REC','SH-W','SH-C','SH-L') NOT NULL,
PRIMARY KEY (id) );
INSERT INTO upacco
VALUES
(12,'gf',0,29,'pacco 12',3,'asdf','2002-06-07','APERTO')
,(16,'ddt8',1, 9,'pacco 16',3,'asdf','2002-06-10','SH-C')
,(17,NULL,3,24,'pacco 17',2,'xyz','NULL','REC')
,(18,NULL,0,33,'pacco 18',3,'asdf','NULL','APERTO')
,(19,NULL,0,5,NULL,NULL,NULL,NULL,'APERTO')
,(20,NULL,4,24,NULL,NULL,NULL,NULL,'REC')
,(21,NULL,1,50,NULL,NULL,NULL,NULL,'APERTO')
,(22,'ddt8',4,23,'pacco 22',3,'pacco 22','2002-06-10','SH-C')
,(23,NULL,0,24,NULL,NULL,NULL,NULL,'APERTO')
,(24,NULL,0,24,NULL,NULL,NULL,NULL,'APERTO');
CREATE TABLE corriere (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
sigla CHAR(20) NOT NULL,
PRIMARY KEY (id) );
INSERT INTO corriere
VALUES
(1,'Executive')
,(2,'TNT Global Express')
,(3,'Borghi')
,(4,'DHL');
SELECT DISTINCT up.id as id,IFNULL(up.awb,'') as awb
,IF(up.id_corriere,c.sigla,'') as corriere
,IFNULL(up.pack_details,'') as pack_details
,up.data_consegna as data_consegna
,(CASE up.status
WHEN 'REC' THEN 'CHIUSO'
WHEN 'SH-W' THEN 'FERMO'
WHEN 'SH-C' THEN 'SPEDITO'
WHEN 'SH-L' THEN 'SPEDITO'
ELSE up.status END) as status
FROM upacco up, corriere c
WHERE (c.id = up.id_corriere or up.id_corriere IS NULL)
AND (up.rif_ddt='ddt8' OR up.rif_ddt IS NULL)
ORDER BY up.rif_ddt,up.id;
SELECT DISTINCT up.id as id,IFNULL(up.awb,'') as awb
,IF(up.id_corriere,c.sigla,'') as corriere
,IFNULL(up.pack_details,'') as pack_details
,up.data_consegna as data_consegna
,up.status as status
FROM upacco up, corriere c
WHERE (c.id = up.id_corriere or up.id_corriere IS NULL)
AND (up.rif_ddt='ddt8' OR up.rif_ddt IS NULL)
ORDER BY up.rif_ddt,up.id;
>Originator: Ulrich schmid
>Organization: Videoelettronic S.P.A Italy
>mySQL supporT: none
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-Bug
>Release: mysql-3.23.32 (Source distribution)
System: Linux linuxgw 2.2.17-21mdksecure #1 SMP Thu Oct 5 12:52:38 CEST 2000
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux/2.95.3/specs
gcc version 2.95.3 19991030 (prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --prefix=/usr/local/mysql
---------------------------------------------------------------------
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
