| Subject: | DBIx-SQLCrosstab query generation error with Oracle |
Oracle version 9.2.0
DBD::Oracle version 1.15
perl version 5.8.5
Linux 2.6.8.1 x86_64 AMD Opteron Mandrakelinux 10.1
This is the test set, derived from the perldoc page
create table person(
id number not null,
name varchar(80) not null,
gender varchar(1) not null,
dept_id number not null
);
create table depts (
dept_id number not null,
dept varchar2(10) not null
);
insert into person values (1 , 'John' ,'m' , 1);
insert into person values (2 , 'Mario' ,'m' , 1);
insert into person values (7 , 'Mary' ,'f' , 1);
insert into person values (8 , 'Bill' ,'m' , 1);
insert into person values (3 , 'Frank' ,'m' , 2);
insert into person values (5 , 'Susan' ,'f' , 2);
insert into person values (6 , 'Martin' ,'m' , 2);
insert into person values (4 , 'Otto' ,'m' , 3);
insert into person values (9 , 'June' ,'f' , 3);
insert into depts values(1,'pers');
insert into depts values(2,'sales');
insert into depts values(3,'dev');
Here are the params
my $params = {
dbh => $dbh,
op => [ ['COUNT','id'] ],
from => 'person INNER JOIN depts USING (dept_id)',
rows => [
{ col => 'DEPT'},
],
cols => [
{
id => 'GENDER',
from =>'PERSON'
}
]
};
This is what is generated with sqlite (works as advertised)
SELECT DEPT AS DEPT
,COUNT(CASE WHEN GENDER = 'm' THEN id ELSE NULL END) AS xfld001
,COUNT(CASE WHEN GENDER = 'f' THEN id ELSE NULL END) AS xfld002
FROM person INNER JOIN depts USING (dept_id)
GROUP BY DEPT ORDER BY DEPT
And this is what query is generated with Oracle (broken)
SELECT DEPT AS DEPT
FROM person INNER JOIN depts USING (dept_id)
GROUP BY DEPT ORDER BY DEPT
The funny part is the query generated thru the Sqllite driver is a correct query for oracle. Which means the case statement is correct even if the standard Oracle practice is to use the decode function.
I don't know if you're still involved with this package, but I would be very interested if you had any indication on this matter,