SQL 7.0 vs SQL 2005 outer join syntax
-
Hi -
We are running SQL 7.0 and will be converting to SQL 2005 in the next few months. I am trying to get a head start on converting from the =* and *= syntax to the left/right outer join ... on ... syntax. But, see my example below -- I do not get the same results using the different constructs. I have found that the left/right join syntax does not produce the results I want in most cases (in SQL 7.0), which is why I have always opted to use the =* and *= syntax. Am I missing something terribly simple or does SQL 7.0 not handle this syntax properly? Or, is there some setting I am missing in SQL 7.0?
I have approximately 150 sps with the =* syntax that will have to be converted for SQL 2005, but I can't start pre-conversion efforts, if I can't get the same results in the syntax.
Thanks,
Sue
create table #tmp_cd (tmp_cd varchar(6), sortorder smallint)
insert into #tmp_cd values ('Male',1)
insert into #tmp_cd values ('Female',0)
create table #tmp_gpra (appr_day datetime,gender varchar(6),disposition varchar(10), appr_count integer)
insert into #tmp_gpra values ('07/01/2006','Male','ER',2)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',3)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',3)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
insert into #tmp_gpra values ('07/01/2006','Male','ER',1)
select * from #tmp_cd
select * from #tmp_gpra
-- Current 7.0 syntax which produces the correct results
SELECT
b.sx_cd as div_cd,
isnull(sum(case when disposition = 'ER' and appr_day = '07/01/2006' then appr_count end),0) as day_ers,
isnull(sum(case when disposition = 'ER/CF' and appr_day = '07/01/2006' then appr_count end),0) as day_ercfs,
isnull(sum(case when disposition = 'WD/ER' and appr_day = '07/01/2006' then appr_count end),0) as day_erwds,
isnull(sum(case when appr_day = '07/01/2006' then appr_count end),0) as day_er_sum,
isnull(sum(case when disposition = 'ER' then appr_count end),0) as ers,
isnull(sum(case when disposition = 'ER/CF' then appr_count end),0) as ercfs,
isnull(sum(case when disposition = 'WD/ER' then appr_count end),0) as erwds,
isnull(sum(appr_count),0) as er_sum
FROM
#tmp_gpra a,
(select tmp_cd as sx_cd from #tmp_cd) b
WHERE
a.gender =* b.sx_cd
and disposition in ('ER','ER/CF','WD/ER')
GROUP BY
b.sx_cd
ORDER BY
b.sx_cd desc
-- Right outer join, incorrect results
SELECT
b.sx_cd as div_cd,
isnull(sum(case when disposition = 'ER' and appr_day = '07/01/2006' then appr_count end),0) as day_ers,
isnull(sum(case when disposition = 'ER/CF' and appr_day = '07/01/2006' then appr_count end),0) as day_ercfs,
isnull(sum(case when disposition = 'WD/ER' and appr_day = '07/01/2006' then appr_count end),0) as day_erwds,
isnull(sum(case when appr_day = '07/01/2006' then appr_count end),0) as day_er_sum,
isnull(sum(case when disposition = 'ER' then appr_count end),0) as ers,
isnull(sum(case when disposition = 'ER/CF' then appr_count end),0) as ercfs,
isnull(sum(case when disposition = 'WD/ER' then appr_count end),0) as erwds,
isnull(sum(appr_count),0) as er_sum
FROM
#tmp_gpra a right outer join (select tmp_cd as sx_cd from #tmp_cd ) b on a.gender = b.sx_cd
WHERE
disposition in ('ER','ER/CF','WD/ER')
GROUP BY
b.sx_cd
ORDER BY
b.sx_cd desc
-- Right outer join, incorrect results
SELECT
b.tmp_cd as div_cd,
isnull(sum(case when disposition = 'ER' and appr_day = '07/01/2006' then appr_count end),0) as day_ers,
isnull(sum(case when disposition = 'ER/CF' and appr_day = '07/01/2006' then appr_count end),0) as day_ercfs,
isnull(sum(case when disposition = 'WD/ER' and appr_day = '07/01/2006' then appr_count end),0) as day_erwds,
isnull(sum(case when appr_day = '07/01/2006' then appr_count end),0) as day_er_sum,
isnull(sum(case when disposition = 'ER' then appr_count end),0) as ers,
isnull(sum(case when disposition = 'ER/CF' then appr_count end),0) as ercfs,
isnull(sum(case when disposition = 'WD/ER' then appr_count end),0) as erwds,
isnull(sum(appr_count),0) as er_sum
FROM
#tmp_gpra a right outer join #tmp_cd b on a.gender = b.tmp_cd
WHERE
disposition in ('ER','ER/CF','WD/ER')
GROUP BY
b.tmp_cd
ORDER BY
b.tmp_cd desc
-- RESULTS FROM QUERY 1 (correct)
div_cd day_ers day_ercfs day_erwds day_er_sum ers ercfs erwds er_sum
------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Male 15 0 0 15 15 0 0 15
Female 0 0 0 0 0 0 0 0
-- RESULTS FROM QUERY 2 (incorrect, missing 'Female')
div_cd day_ers day_ercfs day_erwds day_er_sum ers ercfs erwds er_sum
------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Male 15 0 0 15 15 0 0 15
-- RESULTS FROM QUERY 3 (incorrect, missing 'Female')
div_cd day_ers day_ercfs day_erwds day_er_sum ers ercfs erwds er_sum
------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Male 15 0 0 15 15 0 0 15
-
Not sure which alias to use for column disposition, but try moving "disposition in ('ER''ER/CF','WD/ER') into the join statement.
hth,
Lynn
-
Sue
Your where clause will prevent the row with female in it from ever being returned. Your outer join in the from clause will preserve the row with female in it but this will then be removed by the where clause as disposition for the row with female will be NULL which is not in
('ER','ER/CF','WD/ER').
If you think about the logical order in which the query must be processed this does make sense. So you have to do either as Lynn suggested move the condition into the ON part of the Join statement or remove it as I don't see that it achieves anything but that may just be because of the sample data supplied.
hth
David
-
Thanks to both of you. That did solve the problem. It is amazing how tunnel-visioned one can get and can no longer see the obvious...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply