Results 1 to 2 of 2

Thread: Sql query problem

  1. #1
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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.

  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact