Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    56

    Unanswered: Pervasive SQL 8.6 cursor update confusion

    Me again. I'm still hating Pervasive SQL but think I'm getting better at it.

    My colleague wrote a report in Excel that does a whole bunch of calculations and vlookups on data returned from the database. This takes exceptionally long so I (stupidly) suggested I look at writing a stored proc in pervasive SQL to speed it up. Having experience in MS SQL has deceived me into expecting similar results from Pervasive..... *sigh*

    Anyways, here is the draft code that I've written:

    CREATE PROCEDURE AGING(IN :vPPeriod int, IN :vCCodeFrom Char(6), IN :vCCodeTo Char(6))
    RETURNS(
    dDate DATE,
    Amount Double,
    EntryType Int,
    CustomerCode Char(6),
    CustomerDesc Char(40),
    Description Char(36),
    Ref Char(8),
    MatchRef Char(8),
    ConcOrgRef Char(14),
    ConcMatchRef Char(14),
    MatchBlankRef Char(8),
    PPeriod Int,
    Original Char(1),
    PaymentTerms Int,
    CreditLimit Int,
    TruePeriod1 Int
    );

    BEGIN

    Declare :dDate DATE;
    Declare :Amount Double;
    Declare :EntryType Int;
    Declare :CustomerCode Char(6);
    Declare :CustomerDesc Char(40);
    Declare : Description Char(36);
    Declare :Ref Char(8);
    Declare :MatchRef Char(8);
    Declare :ConcOrgRef Char(14);
    Declare :ConcMatchRef Char(14);
    Declare :MatchBlankRef Char(8);
    Declare :PPeriod Int;
    Declare :Original Char(1);
    Declare :PaymentTerms Int;
    Declare :CreditLimit Int;
    Declare :TruePeriod1 Int;

    DELETE FROM tblAging2;

    INSERT INTO tblAging2
    select OpenItem.dDate, OpenItem.Amount, OpenItem.EntryType, CustomerMaster.CustomerCode, CustomerMaster.CustomerDesc,
    OpenItem.Description, OpenItem.Ref, OpenItem.MatchRef,
    CONCAT(RTRIM(CustomerMaster.CustomerCode), OpenItem.Ref) as ConcOrgRef,
    CONCAT(RTRIM(CustomerMaster.CustomerCode), IF(OpenItem.MatchRef=' ',OpenItem.Ref,OpenItem.MatchRef))
    as ConcMatchRef,
    IF(OpenItem.MatchRef=' ',OpenItem.Ref,OpenItem.MatchRef) as MatchBlankRef, OpenItem.PPeriod,
    OpenItem.Original, CustomerMaster.PaymentTerms, CustomerMaster.CreditLimit, CONVERT(NULL(),sql_integer)
    from OpenItem
    INNER JOIN CustomerMaster ON OpenItem.CSCode = CustomerMaster.CustomerCode
    INNER JOIN CustomerCategories ON CustomerCategories.CCCode = CustomerMaster.Category
    where OpenItem.DC = 'D'
    and OpenItem.PPeriod <= :vPPeriod
    and CustomerCode between :vCCodeFrom and :vCCodeTo;

    Declare Aging1 Cursor for
    select dDate, Amount, EntryType, CustomerCode, CustomerDesc, Description, Ref, MatchRef, ConcOrgRef,
    ConcMatchRef, MatchBlankRef, PPeriod, Original, PaymentTerms, CreditLimit
    from tblAging2;

    Open Aging1;

    AgingLoop:
    LOOP

    Fetch Next from Aging1 into :dDate, :Amount, :EntryType, :CustomerCode, :CustomerDesc, : Description, :Ref,
    :MatchRef, :ConcOrgRef, :ConcMatchRef, :MatchBlankRef, :PPeriod, :Original, :PaymentTerms, :CreditLimit

    IF SQLSTATE = '02000' THEN
    LEAVE AgingLoop;
    END IF;

    Declare :LookupPeriod Int;

    select TOP 1 PPeriod into :LookupPeriod from tblAging2 where ConcOrgRef = :ConcMatchRef order by dDate DESC;

    UPDATE tblAging2
    SET TruePeriod1 = :LookupPeriod
    WHERE tblAging2.ConcMatchRef = :ConcMatchRef;

    END LOOP;

    select * from tblAging2
    order by CustomerCode ASC, Original ASC, PPeriod DESC;

    END;


    Basically the stored proc receives 3 variables for data limitation purposes. I pre-created a table called tblAging2 and just delete the contents before filling it up again with a new dataset. The reason I'm creating a table is because I need to do a lookup for data (as in an excel vlookup). I then update the current table with the "new" data.

    Well that's what I was TRYING to do. Funny thing is only 1 line in the table updates.... ? As soon as I remove the part below in the code in the cursor and replace the variable with a static value it works fine????

    select TOP 1 PPeriod into :LookupPeriod from tblAging2 where ConcOrgRef = :ConcMatchRef order by dDate DESC;

    This doesn't make sense to me. I have exhausted my normal googling efforts and frankly there isn't much thorough documentation on Pervasive SQL to assist.

    Does anybody know why I can't do this? I've tried creating duplicate tables and using the one table for the cursor and update, and the other just for the variable "lookup", but still no luck.

    I wish Pervasive SQL worked like MS SQL... :-(

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    First, you typically won't get any improvement in speed by using a stored procedure in PSQL. PSQL doesn't precompile SPs like MS SQL.
    Second for the problem at hand, I would suggest some debugging code to make sure you are getting the values you expect. For example, I would add a "Print" command after the "SELECT TOP 1" command with the value of :LookupPeriod and :ConcMatchRef to make sure they are the values you are expecting. Once you've got the values from the running of the query, you can even try those values in the PCC to make sure the stored procedure is doing the right amount of work.
    Another thing you can try would be to chuck the idea of stored procedures and make sure the queries in the Excel are optimized.

    What version of PSQL are you using? You might be able to use a Temp table instead of pre-creating the table, deleting the records,and re-adding the recrords.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Mar 2007
    Posts
    56
    Hi mirtheil

    Thanks for the suggestions. It's P.SQL 8.6 btw.

    I wrote test scripts using static variables and the data is fine. As soon as I put it into the cursor things seemed to go belly-up.

    The reason I'm using a pre-created table is because on my first attempt at this when I created the table and then inserted lines into it I couldn't select from it in the stored proc. So I tried pre-creating the table to see if it would make a difference and it did. Very confusing I know. Pervasive SQL just doesn't seem consistent in it's behaviour and "works" by me doing strange things sometimes. :-(

    Thanks - I think I'll tell my colleague he needs to figure out a better way in Excel, and then I'm quitting my job and running as far away from Pervasive SQL as I can... ;-)

Posting Permissions

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