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... :-(

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.

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... ;-)