Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    25

    Smile Unanswered: Update Where Exists

    Hello everybody,

    Can anyone help me how to trace this error ...

    SQLWKS> UPDATE PCS_HEADER h
    2> set need = (SELECT w.need
    3> FROM pcfwork w, parts p
    4> WHERE (p.part_no = w.part_no
    5> OR p.art_part_no = w.part_no
    6> OR p.supp_part_no = w.part_no
    7> OR p.cust_part_no = w.part_no
    8> OR p.cae_part_no = w.part_no)
    9> and h.part_no = p.part_no
    10> and h.prodn_date = w.prodn_date)
    11> WHERE exists
    12> (SELECT w.need
    13> FROM pcfwork w, parts p
    14> WHERE (p.part_no = w.part_no
    15> OR p.art_part_no = w.part_no
    16> OR p.supp_part_no = w.part_no
    17> OR p.cust_part_no = w.part_no
    18> OR p.cae_part_no = w.part_no)
    19> and h.part_no = p.part_no
    20> and h.prodn_date = w.prodn_date)
    21>
    ORA-01427: single-row subquery returns more than one row


    tia,
    ynoel

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Update Where Exists

    Originally posted by ynoel
    Hello everybody,

    Can anyone help me how to trace this error ...

    SQLWKS> UPDATE PCS_HEADER h
    2> set need = (SELECT w.need
    3> FROM pcfwork w, parts p
    4> WHERE (p.part_no = w.part_no
    5> OR p.art_part_no = w.part_no
    6> OR p.supp_part_no = w.part_no
    7> OR p.cust_part_no = w.part_no
    8> OR p.cae_part_no = w.part_no)
    9> and h.part_no = p.part_no
    10> and h.prodn_date = w.prodn_date)
    11> WHERE exists
    12> (SELECT w.need
    13> FROM pcfwork w, parts p
    14> WHERE (p.part_no = w.part_no
    15> OR p.art_part_no = w.part_no
    16> OR p.supp_part_no = w.part_no
    17> OR p.cust_part_no = w.part_no
    18> OR p.cae_part_no = w.part_no)
    19> and h.part_no = p.part_no
    20> and h.prodn_date = w.prodn_date)
    21>
    ORA-01427: single-row subquery returns more than one row


    tia,
    ynoel
    The code in lines 12-20 returns more than one row.
    WHERE EXISTS tests TRUE or FALSE against either 0 or 1 row returned

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    it's not lines 2-10??
    With the OR statements you could be selecting the whole table and not know it.

    Run a test using the first select statement.
    on this:
    PHP Code:
    SELECT w.need
    FROM pcfwork w
    parts p
    WHERE 
    (p.part_no w.part_no
    OR p.art_part_no w.part_no
    OR p.supp_part_no w.part_no
    OR p.cust_part_no w.part_no
    OR p.cae_part_no w.part_no)
    and 
    h.part_no p.part_no
    and h.prodn_date w.prodn_date 
    If you run the update in sql-plus then it will tell you roughly what line has the error.
    Last edited by The_Duck; 10-27-03 at 15:08.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,

    Of course, it was looking for pcs_header h......

    SQL> SELECT w.need
    2 FROM pcfwork w, parts p
    3 WHERE
    4 (p.part_no = w.part_no
    5 OR p.art_part_no = w.part_no
    6 OR p.supp_part_no = w.part_no
    7 OR p.cust_part_no = w.part_no
    8 OR p.cae_part_no = w.part_no)
    9 and h.part_no = p.part_no
    10 and h.prodn_date = w.prodn_date;
    and h.prodn_date = w.prodn_date
    *
    ERROR at line 10:
    ORA-00904: invalid column name

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I know, take a row from pcs_header h, plug the corresponding values for part_no and prodn_date into the below query and see if you get more than one row.

    If you do, then you have tracked down the problem.

    So you would have:
    PHP Code:
    SELECT w.need
    FROM pcfwork w
    parts p
    WHERE 
    (p.part_no w.part_no
    OR p.art_part_no w.part_no
    OR p.supp_part_no w.part_no
    OR p.cust_part_no w.part_no
    OR p.cae_part_no w.part_no)
    p.part_no '123456789'  /* h.part_no */ 
    w.prodn_date to_date('010103','DDMMYY'/* h.prodn_date */ 
    Originally posted by ynoel
    Hi The_Duck,

    Of course, it was looking for pcs_header h......

    SQL> SELECT w.need
    2 FROM pcfwork w, parts p
    3 WHERE
    4 (p.part_no = w.part_no
    5 OR p.art_part_no = w.part_no
    6 OR p.supp_part_no = w.part_no
    7 OR p.cust_part_no = w.part_no
    8 OR p.cae_part_no = w.part_no)
    9 and h.part_no = p.part_no
    10 and h.prodn_date = w.prodn_date;
    and h.prodn_date = w.prodn_date
    *
    ERROR at line 10:
    ORA-00904: invalid column name
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2003
    Posts
    25
    Hello The_Duck,

    I got what you mean....

    SQLWKS> SELECT *
    2> FROM pcfwork w, parts p
    3> WHERE
    4> (p.part_no = w.part_no
    5> OR p.art_part_no = w.part_no
    6> OR p.supp_part_no = w.part_no
    7> OR p.cust_part_no = w.part_no
    8> OR p.cae_part_no = w.part_no) and
    9> p.part_no = 'COT-F 7 B L=295'
    10> and
    11> w.prodn_date = to_date('011003','DDMMYY');
    PART_NO PRODN_DATE NEED PAR_ID PART_NO CUST_PART_NO SUPP_PART_NO CAE_PART_NO ART_PART_NO PROTO_NO COMM_DESC_ AREA_CODE PAR PAR CO PCKG_SEQ_N UMC LT SL POS_SL_TOL NEG_SL_TOL SUPLR_CODE ORDER_NO_P PCKG_QTY PEAS MIN_PCKG_Q MIN_SUPLY_ NET_WT WT_ WHSE_CTRL_ ALTER_COMM_CODE ALTER_TARIF_HDG ALTER_RATE MIN_ORDER_ STD_ STD_ ST ST ST MONTH_END_ INVTY_DATE CREATED_BY DATE_CREATED MODIFIED_BY DATE_MODIFIED MFG_P ORDER_UNIT ORD
    ------------------------------ -------------------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- --- --- -- ---------- --- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- --------------- --------------- ---------- ---------- ---- ---- -- -- -- ---------- -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ----- ---------- ---
    COT-7-B L=295 01-OCT-03 0 5152 COT-F 7 B L=295 COT-7-B L=295 COTF 7 B L=295 COT-F 7 B L=295 COT-F 7 B L=295 CORRU. T 50 930 049 30 PCS 295 SAGARA 100 0 100 1 100 0 30-AUG-03 0 $
    COT-F 7 B L=295 01-OCT-03 0 5152 COT-F 7 B L=295 COT-7-B L=295 COTF 7 B L=295 COT-F 7 B L=295 COT-F 7 B L=295 CORRU. T 50 930 049 30 PCS 295 SAGARA 100 0 100 1 100 0 30-AUG-03 0 $
    2 rows selected.
    SQLWKS>

    Can you help me to trace all those with duplicate records in pcfwork. The result of sql above means there are two part number exists
    in pcfwork written in different format but they are both valid in parts table
    .

    thanks again
    Last edited by ynoel; 10-27-03 at 16:42.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    use this query by converting the columns and tablenames to what you need. this one will only show one duplicate row per duplicate.

    PHP Code:
    select 
    FROM COMPRESSOR_INTERVAL a
    WHERE ROWID 
    < (
    SELECT MAX(ROWID
    FROM COMPRESSOR_INTERVAL b
    WHERE b
    .org_id 'OL' AND
    b.ORG_ID a.ORG_ID AND 
    b.CUST_ID a.CUST_ID AND 
    b.PREM_SEQ_NBR a.PREM_SEQ_NBR AND
    b.THRMSTAT_SEQ_NBR a.THRMSTAT_SEQ_NBR AND 
    b.END_DT a.END_DT); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •