Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: problem inserting records from temp table

    Hi,

    I've got a bit of a weird one here which I can't work out. Basically, I'm extracting some records that I want to summarise from a table into a temp table, delete the records out of the source table, and then inserting any records back into the original table where the key currently doesn't exist in the target table. I then update the balances with the new amounts calculated in the temp table. When I run the sql statements in the sql window it run fines, but as soon as I compile them in a stored procedure it falls over when I run the procedure and say's I'm trying to insert duplicate records (I have a unique non-clustered index on the table).

    My sql statements are basically (simplified examples) this

    Select cust_id, ccy, sbrc, asofdate, 'RESIDUAL' as reference, sum(ss_balance) as ss_bal
    into #roll_balances
    from detail_balance
    where abs(ss_balance)<=0.05
    group by cust_id, ccy, sbrc, asofdate

    I then delete the records where the abs(ss_balance)<=0.05

    And then I insert the new summary records back in populating just the key fields:

    Insert into detail_balance
    (cust_id, ccy, sbrc, asofdate, reference, ss_balance)
    select rb.cust_id, rb.ccy, rb.sbrc, rb.asofdate, rb.reference, 0
    from #roll_balances rb left join detail_balance deb on
    (rb.cust_id = deb.cust_id and
    rb.ccy = deb.ccy and
    rb.sbrc = deb.sbrc and
    rb.asofdate = deb.asofdate and
    rb.reference = deb.reference)
    where deb.cust_id is null

    This last statement is the one causing it to fail. I've checked that there are no records in the table where cust_id is null, and when I run it outside of the stored procedure it works. Any help on this is greatly appreciated, as I am very stuck!!!

  2. #2
    Join Date
    Oct 2003
    Posts
    5

    It get's worse

    I now have another example of a SQL statement which has completely different results based on whether it's in a stored procedure or run in an isql window.

    Update sub_account_entry
    set summary = 'Y'
    where cont_typ = 'AC' and
    post_acct_nbr like 'IRS%' and
    post_acct_typ in ('AINP','INAR')

    When I run this statement in a isql window it's fine. When I run it as part of a stored procedure, it updates records where con_typ='LD' and post_acct_nbr='IRS' and post_acct_typ = 'WASH'

    It's almost like it's treating the "AND" like an "OR". Never had these problem Microsoft SQL Server, and this is my first time using sybase, and I'm currently finding it significantly worse!

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I've never, ever heard of the second error you are getting -- try posting the ENTIRE stored procecure so we can see what is going on; I am almost 100% certain that this is probably a logical error somewhere and not a bug in the system. What version of ASE are you on?
    Thanks,

    Matt

  4. #4
    Join Date
    Oct 2003
    Posts
    5
    MattR,

    There was a logic error - my mistake, it was a long week and after spending a whole day looking at the initial problem, I didn't investigate as thoroughly as I shoudl have.

    However, the initial problem still stands - I've checked and re-checked my logic, and there is absolutely no ways that I should be inserting duplicates, yet it keeps coming back and saying that I am, but only when I run it as a stored procedure, not in an isql window.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Can you post the table schema and the stored procedure text?
    Thanks,

    Matt

  6. #6
    Join Date
    Oct 2003
    Posts
    5
    Matt,

    Please see attached schema and stored procedure - It's falling over at the when inserting from the temporary table (used for performance reasons mainly) into the detail_account_balance table.

    Thanks for your help!!


    CREATE TABLE dbo.detail_account_balance
    (
    cust_id char(12) NOT NULL,
    typ char(4) NOT NULL,
    ccy char(3) NOT NULL,
    ref char(3) NOT NULL,
    sbrc char(6) NOT NULL,
    asofdate datetime NOT NULL,
    snapshot_balance numeric(28,2) DEFAULT 0 NOT NULL,
    historic_balance numeric(28,2) DEFAULT 0 NOT NULL,
    pm_historic_balance numeric(28,2) DEFAULT 0 NOT NULL,
    reference varchar(255) NOT NULL
    )
    LOCK ALLPAGES
    go
    IF OBJECT_ID('dbo.detail_account_balance') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.detail_account_balance >>>'
    ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.detail_account_balance >>>'
    go
    CREATE UNIQUE INDEX idx_det_bal_uclt_1
    ON dbo.detail_account_balance(cust_id,typ,ccy,ref,sbr c,asofdate,reference)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.detail_account_balance') AND name='idx_det_bal_uclt_1')
    PRINT '<<< CREATED INDEX dbo.detail_account_balance.idx_det_bal_uclt_1 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.detail_account_balance.idx_det_bal_uclt_1 >>>'
    go

    ************************************************** **********

    CREATE PROCEDURE dbo.sp_archive_purge_data
    AS

    DECLARE @msg VARCHAR(255),
    @stpid INT,
    @error INT,
    @cob_date DATETIME

    SELECT @stpid = @@procid,
    @msg = "Starting archive process"

    EXEC sp_log_error "Archive Acc Entry", @stpid, "INFO", "1. Start", @msg,0


    BEGIN

    SELECT @cob_date = (SELECT max(cob_date) from run_dates)

    SELECT @error = @@error
    IF @error!=0
    BEGIN
    SELECT @msg= "Error extracting Close of Business date"
    EXEC sp_log_error "Archive Acc Entry", @stpid, "CRITICAL", "2. Get Dates", @msg, @error
    RETURN -1
    END

    END


    /*-------------------------------------------------------------------------------*/
    /* Roll up balances on the detail balances table */
    /*-------------------------------------------------------------------------------*/

    BEGIN
    SELECT dab.cust_id,
    dab.typ,
    dab.ccy,
    dab.ref,
    dab.sbrc,
    sum(dab.snapshot_balance) as ss_bal,
    sum(dab.historic_balance) as hs_bal,
    sum(dab.pm_historic_balance) as pm_hs_bal,
    dab.asofdate,
    'RESIDUAL' as reference
    INTO #roll_balance
    FROM detail_account_balance dab
    INNER JOIN ref_trade_customer rtc
    ON (dab.reference = rtc.prime_key)
    WHERE abs(snapshot_balance) <= 0.05 AND
    abs(historic_balance) <= 0.05 AND
    abs(pm_historic_balance) <= 0.05 AND
    rtc.reference_type in ('POSITION','FXTRADE','LDTRADE') AND
    rtc.value_date_2 <= @cob_date AND
    dab.asofdate = @cob_date
    GROUP BY dab.cust_id,
    dab.typ,
    dab.ccy,
    dab.ref,
    dab.sbrc,
    dab.asofdate

    SELECT @error = @@error
    IF @error!=0
    BEGIN
    SELECT @msg= "Error creating roll up temp table"
    EXEC sp_log_error "Archive Acc Entry", @stpid, "CRITICAL", "5.1 Create roll up temp table", @msg, @error
    RETURN -1
    END
    END

    BEGIN
    BEGIN TRANSACTION

    INSERT INTO detail_account_balance
    (cust_id,
    typ,
    ccy,
    ref,
    sbrc,
    asofdate,
    reference)

    SELECT rb.cust_id,
    rb.typ,
    rb.ccy,
    rb.ref,
    rb.sbrc,
    rb.asofdate,
    rb.reference
    FROM #roll_balance rb
    LEFT JOIN detail_account_balance dab
    ON (rb.cust_id = dab.cust_id AND
    rb.typ = dab.typ AND
    rb.ccy = dab.ccy AND
    rb.ref = dab.ref AND
    rb.sbrc = dab.sbrc AND
    rb.asofdate = dab.asofdate AND
    rb.reference = dab.reference)
    where dab.cust_id IS NULL


    SELECT @error = @@error
    IF @error!=0
    BEGIN
    ROLLBACK TRANSACTION
    SELECT @msg= "Error inserting new residual balances into table"
    EXEC sp_log_error "Archive Acc Entry", @stpid, "CRITICAL", "5.2 Insert new bals", @msg, @error
    RETURN -1
    END

    END

    BEGIN


    UPDATE detail_account_balance

    SET pm_historic_balance = pm_historic_balance + rb.pm_hs_bal,
    snapshot_balance = snapshot_balance + rb.ss_bal,
    historic_balance = historic_balance + rb.hs_bal
    FROM detail_account_balance dab
    INNER JOIN #roll_balance rb
    ON (dab.cust_id = rb.cust_id AND
    dab.typ = rb.typ AND
    dab.ccy = rb.ccy AND
    dab.ref = rb.ref AND
    dab.sbrc = rb.sbrc AND
    dab.reference = rb.reference AND
    dab.asofdate = rb.asofdate)


    UPDATE detail_account_balance
    SET dab.reference = 'DELETE'
    FROM detail_account_balance dab
    INNER JOIN ref_trade_customer rtc
    ON (dab.reference = rtc.prime_key)
    WHERE abs(snapshot_balance) <= 0.05 AND
    abs(historic_balance) <= 0.05 AND
    abs(pm_historic_balance) <= 0.05 AND
    rtc.reference_type in ('POSITION','FXTRADE','LDTRADE') AND
    rtc.value_date_2 <= @cob_date AND
    dab.asofdate = @cob_date

    DELETE detail_account_balance
    WHERE reference = 'DELETE'


    SELECT @error = @@error
    IF @error!=0
    BEGIN
    ROLLBACK TRANSACTION
    SELECT @msg= "Error inserting new residual balances into table"
    EXEC sp_log_error "Archive Acc Entry", @stpid, "CRITICAL", "5.3 Insert new bals", @msg, @error
    RETURN -1
    END
    ELSE
    BEGIN
    COMMIT TRANSACTION
    END
    END

Posting Permissions

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