troubleshooting Question

Oracle query

Avatar of anumoses
anumosesFlag for United States of America asked on
Oracle Database
4 Comments1 Solution193 ViewsLast Modified:
CREATE TABLE DSS.SAMPLE_TAB1
(
  DIVNO         NUMBER(2),
  FSC           VARCHAR2(5 BYTE),
  PART_NUMBER   VARCHAR2(40 BYTE),
  SCREEN        VARCHAR2(10 BYTE),
  PO_MESSAGE    VARCHAR2(4000 BYTE),
  INSP_MESSAGE  VARCHAR2(4000 BYTE)
)

Insert into DSS.SAMPLE_TAB1
   (DIVNO, FSC, PART_NUMBER, SCREEN, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, '96906', 'MS20427M5-4', 'PO0010', 'THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.', 
    NULL);
COMMIT;

CREATE TABLE DSS.SAMPLE_TAB2
(
  PART  VARCHAR2(20 BYTE)                       NOT NULL,
  ID    NUMBER(38)                              NOT NULL,
  FSC   VARCHAR2(5 BYTE)
)

Insert into DSS.SAMPLE_TAB2
   (PART, ID, FSC)
 Values
   ('MS20427M5-4', 2683485, '96906');
COMMIT;

CREATE TABLE DSS.SAMPLE_TAB3
(
  ID                NUMBER(38)                  NOT NULL,
  DIV_NO            NUMBER(2)                   NOT NULL,
  MODUL_SHORT_NAME  VARCHAR2(10 BYTE)           NOT NULL,
  MESSAGE           VARCHAR2(4000 BYTE)         NOT NULL,
  ACTIVE            VARCHAR2(1 BYTE)            NOT NULL,
  PSPEC_ID          NUMBER(38),
  CUST_ID           NUMBER(38),
  VEND_ID           NUMBER(38)
)

Insert into DSS.SAMPLE_TAB3
   (ID, DIV_NO, MODUL_SHORT_NAME, MESSAGE, ACTIVE, 
    PSPEC_ID, CUST_ID, VEND_ID)
 Values
   (9948505115, 5, 'PO0010', 'Part MS20427M5-4 is under contract with SITE (Vendor Number: 123456) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: A B | Email: A.b@site.com | Contract Ref: AB1234 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 1LB PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT MUST BE PACKAGED 1 LB PER PKG. MUST BE PACKAGED 1LB PER PKG. MUST BE UNITIZED 1LB PER PKG.', 'A', 
    2683485, NULL, NULL);
COMMIT;

 select DIVNO                                                                 
         , FSC                                                                                                             
         , PART_NUMBER                                                                                                     
         , SCREEN                                                                                                     
         , UPPER(PO_MESSAGE) AS PO_MESSAGE                                                                                                   
         , UPPER(INSP_MESSAGE) AS INSP_MESSAGE            
      from sample_tab1
    WHERE ((PO_MESSAGE IS NOT NULL AND INSP_MESSAGE IS NOT NULL)
         OR (PO_MESSAGE IS NOT NULL OR INSP_MESSAGE IS NOT NULL))
         and part_number in ('MS20427M5-4')
      and  divno=5 
      and fsc in ('96906');
      
   
      select  *     
  from sample_tab2 
   where  part in ('MS20427M5-4')
   and fsc in ('96906');
   

   select * from sample_tab3
   where pspec_id = 2683485
   and div_no = 5
   and modul_short_name = 'PO0010'

   
 update sample_tab3
   set message = 'DELTA BUYER COMMENTS:'
    || decode(nvl('add new text','XYZ'),'XYZ',chr(10)||chr(10),chr(10)||chr(10)||'some message'||chr(10)||chr(10))
    || 'may be'
 where pspec_id = 2683485
   and modul_short_name = 'PO0010'        
   and div_no = 5;

Need to delete only

DELTA BUYER COMMENTS: PACKAGE 1LB PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT MUST BE PACKAGED 1 LB PER PKG. MUST BE PACKAGED 1LB PER PKG. MUST BE UNITIZED 1LB PER PKG.

and add

DELTA BUYER COMMENTS:  THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.

leaving

'Part MS20427M5-4 is under contract with SITE (Vendor Number: 123456) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: A B | Email: A.b@site.com | Contract Ref: AB1234 | 2017-08-08 :SS:

AS IS..

I have have to take instr but help is appreciated.
ASKER CERTIFIED SOLUTION
Sujith
Data Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros