1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Duplication records in SQL*plus report

Discussion in 'SQL PL/SQL' started by sharmamohit365@gmail.com, Nov 12, 2014.

  1. sharmamohit365@gmail.com

    sharmamohit365@gmail.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Using below code my report is generating duplicate records :

    set space 1
    set echo off
    set heading on
    set feedback off
    set termout on
    set recsep off
    set colsep '|'
    set verify on
    set define on

    set feed off


    --- START monthly claim detail history report

    VAR postmonth char(2)
    VAR postyear char(4)

    begin
    select TO_CHAR(sysdate, 'MM')-1 into :postmonth from dual;
    select TO_CHAR(sysdate, 'YYYY') into :postyear from dual;

    end;
    /


    spool $GONE_DATA_OUT/EPayGL21012699.txt


    COLUMN post_year HEADING YEAR
    COLUMN post_Period HEADING MONTH

    select rptkey,empid,emp_name,post_year,post_period,center, account,amount from claim_detail_history
    where POST_PERIOD = :postmonth and
    POST_YEAR = :postyear and account = '21012699' order by rptkey;
    /



    spool off

    set termout on
    set feedback on
    set heading on
    set echo on
    set space 1
    set flush on
    set recsep EA

    what to do to overcome such duplicacy?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Who knows? You have not posted any create table statements or provided sample data. Please do so.
     
  3. sharmamohit365@gmail.com

    sharmamohit365@gmail.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    RPTKEY |EMPID |EMP_NAME |YEAR|MO|CENTE|ACCOUNT | AMOUNT
    ----------------------|---------------|-----------------------------------------------------------------|----|--|-----|--------|----------
    TEA100183747 |000610 |LANPHERE, JOHN L |2014|10|DW000|99999999| -87.73
    TEA100183747 |000610 |LANPHERE, JOHN L |2014|10|DW000|99999999| -87.73

    i am geeting such records duplicates in sql report while extracting details from above table...
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Mohit,

    As David highlighted, you need to provide the description of the table and sample data to reproduce the issue for us to be able to assist you. The output you provided is not enough.

    However, after having a closer look at your script, I noticed that after your SQL statement (terminated by ";") you had an extra "/" character:

    select rptkey,empid,emp_name,post_year,post_period,center , account,amount from claim_detail_history
    where POST_PERIOD = ostmonth and
    POST_YEAR = ostyear and account = '21012699' order by rptkey;
    /

    The "/" re-runs the last SQL statement in the buffer, which results in the rows output twice !

    Just remove the extra "/" character and re-try.