Results 1 to 4 of 4

Thread: query help

  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: query help

    hello

    if i run below query i am getting --2566.58

    select d.ddpoln,SUM(d.ddprpd) AS premium_received
    from pdbdetl@viper2 d,PMTDETL@viper2 b
    where d.ddpoln=b.pypoln
    and b.PYPAYN=d.DDPAYN
    and d.ddpoln=3568485
    AND b.pypydt>='30-dec-2006'
    AND b.pypydt<='31-dec-2009'
    group by d.ddpoln

    --2566.58
    ---------------------
    if i join the query with other table it is giving 25665.8

    select a.pdpoln,SUM(d.ddprpd) AS premium_received
    from pdbdetl@viper2 d,PMTDETL@viper2 b,poldetl@viper2 a
    where d.ddpoln=b.pypoln
    and b.PYPAYN=d.DDPAYN
    and a.pdpoln=d.ddpoln
    and a.pdpoln=b.pypoln
    and d.ddpoln=3568485
    AND b.pypydt>='30-dec-2006'
    AND b.pypydt<='31-dec-2009'
    group by a.pdpoln

    please help the query above is part of my actual ,if you want to check that here is the query

    CREATE TABLE nv_prem_remm AS
    SELECT
    c.conai# AS company_id,
    a.pdpoln AS policy_id,
    TO_CHAR(b.pypydt,'mm/dd/yy') AS premium_booked_date,
    SUM(b.pypyam)AS amount_received,
    SUM(d.ddprpd) AS premium_received,
    SUM(d.ddfepd) AS policy_fee,
    SUM(CASE WHEN ddpdst IN ('INS','IN1','IN','RRB','RNB')
    THEN d.ddfepd END) AS service_charges,
    SUM(CASE WHEN ddpdst = 'RLP' THEN d.ddfepd END) AS reinstatement_fees,
    SUM(CASE WHEN ddpdst = 'NSF' THEN d.ddfepd END) AS returned_check_fees,
    SUM(CASE WHEN ddpdst NOT IN ('INS','IN1','IN','RRB','RNB','RLP','NSF')
    THEN d.ddfepd END) AS other_fees
    FROM poldetl@viper2 a,pmtdetl@viper2 b,company@viper2 c ,pdbdetl@viper2 d
    WHERE a.pdpoln=b.pypoln
    AND a.pdcocd=c.cococd
    AND a.pdpoln=d.ddpoln
    AND b.pypayn=d.ddpayn
    AND b.pypydt>='30-dec-2006'
    AND b.pypydt<='31-dec-2009'
    AND c.cococd IN(9,29,49)
    AND a.pdsabb='NV'
    GROUP BY c.conai#,a.pdpoln,b.pypydt


    please help me

    Thanks!!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what's the problem?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    My crystal ball tells me there are 10 records in poldetl@viper2 for which ddpoln=3568485. You probably need just one of them.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >AND b.pypydt>='30-dec-2006'
    >AND b.pypydt<='31-dec-2009'

    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.

    Please realize that we don't have your tables & we don't have your data.
    Therefore we can't run, test or improve your posted SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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