Problem updating last record written at sort break in QTP
Tim Cummings
tim.cummings@frequencymarketing.com
Thu, 9 Dec 2004 13:43:13 -0500
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C4DE1E.F0810930
Content-Type: text/plain;
charset="iso-8859-1"
PS: I originally had "FNIAL" on the ITEM statement for LASTOO, that didn't
work either.
-----Original Message-----
From: Tim Cummings [mailto:tim.cummings@frequencymarketing.com]
Sent: Thursday, December 09, 2004 1:18 PM
To: Cognos Powerhouse List (E-mail)
Subject: Problem updating last record written at sort break in QTP
I have a QTP that writes some transactions and at the end I may have to
update the last
transaction written to adjust for rounding problems. Here is the scenario:
ORDER ITEM
XXX BR-CREDITS QUANTITY-ON-HAND ITEM-NO
1 101 1 10001059
XXX-RATIO
XXX PR-PREFIX XXX-RATIO
1 20 .33
1 20 .33
1 20 .34
All of the OO-TRX and OQ-TRX get written properly but the update at ITEM-NO
fails.
Using the exact key value given in the error message, I can find the
TRAN-KEY when
the QTP is finished.
Why does the QTP fail?
Here's the code:
CAN CLE
ACCESS ORDER-ITEMS &
LINK XXX OF ORDER-ITEMS &
TO PR-XXX OF XXX-RATIO
DEFINE SPOILED-CREDITS INTEGER SIGNED SIZE 4 = BR-CREDITS of order-items
&
* QUANTITY-ON-HAND of
order-items &
* (PR-PERCENT of XXX-ratio /
100)
TEMPORARY CREDIT-TOTAL NUM*8
SORT ON ITEM-NO
ITEM CREDIT-TOTAL SUBTOTAL SPOILED-CREDITS RESET AT ITEM-NO
OUTPUT TRANSACTIONS ADD NOITEMS ALIAS Oo-TRX
ITEM UNIT OF Oo-TRX INITIAL "9999999999"
ITEM TRAN-KEY OF Oo-TRX INITIAL "Oo" + ITEM-NO + PR-PREFIX
ITEM CREDITS OF Oo-TRX FINAL SPOILED-CREDITS
OUTPUT TRANSACTIONS UPDATE ALIAS LASTOo AT ITEM-NO &
VIA TRAN-KEY USING "Oo" + ITEM-NO + PR-PREFIX &
IF 0 <> (BR-CREDITS * QUANTITY-ON-HAND - CREDIT-TOTAL)
ITEM CREDITS OF LASTOo = CREDITS OF LASTOo+ ((BR-CREDITS *
QUANTITY-ON-HAND - CREDIT-TOTAL * -1))
GO
Executing request 1 ...
----------------------------------------------------------------------------
---
Cannot find record with this key/index value.
[3]
File: LASTOO
Linkitem: UNIT
Linkitem: LINK-UNIT
Linkitem: TRAN-KEY Oq1000105920
Action Taken: Run terminated.
----------------------------------------------------------------------------
---
Records read:
ORDER-ITEMS 5,790
XXX-RATIO 3
Transactions processed: 3
Records processed: Added Updated Unchanged
Deleted
ORDER-ITEMS 0 0 0
0
OO-TRX 3 0 0
0
LASTOO 0 0 0
0
Finished.
>
------_=_NextPart_001_01C4DE1E.F0810930
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>Problem updating last record written at sort break in =
QTP</TITLE>
<META content=3D"MSHTML 6.00.2800.1479" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D859263518-09122004>PS: I originally had "FNIAL" on =
the ITEM=20
statement for LASTOO, that didn't work either.</SPAN></FONT></DIV>
<BLOCKQUOTE>
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Tim Cummings=20
[mailto:tim.cummings@frequencymarketing.com]<BR><B>Sent:</B> =
Thursday,=20
December 09, 2004 1:18 PM<BR><B>To:</B> Cognos Powerhouse List=20
(E-mail)<BR><B>Subject:</B> Problem updating last record written at =
sort break=20
in QTP<BR><BR></FONT></DIV>
<P><FONT face=3D"Courier New" size=3D2>I have a QTP that writes some =
transactions=20
and at the end I may have to update the last </FONT><BR><FONT=20
face=3D"Courier New" size=3D2>transaction written to adjust for =
rounding=20
problems. Here is the scenario:</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2>ORDER ITEM</FONT> <BR><FONT=20
face=3D"Courier New" size=3D2> XXX BR-CREDITS =20
QUANTITY-ON-HAND ITEM-NO</FONT> <BR><FONT face=3D"Courier New"=20
size=3D2> =
1 =20
=
101 &nb=
sp; =20
1 10001059</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2>XXX-RATIO</FONT> <BR><FONT=20
face=3D"Courier New" size=3D2> XXX PR-PREFIX =
XXX-RATIO</FONT>=20
<BR><FONT face=3D"Courier New" size=3D2> =20
1 =20
20 .33</FONT> <BR><FONT=20
face=3D"Courier New" size=3D2> =20
1 =20
20 .33</FONT> <BR><FONT=20
face=3D"Courier New" size=3D2> =20
1 =20
20 .34</FONT> <BR><FONT=20
face=3D"Courier New" size=3D2> </FONT><BR><FONT face=3D"Courier =
New"=20
size=3D2>All of the OO-TRX and OQ-TRX get written properly but the =
update at=20
ITEM-NO fails.</FONT> <BR><FONT face=3D"Courier New" size=3D2>Using =
the exact key=20
value given in the error message, I can find the TRAN-KEY when</FONT> =
<BR><FONT face=3D"Courier New" size=3D2>the QTP is finished. =
</FONT></P>
<P><FONT face=3D"Courier New" size=3D2>Why does the QTP fail?</FONT> =
</P>
<P><FONT face=3D"Courier New" size=3D2>Here's the code:</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2>CAN CLE</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2> ACCESS =
ORDER-ITEMS=20
&</FONT> <BR><FONT face=3D"Courier New" =
size=3D2> LINK=20
XXX OF ORDER-ITEMS &</FONT> <BR><FONT face=3D"Courier New"=20
size=3D2> TO PR-XXX OF =
XXX-RATIO</FONT>=20
<BR><FONT face=3D"Courier New" size=3D2> </FONT><BR><FONT =
face=3D"Courier New" size=3D2> </FONT><BR><FONT face=3D"Courier =
New"=20
size=3D2> DEFINE SPOILED-CREDITS INTEGER SIGNED SIZE 4 =
=3D BR-CREDITS=20
of order-items &</FONT> <BR><FONT face=3D"Courier New"=20
=
size=3D2> &nb=
sp; &nb=
sp; &nb=
sp; &nb=
sp;=20
* QUANTITY-ON-HAND of order-items &</FONT> <BR><FONT =
face=3D"Courier New"=20
size=3D2> &=
nbsp; &=
nbsp; &=
nbsp; &=
nbsp;=20
* (PR-PERCENT of XXX-ratio / 100) </FONT></P>
<P><FONT face=3D"Courier New" size=3D2> TEMPORARY =
CREDIT-TOTAL=20
NUM*8</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2> SORT ON =
ITEM-NO</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2> ITEM CREDIT-TOTAL =
SUBTOTAL=20
SPOILED-CREDITS RESET AT ITEM-NO</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2> OUTPUT =
TRANSACTIONS ADD=20
NOITEMS ALIAS Oo-TRX</FONT> <BR><FONT face=3D"Courier New"=20
size=3D2> ITEM=20
UNIT OF =
Oo-TRX=20
INITIAL "9999999999"</FONT> <BR><FONT face=3D"Courier New"=20
size=3D2> ITEM =
TRAN-KEY OF=20
Oo-TRX INITIAL "Oo" + ITEM-NO + PR-PREFIX</FONT> <BR><FONT =
face=3D"Courier New"=20
size=3D2> ITEM=20
CREDITS OF Oo-TRX =
FINAL =20
SPOILED-CREDITS</FONT> </P>
<P><FONT face=3D"Courier New" size=3D2> OUTPUT =
TRANSACTIONS=20
UPDATE ALIAS LASTOo AT ITEM-NO &</FONT> <BR><FONT face=3D"Courier =
New"=20
size=3D2> VIA TRAN-KEY USING "Oo" =
+ ITEM-NO=20
+ PR-PREFIX &</FONT> <BR><FONT face=3D"Courier New"=20
size=3D2> IF 0 <> =
(BR-CREDITS *=20
QUANTITY-ON-HAND - CREDIT-TOTAL)</FONT> <BR><FONT face=3D"Courier =
New"=20
size=3D2> ITEM CREDITS OF LASTOo =3D CREDITS OF =
LASTOo+=20
((BR-CREDITS * QUANTITY-ON-HAND - CREDIT-TOTAL * -1))</FONT> =
<BR><FONT=20
face=3D"Courier New" size=3D2>GO</FONT> </P><BR>
<P><FONT face=3Dr_ansi size=3D2>Executing request 1 ...</FONT> =
<BR><FONT=20
face=3Dr_ansi=20
=
size=3D2>---------------------------------------------------------------=
----------------</FONT>=20
<BR><FONT face=3Dr_ansi size=3D2>Cannot find record with this =
key/index=20
=
value. =
=
=20
[3] </FONT><BR><FONT face=3Dr_ansi size=3D2> File: =20
LASTOO</FONT><BR><FONT face=3Dr_ansi =
size=3D2> =20
Linkitem: UNIT</FONT> <BR><FONT face=3Dr_ansi=20
size=3D2> Linkitem: LINK-UNIT</FONT> =
<BR><FONT=20
face=3Dr_ansi size=3D2> Linkitem: =20
=
TRAN-KEY &nbs=
p; &nbs=
p; =20
Oq1000105920</FONT> </P>
<P><FONT face=3Dr_ansi size=3D2>Action Taken: Run terminated.</FONT> =
<BR><FONT=20
face=3Dr_ansi=20
=
size=3D2>---------------------------------------------------------------=
----------------</FONT>=20
</P>
<P><FONT face=3Dr_ansi size=3D2>Records read:</FONT> <BR><FONT =
face=3Dr_ansi=20
size=3D2> =20
=
ORDER-ITEMS &=
nbsp; &=
nbsp; =20
5,790</FONT> <BR><FONT face=3Dr_ansi size=3D2> =20
=
XXX-RATIO &nb=
sp; &nb=
sp; =20
3</FONT> </P>
<P><FONT face=3Dr_ansi size=3D2>Transactions=20
=
processed: &n=
bsp; =20
3</FONT> </P>
<P><FONT face=3Dr_ansi size=3D2>Records=20
=
processed: &n=
bsp; =20
Added Updated Unchanged =20
Deleted</FONT> <BR><FONT face=3Dr_ansi size=3D2> =20
=
ORDER-ITEMS &=
nbsp; &=
nbsp; =20
0 =20
0 =20
0 0</FONT> =
<BR><FONT=20
face=3Dr_ansi size=3D2> =20
=
OO-TRX =
=
=20
3 =20
0 =20
0 =20
0</FONT> <BR><FONT face=3Dr_ansi size=3D2> =20
=
LASTOO =
=
=20
0 =20
0 =20
0 0</FONT> </P>
<P><FONT face=3Dr_ansi size=3D2>Finished.</FONT> <BR><FONT =
face=3Dr_ansi size=3D2>>=20
</FONT></P><BR><BR><BR><BR><BR></BLOCKQUOTE></BODY></HTML>
------_=_NextPart_001_01C4DE1E.F0810930--