-
Expert Member
Sql query problem
Select pr_at_tokn,pr_at_dt14,sum(pr_at_loph) loph,pr_at_dt01 from
(select pr_at_tokn,pr_at_dt14,pr_at_loph,null pr_at_dt01 from pr_batn_yr
where pr_at_perd='15-sep-2007' and nvl(pr_at_loph,0)=0 and pr_at_dwrk > 0
and pr_at_mode='w' and substr(pr_at_tokn,2,1)='1' and
pr_at_tokn not like 'z%' and pr_at_dt14 not in ('uu')
union select pr_at_tokn,null pr_at_dt14,0,pr_at_dt01 from pr_batn_01
where pr_at_perd='30-sep-2007' and pr_at_dwrk > 0 and pr_at_mode='w'
and substr(pr_at_tokn,2,1)='1' and pr_at_tokn not like 'z%' and pr_at_dt01 not in ('uu')
and pr_at_tokn in
(select pr_at_tokn from pr_batn_yr
where pr_at_perd='15-sep-2007' and nvl(pr_at_loph,0)=0 and pr_at_dwrk > 0 and pr_at_mode='w'
and substr(pr_at_tokn,2,1)='1' and pr_at_tokn not like 'z%' and pr_at_dt14 not in ('uu')))
group by pr_at_tokn,pr_at_dt14,pr_at_dt01
The output of the query is like this:
Pr_at_tokn pr_at_dt14 loph pr_at_dt01
111147 null 0 SS
111147 SS 0 null
But required output is
pr_at_tokn pr_at_dt14 loph pr_at_dt01
111147 SS 0 SS
The above query is unable to get required output, pls correct the
query to get required output...
Last edited by susarlasireesha; 11-10-2007 at 03:57 AM.
-
Expert Member
Re: Sql query problem
Try this
Select a.pr_at_tokn,a.pr_at_dt14,sum(a.pr_at_loph)+sum(b.pr_at loph),b.pr_at_dt01 from
(
select pr_at_tokn,pr_at_dt14,pr_at_loph,null pr_at_dt01
from pr_batn_yr
where pr_at_perd='15-sep-2007'
and nvl(pr_at_loph,0)=0 and pr_at_dwrk > 0
and pr_at_mode='w' and substr(pr_at_tokn,2,1)='1'
and pr_at_tokn not like 'z%' and pr_at_dt14 not in ('uu')
)a
full outer join
(
select pr_at_tokn,null pr_at_dt14,0,pr_at_dt01
from pr_batn_01
where pr_at_perd='30-sep-2007'
and pr_at_dwrk > 0
and pr_at_mode='w'
and substr(pr_at_tokn,2,1)='1'
and pr_at_tokn not like 'z%'
and pr_at_dt01 not in ('uu')
and pr_at_tokn in
(
select pr_at_tokn from pr_batn_yr
where pr_at_perd='15-sep-2007' and nvl(pr_at_loph,0)=0 and pr_at_dwrk > 0 and pr_at_mode='w'
and substr(pr_at_tokn,2,1)='1' and pr_at_tokn not like 'z%' and pr_at_dt14 not in ('uu')))
group by pr_at_tokn,pr_at_dt14,pr_at_dt01
)b
on a.pr_at_tokn = b.pr_at_tokn
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules