Home » SQL & PL/SQL » SQL & PL/SQL » Load data (11.2.0.3, 64-bit, Windows 2003)
Load data [message #645564] Tue, 08 December 2015 16:31 Go to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Hi friends,
I'm trying to load records into table rules from table product with the below criteria..

create table product(
  prod_id varchar2(20),
  prod_grp  varchar2(20),
  from_amt  number(10),
  to_amt  number(10),
  share_amt number(10)
);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10037', 'STK', 1, 18);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10037', 'NSTK', 1, 16.2);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10038', 'NSTK', 1, 5000, 12);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10038', 'STK', 5001, 10000, 16);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values  ('10038', 'STK', 10001, 20);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10039', 'NSTK', 1, 8000, 10);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10039', 'STK', 8001, 12);
 
create table rules (
  rule_id varchar2(30),
  rule_grp  varchar2(10),
  rate_1 number(10),
  point_1  number(10),
  rate_2   number(10),
  point_2   number(10),
  rate_3 number(10),
  point_3 number(10)
  );
 
Criteria to load into table Rules:
 
rule_id   - 'RL' || product.prod_id
rule_grp  - product.prod_grp
rate_1    - product.share_amt where from_amt = 1
point_1   - product.to_amt
rate_2  - If product.to_amt in point_1 is not NULL then find product.share_amt from product records where rule_id=prod_id and point_1=from_amt+1
point_2   - If product.to_amt in point_1 is not NULL then find product.to_amt  from product records where rule_id=prod_id and point_1=from_amt+1
rate_3    - If product.to_amt in point_2 is not NULL then find product.share_amt from product records where rule_id=prod_id and point_2=from_amt+1
point_3  - If product.to_amt in point_2 is not NULL then find product.to_amt from product records where rule_id=prod_id and point_2=from_amt+1
 
I tried to load first few columns (rule_id,rule_grp,rate_1,point_1,rate_2,point_2) via sql loader.
 
SQL> select  * from product;
PROD_ID              PROD_GRP               FROM_AMT     TO_AMT  SHARE_AMT
-------------------- -------------------- ---------- ---------- ----------
10037                STK                           1                    18
10037                NSTK                          1                    16
10038                NSTK                          1       5000         12
10038                STK                        5001      10000         16
10038                STK                       10001                    20
10039                NSTK                          1       8000         10
10039                STK                        8001                    12
 
product.dat
PROD_ID|PROD_GRP|FROM_AMT|TO_AMT|SHARE_AMT
'10037'|'STK'|1||18
'10037'|'NSTK'|1||16.2
'10038'|'NSTK'|1|5000|12
'10038'|'STK'|5001|10000|16
'10038'|'STK'|10001||20
'10039'|'NSTK'|1|8000|10
'10039'|'STK'|8001||12
 
product.ctl
options(skip=1)
load data
into table Rules
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(rule_id		POSITION(1) " 'RL' || :rule_id"	
,rule_grp
,from_amt		BOUNDFILLER
,point_1
,share_amt		BOUNDFILLER
,rate_1			"CASE WHEN :from_amt=1 THEN :share_amt END"
,rate_2			expression "(select pr.share_amt from product pr where :point_1 is not null and pr.prod_id=:rule_id and :point_1=:from_amt+1)"
,point_2		expression "(select pr.to_amt from product pr where :point_1 is not null and pr.prod_id=:rule_id and :point_1=:from_amt+1)"
)

It did not any load values in rate_2,point_2...No errors either..Not sure if there is any other method to do this..

Please give your suggestions.. Thank you so much for your time

Report message to a moderator

Re: Load data [message #645565 is a reply to message #645564] Tue, 08 December 2015 17:16 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why on earth are you using sqlloader if the source of the data is already in the DB?

Report message to a moderator

Re: Load data [message #645566 is a reply to message #645565] Tue, 08 December 2015 17:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
"simple" INSERT INTO RULES SELECT * FROM PRODUCT WHERE ...... will produce desired results

Report message to a moderator

Re: Load data [message #645567 is a reply to message #645566] Tue, 08 December 2015 18:41 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Sorry about the confusion.. I'm copying all the table information in one post below..

create table product(
  prod_id varchar2(20),
  prod_grp  varchar2(20),
  from_amt  number(10),
  to_amt  number(10),
  share_amt number(10)
);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10037', 'STK', 1, 18);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10037', 'NSTK', 1, 16.2);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10038', 'NSTK', 1, 5000, 12);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10038', 'STK', 5001, 10000, 16);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values  ('10038', 'STK', 10001, 20);
Insert into product (prod_id, prod_grp, from_amt, to_amt, share_amt) Values ('10039', 'NSTK', 1, 8000, 10);
Insert into product (prod_id, prod_grp, from_amt, share_amt) Values ('10039', 'STK', 8001, 12);

create table rules (
  rule_id varchar2(30),
  rule_grp  varchar2(10),
  rate_1 number(10),
  point_1  number(10),
  rate_2  number(10),
  point_2  number(10),
  rate_3 number(10),
  point_3 number(10)
  );

Criteria to load into table Rules:

rule_id  - 'RL' || product.prod_id
rule_grp  - product.prod_grp
rate_1    - product.share_amt where from_amt = 1
point_1  - product.to_amt
rate_2    -  If product.to_amt in point_1 is not NULL then find product.share_amt from the next record with same rule_id/prod_id where from_amt(of 
             next record)=to_amt(current record-point_1)+1
point_2  - If product.to_amt in point_1 is not NULL then find product.to_amt  from the next record with same rule_id/prod_id where from_amt(of 
           next record)=to_amt(current record-point_1)+1
rate_3    - If product.to_amt in point_2 is not NULL then find product.share_amt from the next record with same rule_id/prod_id where from_amt(of 
           next record)=to_amt(current record- point_2)+1
point_3  - If product.to_amt in point_2 is not NULL then find product.to_amt from the next record with same rule_id/prod_id where from_amt(of next 
           record)=to_amt(current record- point_2)+1

SQL> select * from product;

PROD_ID              PROD_GRP               FROM_AMT     TO_AMT  SHARE_AMT
-------------------- -------------------- ---------- ---------- ----------
10037                STK                           1                    18
10037                NSTK                          1                    16
10038                NSTK                          1       5000         12
10038                STK                        5001      10000         16
10038                STK                       10001                    20
10039                NSTK                          1       8000         10
10039                STK                        8001                    12


I tried using insert but not sure how to insert values in rate_2,point_2,rate_3, point_3 columns.. For example, when point_1 (or pr.to_amt) is not null, need to do a find for other records with same rule_id(if exists) and if pr.from_amt of next record =current pr.to_amt+1 then RATE_2 will be pr.share_amt of the next record...

SQL> insert into rules (
2 rule_id,
3 rule_grp,
4 rate_1,
5 point_1,
6 rate_2,
7 point_2,
8 rate_3,
9 point_3)
10 select
11 'RL' || pr.prod_id RULE_ID,
12 pr.prod_grp RULE_GRP,
13 CASE WHEN pr.from_amt=1 THEN pr.share_amt END RATE_1,
14 pr.to_amt POINT_1,
15 (select pr.share_amt from product pr where point_1 is not null and rules.rule_id=pr.prod_id and point_1 = pr.from_amt+1) RATE_2,
16 (select pr.to_amt from product pr where point_1 is not null and rules.rule_id=pr.prod_id and point_1 = pr.from_amt+1) POINT_2,
17 (select pr.share_amt from product pr where point_2 is not null and rules.rule_id=pr.prod_id and point_2 = pr.from_amt+1) RATE_3,
18 (select pr.to_amt from product pr where point_2 is not null and rules.rule_id=pr.prod_id and point_2 = pr.from_amt+1) POINT_3
19 from product pr;
(select pr.share_amt from product pr where point_1 is not null and point_1 = pr.from_amt+1) RATE_2,
*
ERROR at line 15:
ORA-00904: "POINT_1": invalid identifier

Result of rules table should look like:

SQL> col rule_id format a12
SQL> select rule_id,rule_grp,rate_1,point_1,rate_2,point_2 from rules order by rule_id;

RULE_ID      RULE_GRP       RATE_1    POINT_1     RATE_2    POINT_2
------------ ---------- ---------- ---------- ---------- ----------
RL10037      STK                18
RL10037      NSTK               16
RL10038      NSTK               12       5000         16      10000
RL10038      STK                        10000         20
RL10038      STK
RL10039      NSTK               10       8000         12
RL10039      STK


Please help.. Thanks a lot

[Updated on: Tue, 08 December 2015 19:31]

Report message to a moderator

Re: Load data [message #645568 is a reply to message #645567] Tue, 08 December 2015 21:53 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
I've tried to use case statement but need your help to insert rate_1,point_2,rate_3,point_3 fields..
insert into rules (
rule_id,
rule_grp,
rate_1,
point_1,
rate_2,
point_2,
rate_3,
point_3
)
select
  'RL' || pr.prod_id  RULE_ID,
  pr.prod_grp  RULE_GRP,
  CASE WHEN pr.from_amt=1 THEN pr.share_amt END RATE_1,
  pr.to_amt POINT_1,
  case when pr.to_amt is not null and pr.from_amt=pr.to_amt+1 THEN pr.share_amt END     RATE_2,
  case when pr.to_amt is not null and pr.from_amt=pr.to_amt+1 THEN pr.to_amt END      POINT_2,
  case when point_2 is not null and pr.from_amt=pr.to_amt+1 THEN pr.share_amt END    RATE_3,
  case when point_2 is not null and pr.from_amt=pr.to_amt+1 THEN pr.to_amt END   POINT_3
from product pr;


Please need your expert advice with this script..

In the 1st case statement, (pr.from_amt=pr.to_amt+1) - pr.from_amt is the value of next row in product table with
same product.prod_id (if exists). I'm not sure how to look for pr.from_amt in the next row of product.prod_id

In the 2nd case statement, (pr.from_amt=pr.to_amt+1) - pr.from_amt is the value of next row in product table with
same product.prod_id (if exists). I'm not sure how to look for pr.from_amt in the next row of product.prod_id

In the 3rd case statement, check if point_2(result value from the 2nd case statement) is not null.
Then check (pr.from_amt=pr.to_amt+1) where pr.from_amt is the value of next row in product table with
same product.prod_id (if exists). I'm not sure how to look for pr.from_amt in the next row of product.prod_id

In the 4th case statement, check if point_2(result value from the 2nd case statement) is not null.
Then check (pr.from_amt=pr.to_amt+1) where pr.from_amt is the value of next row in product table with
same product.prod_id (if exists). I'm not sure how to look for pr.from_amt in the next row of product.prod_id

This is the source data:

SQL> select * from product;

PROD_ID              PROD_GRP               FROM_AMT     TO_AMT  SHARE_AMT
-------------------- -------------------- ---------- ---------- ----------
10037                STK                           1                    18
10037                NSTK                          1                    16
10038                NSTK                          1       5000         12
10038                STK                        5001      10000         16
10038                STK                       10001                    20
10039                NSTK                          1       8000         10
10039                STK                        8001                    12
 
REsult in rules should look like:

SQL> select rule_id,rate_1,point_1,rate_2,point_2,rate_3,point_3 from rules orde
r by rule_id;

RULE_ID          RATE_1    POINT_1     RATE_2    POINT_2     RATE_3    POINT_3
------------ ---------- ---------- ---------- ---------- ---------- ----------
RL10037              18
RL10037              16
RL10038              12       5000         16      10000         20
RL10038                      10000         20
RL10038
RL10039              10       8000         12
RL10039


Please help..Thank you all

[Updated on: Tue, 08 December 2015 21:55]

Report message to a moderator

Re: Load data [message #645570 is a reply to message #645568] Wed, 09 December 2015 00:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
I'm not sure how to look for pr.from_amt in the next row of product.prod_id


Have a look at LEAD function.

Report message to a moderator

Re: Load data [message #645593 is a reply to message #645570] Wed, 09 December 2015 08:54 Go to previous message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you Michel. I tried with the insert script below.. 
insert into rules (
rule_id,
rule_grp,
rate_1,
point_1,
rate_2,
point_2,
rate_3,
point_3
)
with res as 
(
select
   'RL' || pr.prod_id  RULE_ID,
   pr.prod_grp  RULE_GRP,
   CASE WHEN pr.from_amt=1 THEN pr.share_amt END RATE_1,
   pr.to_amt POINT_1,
   (select pr.from_amt 
		(
		  select LEAD(pr.from_amt,1) over (ORDER BY pr.prod_id) from product pr
		) next_from_amt
   ),
   pr.to_amt TO_AMT,
   pr.share_amt SHARE_AMT   	
from product pr
)
select 
	rule_id,
	rule_grp,
	rate_1,
	point_1,
	CASE WHEN res.point_1 is NOT NULL and res.next_from_amt = res.to_amt+1 THEN res.share_amt END RATE_2,
	CASE WHEN res.point_1 is NOT NULL and res.next_from_amt = res.to_amt+1 THEN res.to_amt END POINT_2,
	..... -- check if point_2 in above line is not null and pr.from_amt=pr.to_amt+1 THEN pr.share_amt END RATE_3,
        ..... -- check if point_2 above is not null and pr.from_amt=pr.to_amt+1 THEN pr.to_amt END  POINT_3
	
from res
/


Can you please show me how I can check if point_1(value inserted for the previous column) is not null? Please help... Thanks a lot

Report message to a moderator

Previous Topic: Comma separated values
Next Topic: Getting Error inListagg
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 28 12:08:46 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.