Results 1 to 5 of 5

Thread: query join

  1. #1
    Join Date
    May 2003
    Posts
    47

    Question Unanswered: query join

    hi,
    i have 2 select statements like

    select bdm, status, sum(nbr), sum(annualprem)
    from mgtrptdetails
    where
    processdate >='01-oct-2004' and
    processdate <= '31-oct-2004' and
    status='Submitted' and
    trim(channel) = 'OFFICE BUS' and
    ((nvl(trim(bdm),'NA') = nvl(trim(bdm),'NA')))
    group by bdm, status;


    o/p
    bdm status sum(nbr) sum(annualprem)
    submitted 7 1800
    mc submitted 1 11
    mo submitted 1 45


    2 select is :

    select bdm, status, sum(nbr), sum(annualprem)
    from mgtrptdetails
    where
    processdate >='01-oct-2004' and
    processdate <= '31-oct-2004' and
    status='Submitted' and
    trim(channel) = 'OFFICE BUS' and
    (ascii(bdm) = 32)
    group by bdm, status;

    o/p
    bdm status sum(nbr) sum(annualprem)
    submitted 7 1900


    how can i join these 2 queries so as to get

    o/p
    bdm status sum(nbr) sum(annualprem)
    submitted 14 3700
    mc submitted 1 11
    mo submitted 1 45

  2. #2
    Join Date
    Sep 2004
    Posts
    16
    Use 'or' to combine the two conditions on bdm, since that is the only difference in the two queries.

    select bdm, status, sum(nbr), sum(annualprem)
    from mgtrptdetails
    where
    processdate >='01-oct-2004' and
    processdate <= '31-oct-2004' and
    status='Submitted' and
    trim(channel) = 'OFFICE BUS' and
    (
    (nvl(trim(bdm),'NA') = nvl(trim(bdm),'NA'))
    or (ascii(bdm) = 32)
    )
    group by bdm, status;

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    one thing of note.
    I would be careful when comparing dates to character strings
    ie: processdate <= '31-oct-2004'

    if your requirement is to search for ALL processdates transpired on
    october 31 then in the above situation only those dates with a time of
    10/31/2004 00:00:00 will be counted. anything after that time will not be
    considered.

    if you also want to include processdates like 10/31/2004 10:51:00 then
    use TRUNC to convert the date column and match that against a true date:
    trunc(processdate) <= to_date('31-oct-2004', 'dd-mon-yyyy')
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2003
    Posts
    47

    Question bdm is a varchar2 field

    i cannot use a group by sum(bdm) as it is a varchar2 field...

    the query1 o/p is like

    submitted null 7 10
    submitted mcarthy 1 10
    submitted mccon 1 12

    o/p 2 is like:

    submitted null 7 15

    so i want o/p as:

    submitted null 14 25
    submitted mcarthy 1 10
    submitted mccon 1 12


    how can this b achieved..
    reply will b grately appreciated..

  5. #5
    Join Date
    Sep 2004
    Posts
    16
    Sum up sum(nbr), sum(annualprem) and
    You should be grouping by bdm and status, not sum(bdm)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •