Home » SQL & PL/SQL » SQL & PL/SQL » FIFO Algorithm to calculate Avg weighted value (Oracle 10g)
FIFO Algorithm to calculate Avg weighted value [message #652115] Thu, 02 June 2016 02:18 Go to next message
anil029
Messages: 15
Registered: February 2010
Junior Member
Hi All,
I have list of records which is basically the Trade positions taken for a particular Fund (Please refer to the sheet attached - Trade_book_PRICE_Calc.xls). I have to calculate a PRICE column for each of the positions. For OPEN positions (TRANSACTION_NOTES = OPL)the PRICE value would be same as corresponding ORIG_TRADE_PRICE. But for closed position (TRANSACTION_NOTES = CLL), the value of PRICE column would be calculated from ORIG_TRADE_PRICE of corresponding OPEN positions with FIFO - Average weighted price calculation method.

Please refer to the sheet attached - Trade_book_PRICE_Calc.xls. I have explained the calculation method in Explanation column.
Can someone help me write a SQL script which yields me the PRICE column based on the existing columns (First 5 columns) after applying all the calculation logic explained.

Many Thanks
Anil
Re: FIFO Algorithm to calculate Avg weighted value [message #652116 is a reply to message #652115] Thu, 02 June 2016 02:31 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
I would begin by creating an external table
http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153251
to read the file.

[Updated on: Thu, 02 June 2016 02:46] by Moderator

Report message to a moderator

Re: FIFO Algorithm to calculate Avg weighted value [message #652124 is a reply to message #652115] Thu, 02 June 2016 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you have some records in several lines you cannot use external tables.
You can load the file in a permanent table using SQL*Loader (I let you verify that explanation is filled):
SQL> create table trade (
  2    transaction_notes varchar2(3),
  3    fund              varchar2(3),
  4    trade_date        date,
  5    orig_trade_price  number,
  6    num_positions     integer,
  7    price             number,
  8    explanation       varchar2(1000)
  9  )
 10  /

Table created.

SQL> host type trade.ctl
LOAD DATA
INFILE 'C:\Trade_book_PRICE_Calc.csv'
CONTINUEIF NEXT PRESERVE(4) != ','
INTO TABLE trade
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS 
(
  transaction_notes CHAR,
  fund              CHAR,
  trade_date        DATE "DD/MM/YYYY",
  orig_trade_price  DECIMAL EXTERNAL,
  num_positions     INTEGER EXTERNAL,
  price             DECIMAL EXTERNAL,
  explanation       CHAR(1000)
)
SQL> host sqlldr michel/michel control=c:\trade.ctl skip=1

SQL*Loader: Release 11.2.0.4.0 - Production on Jeu. Juin 2 11:32:54 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 10

SQL> select transaction_notes, fund, trade_date, orig_trade_price, num_positions, price
  2  from trade
  3  order by trade_date;
TRA FUN TRADE_DATE ORIG_TRADE_PRICE NUM_POSITIONS      PRICE
--- --- ---------- ---------------- ------------- ----------
OPL ABC 20/12/2013          123.625           130    123.625
CLL ABC 07/01/2014       123.484375           130    123.625
OPL ABC 09/01/2014         123.1875            13   123.1875
OPL ABC 14/01/2014          124.375            18    124.375
CLL ABC 16/01/2014       123.984375             9   123.1875
CLL ABC 28/01/2014        124.71875            14  124.03571
OPL ABC 31/01/2014       125.765625             4 125.765625
OPL ABC 18/02/2014        125.65625            14  125.65625
CLL ABC 19/02/2014        126.03125             3    124.375
CLL ABC 25/02/2014        125.46875            23 125.396739

10 rows selected.

Quote:
the value of PRICE column would be calculated from ORIG_TRADE_PRICE of corresponding OPEN positions with FIFO - Average weighted price calculation method.


I don't know what is this.
Please post the result you want for the data you gave as I did it above.
Before, Please read and understand How to use [code] tags and make your code easier to read.

[Updated on: Thu, 02 June 2016 07:43]

Report message to a moderator

Re: FIFO Algorithm to calculate Avg weighted value [message #652133 is a reply to message #652124] Thu, 02 June 2016 07:34 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
There is a nice presentation on FIFO on the oracle site

https://docs.oracle.com/cd/E16582_01/doc.91/e15150/undstng_vluatn_calcln.htm#EOASV00116
Re: FIFO Algorithm to calculate Avg weighted value [message #652157 is a reply to message #652115] Thu, 02 June 2016 20:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
My understanding of the problem, which may or may not be correct, is that you have an Oracle table with five columns: transaction_notes, fund, trade_date, orig_trade_price, and num_positions, and you want a query to select those columns, and also produce a price column, calculated on a FIFO basis. The data sheet that you attached, containing the desired price column values, and an explanation column, explaining the derivation of the price column values is purely for explaining it to us and you do not need a SQL*Loader script or external table for loading that data into an Oracle table. Is this correct? If so, please see the demonstration below, in which I have provided a function that calculates the price, as per your explanations, and used that function in a query. This is the first thing that came to mind. There may be a way to simplify the function or use a query alone without a function.

-- data you provided:
SCOTT@orcl_12.1.0.2.0> SELECT transaction_notes, fund, trade_date, orig_trade_price, num_positions
  2  FROM   trade
  3  ORDER  BY trade_date
  4  /

TRA FUN TRADE_DATE      ORIG_TRADE_PRICE NUM_POSITIONS
--- --- --------------- ---------------- -------------
OPL ABC Fri 20-Dec-2013          123.625           130
CLL ABC Tue 07-Jan-2014       123.484375           130
OPL ABC Thu 09-Jan-2014         123.1875            13
OPL ABC Tue 14-Jan-2014          124.375            18
CLL ABC Thu 16-Jan-2014       123.984375             9
CLL ABC Tue 28-Jan-2014        124.71875            14
OPL ABC Fri 31-Jan-2014       125.765625             4
OPL ABC Tue 18-Feb-2014        125.65625            14
CLL ABC Wed 19-Feb-2014        126.03125             3
CLL ABC Tue 25-Feb-2014        125.46875            23

10 rows selected.


-- function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_price
  2    (p_transaction_notes  IN VARCHAR2,
  3  	p_fund		     IN VARCHAR2,
  4  	p_trade_date	     IN DATE,
  5  	p_orig_trade_price   IN NUMBER,
  6  	p_num_positions      IN NUMBER)
  7    RETURN			NUMBER
  8  AS
  9    v_price			NUMBER;
 10  BEGIN
 11    IF p_transaction_notes = 'OPL' THEN v_price := p_orig_trade_price;
 12    ELSE
 13  	 SELECT SUM (orig_trade_price) / SUM (ocv) price
 14  	 INTO	v_price
 15  	 FROM	(SELECT o.orig_trade_price, o.cv ocv,
 16  			ROW_NUMBER () OVER (ORDER BY o.trade_date) rn2
 17  		 FROM	(SELECT trade_date, orig_trade_price, cv,
 18  				ROW_NUMBER () OVER (ORDER BY trade_date) rn
 19  			 FROM	(SELECT trade_date, orig_trade_price, COLUMN_VALUE cv
 20  				 FROM	(SELECT *
 21  					 FROM	trade
 22  					 WHERE	transaction_notes = 'OPL'
 23  					 AND	fund = p_fund
 24  					 AND	trade_date < p_trade_date),
 25  					TABLE
 26  					  (CAST
 27  					     (MULTISET
 28  						(SELECT 1
 29  						 FROM	DUAL
 30  						 CONNECT BY LEVEL <= num_positions)
 31  					      AS SYS.ODCINUMBERLIST)))) o,
 32  			(SELECT trade_date, orig_trade_price, cv,
 33  				ROW_NUMBER () OVER (ORDER BY trade_date) rn
 34  			 FROM	(SELECT trade_date, orig_trade_price, COLUMN_VALUE cv
 35  				 FROM	(SELECT *
 36  					 FROM	trade
 37  					 WHERE	transaction_notes = 'CLL'
 38  					 AND	fund = p_fund
 39  					 AND	trade_date < p_trade_date),
 40  					TABLE
 41  					  (CAST
 42  					     (MULTISET
 43  						(SELECT -1
 44  						 FROM	DUAL
 45  						 CONNECT BY LEVEL <= num_positions)
 46  					      AS SYS.ODCINUMBERLIST)))) c
 47  		WHERE  o.rn = c.rn (+)
 48  		AND    c.rn IS NULL)
 49  	 WHERE rn2 <= p_num_positions;
 50    END IF;
 51    RETURN v_price;
 52  END get_price;
 53  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.


-- query:
SCOTT@orcl_12.1.0.2.0> SELECT transaction_notes, fund, trade_date, orig_trade_price, num_positions,
  2  	    get_price (transaction_notes, fund, trade_date, orig_trade_price, num_positions) price
  3  FROM   trade
  4  ORDER  BY trade_date
  5  /

TRA FUN TRADE_DATE      ORIG_TRADE_PRICE NUM_POSITIONS      PRICE
--- --- --------------- ---------------- ------------- ----------
OPL ABC Fri 20-Dec-2013          123.625           130    123.625
CLL ABC Tue 07-Jan-2014       123.484375           130    123.625
OPL ABC Thu 09-Jan-2014         123.1875            13   123.1875
OPL ABC Tue 14-Jan-2014          124.375            18    124.375
CLL ABC Thu 16-Jan-2014       123.984375             9   123.1875
CLL ABC Tue 28-Jan-2014        124.71875            14 124.035714
OPL ABC Fri 31-Jan-2014       125.765625             4 125.765625
OPL ABC Tue 18-Feb-2014        125.65625            14  125.65625
CLL ABC Wed 19-Feb-2014        126.03125             3    124.375
CLL ABC Tue 25-Feb-2014        125.46875            23 125.396739

10 rows selected.

Re: FIFO Algorithm to calculate Avg weighted value [message #652162 is a reply to message #652157] Fri, 03 June 2016 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and an explanation column, explaining the derivation of the price column values is purely for explaining it to us and you do not need a SQL*Loader script or external table for loading that data into an Oracle table.


Laughing I even did not read what's inside it. Laughing

Anyway, this learned me how to load such multiple lines data.

Re: FIFO Algorithm to calculate Avg weighted value [message #652175 is a reply to message #652162] Fri, 03 June 2016 11:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Michel,

I was able to load the data using SQL*Loader without using CONTINUEIF. I could have used an external table just as well. I did not encounter any wrapped lines. However, that may have had to do with how I copied and pasted the data into another file and, perhaps due to differences in operating systems, there may have been line breaks that your system recognized and mine did not.

Barbara
Re: FIFO Algorithm to calculate Avg weighted value [message #652176 is a reply to message #652175] Fri, 03 June 2016 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quite strange, when I open the downloaded CSV file with an hexadecimal editor, last record for instance, between "Hence PRICE value would" and "(5*124.375", I have 0x0D-0x0A (\r\n) so a new line for Windows.
If i remove the CONTINUEIF, SQL*Loader reports 12 records instead of 10 and 4 of them are bad which is consistent with the fact I see 2 double-line records (the 6th and the last ones).

[Updated on: Fri, 03 June 2016 11:55]

Report message to a moderator

Re: FIFO Algorithm to calculate Avg weighted value [message #652177 is a reply to message #652176] Fri, 03 June 2016 12:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Fri, 03 June 2016 09:54

Quite strange, when I open the downloaded CSV file with an hexadecimal editor, last record for instance, between "Hence PRICE value would" and "(5*124.375", I have 0x0D-0x0A (\r\n) so a new line for Windows.
If i remove the CONTINUEIF, SQL*Loader reports 12 records instead of 10 and 4 of them are bad which is consistent with the fact I see 2 double-line records (the 6th and the last ones).



I am baffled. I don't know if posting a copy and paste of what I did will shed any light on the problem or not, but here it is.

SCOTT@orcl_12.1.0.2.0> host type trade.ctl
options(skip=1)
LOAD DATA
INFILE Trade_book_PRICE_Calc.csv
INTO TABLE trade
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
transaction_notes CHAR,
fund              CHAR,
trade_date        DATE "DD/MM/YYYY",
orig_trade_price  DECIMAL EXTERNAL,
num_positions     INTEGER EXTERNAL,
price             DECIMAL EXTERNAL,
explanation       CHAR(1000)
)

SCOTT@orcl_12.1.0.2.0> create table trade (
  2    transaction_notes varchar2(3),
  3    fund              varchar2(3),
  4    trade_date        date,
  5    orig_trade_price  number,
  6    num_positions     integer,
  7    price             number,
  8    explanation       varchar2(1000)
  9  )
 10  /

Table created.

SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=trade.ctl log=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Jun 3 10:26:19 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 10

Table TRADE:
  10 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl_12.1.0.2.0> column explanation format a30 word_wrapped
SCOTT@orcl_12.1.0.2.0> select * from trade
  2  /

TRA FUN TRADE_DATE      ORIG_TRADE_PRICE NUM_POSITIONS      PRICE EXPLANATION
--- --- --------------- ---------------- ------------- ---------- ------------------------------
OPL ABC Fri 20-Dec-2013          123.625           130    123.625 Same as of ORIG_TRADE_PRICE
CLL ABC Tue 07-Jan-2014       123.484375           130    123.625 As No. of positions closed is
                                                                  same as no of positions open,
                                                                  the  PRICE value would be
                                                                  ORIG_TRADE_PRICE of
                                                                  corresponding OPEN positions

OPL ABC Thu 09-Jan-2014         123.1875            13   123.1875 Same as of ORIG_TRADE_PRICE
OPL ABC Tue 14-Jan-2014          124.375            18    124.375 Same as of ORIG_TRADE_PRICE
CLL ABC Thu 16-Jan-2014       123.984375             9   123.1875 No. of positions closed is 9.
                                                                  Hence the PRICE value would be
                                                                  ORIG_TRADE_PRICE of First In
                                                                  Open Positions (3rd row). As
                                                                  13>9 , the PRICE would be
                                                                  corresponding ORIG_TRADE_PRICE
                                                                  for all 9 closed positions.

CLL ABC Tue 28-Jan-2014        124.71875            14  124.03571 No. of positons closed is 14.
                                                                  First 4 positions would come
                                                                  from 3rd row (left afer
                                                                  previous closed position) and
                                                                  remaining 10 would come
                                                                  from 4th row. Hence PRICE
                                                                  value would be (4*123.1875 +
                                                                  10*124.375)/14 = 124.03571

OPL ABC Fri 31-Jan-2014       125.765625             4 125.765625 Same as of ORIG_TRADE_PRICE
OPL ABC Tue 18-Feb-2014        125.65625            14  125.65625 Same as of ORIG_TRADE_PRICE
CLL ABC Wed 19-Feb-2014        126.03125             3    124.375 These 3 positions would be
                                                                  closed from the 8 positions
                                                                  still remaining in 4th row.
                                                                  Hence PRICE value would be
                                                                  ORIG_TRADE_PRICE of 4th row.

CLL ABC Tue 25-Feb-2014        125.46875            23 125.396739 5 positons from 4th
                                                                  row(remained after previous
                                                                  closed positions),4 from 7th
                                                                  row and 14 from 8th row. Hence
                                                                  PRICE value would
                                                                  (5*124.375 + 4*125.765625 +
                                                                  14*125.65625)/23 = 125.396739


10 rows selected.

Re: FIFO Algorithm to calculate Avg weighted value [message #652178 is a reply to message #652177] Fri, 03 June 2016 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The display of your last row seems to also show a new line at the same place but maybe it is an illusion due to word_wrapped option.
Can you post dump(EXPLANATION,16) for this row.
The part "would%(5*124" is for me "77,6f,75,6c,64,28,35,2a,31,32,34" after loading with CONTINUEOF and "77 6F 75 6C 64 0D 0A 28 35 2A 31 32 34" in file.

Re: FIFO Algorithm to calculate Avg weighted value [message #652179 is a reply to message #652178] Fri, 03 June 2016 14:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I don't see any 0D 0A.

SCOTT@orcl_12.1.0.2.0> SELECT DUMP (explanation, 16) FROM trade
  2  /

DUMP(EXPLANATION,16)
----------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=134: 41,73,20,4e,6f,2e,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,73,61,6d,65,20,61,73,20,6
e,6f,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,6f,70,65,6e,2c,20,74,68,65,20,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64
,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45,20,6f,66,20,63,6f,72,72,65,73,70,6f,6e,64,69,6e,67,20,4f,50,45,4e,20,
70,6f,73,69,74,69,6f,6e,73

Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=203: 4e,6f,2e,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,39,2e,20,48,65,6e,63,65,20,74,68,6
5,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45,20,6f,66,20,46
,69,72,73,74,20,49,6e,20,4f,70,65,6e,20,50,6f,73,69,74,69,6f,6e,73,20,28,33,72,64,20,72,6f,77,29,2e,20,41,73,20,31,33,3e,39,20,2c,
20,74,68,65,20,50,52,49,43,45,20,77,6f,75,6c,64,20,62,65,20,63,6f,72,72,65,73,70,6f,6e,64,69,6e,67,20,4f,52,49,47,5f,54,52,41,44,4
5,5f,50,52,49,43,45,20,66,6f,72,20,61,6c,6c,20,39,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,73,2e

Typ=1 Len=219: 4e,6f,2e,20,6f,66,20,70,6f,73,69,74,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,31,34,2e,20,46,69,72,73,74,20,34,20,7
0,6f,73,69,74,69,6f,6e,73,20,77,6f,75,6c,64,20,63,6f,6d,65,20,66,72,6f,6d,20,33,72,64,20,72,6f,77,20,28,6c,65,66,74,20,61,66,65,72
,20,70,72,65,76,69,6f,75,73,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,29,20,61,6e,64,20,72,65,6d,61,69,6e,69,6e,67,20,31,30,
20,77,6f,75,6c,64,20,63,6f,6d,65,a,20,66,72,6f,6d,20,34,74,68,20,72,6f,77,2e,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65
,20,77,6f,75,6c,64,20,62,65,20,28,34,2a,31,32,33,2e,31,38,37,35,20,2b,20,31,30,2a,31,32,34,2e,33,37,35,29,2f,31,34,20,3d,20,31,32,
34,2e,30,33,35,37,31

Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=138: 54,68,65,73,65,20,33,20,70,6f,73,69,74,69,6f,6e,73,20,77,6f,75,6c,64,20,62,65,20,63,6c,6f,73,65,64,20,66,72,6f,6d,2
0,74,68,65,20,38,20,70,6f,73,69,74,69,6f,6e,73,20,73,74,69,6c,6c,20,72,65,6d,61,69,6e,69,6e,67,20,69,6e,20,34,74,68,20,72,6f,77,2e
,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,
45,20,6f,66,20,34,74,68,20,72,6f,77,2e

Typ=1 Len=183: 35,20,70,6f,73,69,74,6f,6e,73,20,66,72,6f,6d,20,34,74,68,20,72,6f,77,28,72,65,6d,61,69,6e,65,64,20,61,66,74,65,72,2
0,70,72,65,76,69,6f,75,73,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,73,29,2c,34,20,66,72,6f,6d,20,37,74,68,20,72,6f,77,20,61
,6e,64,20,31,34,20,66,72,6f,6d,20,38,74,68,20,72,6f,77,2e,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,
a,28,35,2a,31,32,34,2e,33,37,35,20,2b,20,34,2a,31,32,35,2e,37,36,35,36,32,35,20,2b,20,31,34,2a,31,32,35,2e,36,35,36,32,35,29,2f,32
,33,20,3d,20,31,32,35,2e,33,39,36,37,33,39


10 rows selected.

Re: FIFO Algorithm to calculate Avg weighted value [message #652182 is a reply to message #652179] Fri, 03 June 2016 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
77,6f,75,6c,64,a,28,35,2a,31,32,34

You indeed have a \n in your data but just \n with no \r.
I directly opened the file with the hexadecimal editor instead of downloading and then open it with another editor and there is indeed only \n. This was my editor which has automatically converted \n to \r\n.

So this is the explanation of the mystery.

Re: FIFO Algorithm to calculate Avg weighted value [message #652183 is a reply to message #652179] Fri, 03 June 2016 15:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
In place of your 0D 0A I see a which generates a linefeed in the output of the data after the word would, but apparently SQL*Loader did not see it as a line feed.


SCOTT@orcl_12.1.0.2.0> SELECT SUBSTR (explanation, INSTR (explanation, 'would'), 12) original,
2 DUMP (SUBSTR (explanation, INSTR (explanation, 'would'), 12), 16) the_dump
3 FROM trade
4 WHERE explanation LIKE '%5*124%'
5 /

ORIGINAL
------------------------------------------------
THE_DUMP
--------------------------------------------------------------------------------
would
(5*124
Typ=1 Len=12: 77,6f,75,6c,64,a,28,35,2a,31,32,34


1 row selected.
Re: FIFO Algorithm to calculate Avg weighted value [message #652185 is a reply to message #652182] Fri, 03 June 2016 15:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Thanks for solving the mystery and satisfying my curiosity. As you can see, I found the a and posted that before seeing your latest post.
Re: FIFO Algorithm to calculate Avg weighted value [message #652186 is a reply to message #652183] Fri, 03 June 2016 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but apparently SQL*Loader did not see it as a line feed.


Yes because SQL*Loader is smart enough to adapt the value of "new line" to the OS, so \n on Unix/Linux, \r\n on Windows, \n\r on Risc OS...

Re: FIFO Algorithm to calculate Avg weighted value [message #652187 is a reply to message #652186] Fri, 03 June 2016 15:13 Go to previous message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Newline story

Previous Topic: transpose and insert the data
Next Topic: Issue with UTL_FILE.INVALID_OPERATION
Goto Forum:
  


Current Time: Sun Aug 01 17:51:31 CDT 2021