OraFAQ Forum: SQL & PL/SQL » need conditional quantity insert into table

Home » SQL & PL/SQL » SQL & PL/SQL » need conditional quantity insert into table (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

need conditional quantity insert into table [message #635206]

Tue, 24 March 2015 12:59

niteshtet
Messages: 4
Registered: March 2015

Junior Member

Hi I have a table having 6 coulmn, now I want data insert into 7th column i.e. depend upon existed data in column, kindly help me.
attached file having the information of data and output value.

Thanks in advance.

Attachment: insert_tag_in_column.csv
(Size: 1.11KB, Downloaded 1442 times)

Report message to a moderator

Re: need conditional quantity insert into table [message #635207 is a reply to message #635206]

Tue, 24 March 2015 13:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Words have meanings that matter.

INSERT adds new rows to tables.
UPDATE changes column values.

Report message to a moderator

Re: need conditional quantity insert into table [message #635225 is a reply to message #635207]

Tue, 24 March 2015 23:10

niteshtet
Messages: 4
Registered: March 2015

Junior Member

sorry, its update 7th column with conditional value depend upon rest of the column,
need cursor code, its urgent, please help me. sample data attached with first post 'Messages: 1' above.

Report message to a moderator

Re: need conditional quantity insert into table [message #635226 is a reply to message #635225]

Wed, 25 March 2015 01:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you need an urgent help then you should urgently read the links BlackSwan gave and provide a test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The faster you provide it, the faster you get the help.

Currently the only thing we can say you is:
update mytable set col7=<expression using col1, col2, col3, col4, col5 and col6>;
There is no need of a cursor.

Report message to a moderator

Re: need conditional quantity insert into table [message #635229 is a reply to message #635226]

Wed, 25 March 2015 05:16

niteshtet
Messages: 4
Registered: March 2015

Junior Member

CREATE TABLE TAG_TBL
(
OFFER_HEADER_ID NUMBER,
SECTION_NO NUMBER,
LEVEL_NO VARCHAR2(100 BYTE),
ITEM_DESC VARCHAR2(240 BYTE),
ITEM_QUANTITY NUMBER,
TAG_STATUS VARCHAR2(1 BYTE),
TAG_NO VARCHAR2(100 BYTE)
)

Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.1', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.2', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.3', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.13', 'WFE/BEL/CLI/6/60/2000/', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.14', 'MOT/TESQC/1.5/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.24', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 2, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.25', 'FAN/CEN-BC/BFI/27500/250/CEM/', 3, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 10, '10.26', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.1', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.2', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.3', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.13', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.14', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
(OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
Values
(76, 20, '20.15', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
COMMIT;

Output Before update

OFFER_HEADER_ID.SECTION_NO.LEVEL_NO.....ITEM_DESC.............................ITEM_QUANTITY.TAG_STATUS...TAG_NO
76............................10...............10.1.........BFI/RPJ/BH/CEM/25000/1.4/AMB.........1..............Y
76............................10...............10.2.........FAN/CEN-BC/BFI/27500/250/CEM/.......1..............Y
76............................10...............10.3.........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N
76............................10...............10.13........WFE/BEL/CLI/6/60/2000/...................1..............Y
76............................10...............10.14........MOT/TESQC/1.5/1440/415-50/FOM/S1..1..............N
76............................10...............10.24........BFI/RPJ/BH/CEM/25000/1.4/AMB.........2..............Y
76............................10...............10.25........FAN/CEN-BC/BFI/27500/250/CEM/.......3..............Y
76............................10...............10.26........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N
76............................20...............20.1.........BFI/RPJ/BH/CEM/25000/1.4/AMB..........1..............Y
76............................20...............20.2.........FAN/CEN-BC/BFI/27500/250/CEM/........1..............Y
76............................20...............20.3.........MOT/TESQC/30/1440/415-50/FOM/S1....1..............N
76............................20...............20.13.........BFI/RPJ/BH/CEM/25000/1.4/AMB.........1..............Y
76............................20...............20.14.........FAN/CEN-BC/BFI/27500/250/CEM/.......1..............Y
76............................20...............20.15.........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N

Output After update

OFFER_HEADER_ID.SECTION_NO.LEVEL_NO.....ITEM_DESC.............................ITEM_QUANTITY.TAG_STATUS...TAG_NO
76............................10...............10.1.........BFI/RPJ/BH/CEM/25000/1.4/AMB.........1..............Y..................10 BF-1-1
76............................10...............10.2.........FAN/CEN-BC/BFI/27500/250/CEM/.......1..............Y..................10F AN-1-1
76............................10...............10.3.........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N
76............................10...............10.13........WFE/BEL/CLI/6/60/2000/...................1..............Y................ ..10WFE.1-1
76............................10...............10.14........MOT/TESQC/1.5/1440/415-50/FOM/S1..1..............N
76............................10...............10.24........BFI/RPJ/BH/CEM/25000/1.4/AMB.........2..............Y..................10 BFI-2-3
76............................10...............10.25........FAN/CEN-BC/BFI/27500/250/CEM/.......3..............Y..................10F AN-2-4
76............................10...............10.26........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N
76............................20...............20.1.........BFI/RPJ/BH/CEM/25000/1.4/AMB..........1..............Y..................2 0BFI-1-1
76............................20...............20.2.........FAN/CEN-BC/BFI/27500/250/CEM/........1..............Y..................20 FAN-1-1
76............................20...............20.3.........MOT/TESQC/30/1440/415-50/FOM/S1....1..............N
76............................20...............20.13.........BFI/RPJ/BH/CEM/25000/1.4/AMB.........1..............Y..................2 0BFI-2-2
76............................20...............20.14.........FAN/CEN-BC/BFI/27500/250/CEM/.......1..............Y..................20 FAN-2-2
76............................20...............20.15.........MOT/TESQC/30/1440/415-50/FOM/S1...1..............N

Sir, here we need to update column TAG_NO where tag_status is 'Y',
that basis on each SECTION_NO,LEVEL_NO, ITEM_DESC (partial before first '/') AND ITEM_QUANTITY.

Condition like
section_no (i.e. 10) is mapped with level_no 10.1 to 10.26,

If any item_desc (partial before first '/') comes in level_no between 10.1 to 10.26,

it will comes in tag_no i.e. section_no||item_desc(partial before first '/')||'-'||previous(item_quantity)(first time 1)||'-'||last(item_quantity).(output is 10BF-1-1)
Like in first row 10BF-1-1 first time 1 to 1
Next time BFI again comes in level_no 10.24 with item_quantity(i.e. 2) in same section_no(i.e. 10)
then it will add in previous last quantity+1 to item_quantity(i.e. 2)+1. (output is 10BFI-2-3).

Attachment: insert_tag_table_data_new.txt
(Size: 5.13KB, Downloaded 1452 times)

[Updated on: Wed, 25 March 2015 06:00]

Report message to a moderator

Re: need conditional quantity insert into table [message #635231 is a reply to message #635229]

Wed, 25 March 2015 05:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Not sure why you have LEVEL_NO as a varchar2 column, it should be NUMBER with required precision. I used TO_NUMBER for proper sorting.

Also, the rules to get the last concatenation is not clear. Show an example why 10FAN-2-4 has 4 and not something else.

For the rest part, I think you just need SUBSTR, INSTR, CONCATENATION and Analytic ROW_NUMBER.

SQL> column item_desc format a30
SQL> column tab_no noprint
SQL> WITH data1 AS
  2    (SELECT t.*,
  3      section_no
  4      ||'-'
  5      || SUBSTR(item_desc, 1, instr(item_desc, '/', 1) -1) str
  6    FROM TAG_TBL t
  7    WHERE tag_status = 'Y'
  8    ORDER BY section_no,
  9      to_number(level_no)
 10    ),
 11    data2 AS
 12    (SELECT t.*,
 13      row_number() OVER(PARTITION BY section_no, str ORDER BY section_no, to_number(level_no)) rn
 14    FROM data1 t
 15    ORDER BY section_no,
 16      to_number(level_no)
 17    )
 18  SELECT t.*,
 19    str
 20    ||'-'
 21    ||rn tag_no_final
 22  FROM data2 t
 23  ORDER BY section_no,
 24    to_number(level_no)
 25  /

OFFER_HEADER_ID SECTION_NO LEVEL_N ITEM_DESC                      ITEM_QUANTITY T STR                RN TAG_NO_FIN
--------------- ---------- ------- ------------------------------ ------------- - ---------- ---------- ----------
             76         10 10.1    BFI/RPJ/BH/CEM/25000/1.4/AMB               1 Y 10-BFI              1 10-BFI-1
             76         10 10.13   WFE/BEL/CLI/6/60/2000/                     1 Y 10-WFE              1 10-WFE-1
             76         10 10.2    FAN/CEN-BC/BFI/27500/250/CEM/              1 Y 10-FAN              1 10-FAN-1
             76         10 10.24   BFI/RPJ/BH/CEM/25000/1.4/AMB               2 Y 10-BFI              2 10-BFI-2
             76         10 10.25   FAN/CEN-BC/BFI/27500/250/CEM/              3 Y 10-FAN              2 10-FAN-2
             76         20 20.1    BFI/RPJ/BH/CEM/25000/1.4/AMB               1 Y 20-BFI              1 20-BFI-1
             76         20 20.13   BFI/RPJ/BH/CEM/25000/1.4/AMB               1 Y 20-BFI              2 20-BFI-2
             76         20 20.14   FAN/CEN-BC/BFI/27500/250/CEM/              1 Y 20-FAN              1 20-FAN-1
             76         20 20.2    FAN/CEN-BC/BFI/27500/250/CEM/              1 Y 20-FAN              2 20-FAN-2

9 rows selected.

SQL>

Regards,
Lalit

Report message to a moderator

Re: need conditional quantity insert into table [message #635234 is a reply to message #635231]

Wed, 25 March 2015 06:32

niteshtet
Messages: 4
Registered: March 2015

Junior Member

sir,

Instead of to_number(LEVEL_NO) for order, I am using
"to_number(regexp_substr(level_no,'[0-9]+',1,1)),to_number(regexp_substr(level_no,'[0-9]+',1,2))"

you asked that why "10FAN-2-4" taken?
actually its based on quantity, if 10FAN already comes in same section then if again same item comes in same section then quantity will add with previous one. that shows start quantity to end quantity, here next time quantity defined 3 then if earlier was 1 then next it will be 2 to 4 (i.e. 3).

kindly help me.

Report message to a moderator

Previous Topic:	Query help
Next Topic:	Enforcing a particular execution plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:43:08 CDT 2024