Hi Rick,
First, the MySQL IsNULL() function does not operate like the ORACLE or MS
SQL version. It is merely a test and returns either 1 or 0. You will need
to use COALESCE() to provide a non-null replacement for a null value.
I am not sure what you are trying to accomplish with your EXISTS() clause
but that was what was killing you. I tried it both as EXISTS and as NOT
EXISTS and I didn't get *any* records where rangeid=4. I can't fully
explain the behavior but I think its failure is linked to the fact that
there are zero b table rows for rangeid of 4.
I rewrote your exclusion as another LEFT JOIN and I have the right number
of rows but I need you to verify the results:
select a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
a.rangeid rangeid,
case coalesce(a.rangeid, 99999)
when 99999 then 'null rangeid'
else coalesce(b.descr, 'null descr') end descr
from transactions a
left outer join ref_info b
on a.rateplan = b.rateplan
and a.rangeid = b.rangeid
and a.trandate > b.effdate
left join ref_info c
on c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
where a.acct =123
and c.rateplan is null;
Let me know if this worked as you wanted. I will keep trying to figure out
why that EXISTS clause failed....
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rick Robinson"
<[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]>
cc:
06/11/2004 12:48 Fax to:
PM Subject: Possible problem with outer
join in 4.1.2
Please respond to
rick
Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated
a
table set up and query from an Oracle application and I'm not getting the
same result set. The following script sets up the representative tables
and
data and has the query.
drop table if exists ref_info;
drop table if exists transactions;
create table ref_info
(
rateplan char(3) not null,
rangeid int not null,
effdate datetime not null,
descr char(20) not null,
primary key(rateplan, rangeid, effdate)
)
type=myisam
;
create table transactions
(
id int not null auto_increment,
acct int not null,
rateplan char(3) not null,
trandate datetime not null,
rangeid int null,
primary key(id),
key(acct, rateplan, trandate)
)
type=myisam
;
insert into ref_info values
('aaa',1,'1970-01-01','aaa:1:1970-01-01'),
('aaa',1,'2004-06-01','aaa:1:2004-06-01'),
('aaa',1,'2004-06-03','aaa:1:2004-06-03'),
('aaa',1,'2004-06-05','aaa:1:2004-06-05'),
('aaa',2,'1970-01-01','aaa:2:1970-01-01'),
('aaa',2,'2004-06-01','aaa:2:2004-06-01'),
('aaa',2,'2004-06-03','aaa:2:2004-06-03'),
('aaa',2,'2004-06-05','aaa:2:2004-06-05'),
('aaa',3,'1970-01-01','aaa:3:1970-01-01'),
('aaa',3,'2004-06-01','aaa:3:2004-06-01'),
('aaa',3,'2004-06-03','aaa:3:2004-06-03'),
('aaa',3,'2004-06-05','aaa:3:2004-06-05'),
('aaa',5,'1970-01-01','aaa:5:1970-01-01'),
('aaa',5,'2004-06-01','aaa:5:2004-06-01'),
('aaa',5,'2004-06-03','aaa:5:2004-06-03'),
('aaa',5,'2004-06-05','aaa:5:2004-06-05')
;
insert into transactions values
(null,123,'aaa','2004-05-30',1),
(null,123,'aaa','2004-06-02',1),
(null,123,'aaa','2004-06-04',1),
(null,123,'aaa','2004-06-06',1),
(null,123,'aaa','2004-05-30',2),
(null,123,'aaa','2004-06-02',2),
(null,123,'aaa','2004-06-04',2),
(null,123,'aaa','2004-06-06',2),
(null,123,'aaa','2004-05-30',3),
(null,123,'aaa','2004-06-02',3),
(null,123,'aaa','2004-06-04',3),
(null,123,'aaa','2004-06-06',3),
(null,123,'aaa','2004-05-30',4),
(null,123,'aaa','2004-06-02',4),
(null,123,'aaa','2004-06-04',4),
(null,123,'aaa','2004-06-06',4),
(null,123,'aaa','2004-05-30',5),
(null,123,'aaa','2004-06-02',5),
(null,123,'aaa','2004-06-04',5),
(null,123,'aaa','2004-06-06',5),
(null,123,'aaa','2004-05-30',null),
(null,123,'aaa','2004-06-02',null),
(null,123,'aaa','2004-06-04',null),
(null,123,'aaa','2004-06-06',null)
;
select count(*) from transactions
;
select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 99999) rangeid,
case ifnull(a.rangeid, 99999) when 99999 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr
from
transactions a left outer join ref_info b
on a.rateplan = b.rateplan and a.rangeid = b.rangeid and a.trandate >
b.effdate
where
a.acct = 123
and not exists
(select 1 from ref_info c
where c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
)
order by 2 desc, 1 desc
;
On the nifty outer join query, I expect to get 24 rows back, but only get
16; all rows with either a null a.rangeid or a.rangeid=4 are missing. This
(effectively) same query works in Oracle and returns all data. (as FYI,
the
query is trying to get back descriptions that are effective dated -
however,
some descriptions may not be there for various reasons).
So, am I writing this query incorrectly for MySQL? Is this a possible bug?
Thanks for any help. But please skip suggestions on trying to make changes
to the schema definition.
Running MySQL 4.1.2 binary on both Solaris 2.9 and WinXP.
Best regards,
Rick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
