Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Update with Sub Select

    I am trying to use the following query to update some records:

    Code:
    UPDATE PAC.SOF p
       SET p.sofmsg = (SELECT DECODE(INSTR(',14,17,24,27,87,',','||I.P_PMVACODE||','),0, p.sofmsg ||' ',
    	                  DECODE(I.P_FODENLEYIND,1,DECODE(I.P_DAFEHVIND,0,DECODE(I.P_DAFLEYIND, 0,'TRP-F ','TRP-D '),'TRP-D '),'TRP-D ') || p.sofmsg || ' ')
                         FROM pac.sof p
    		        , stsc.item i
    	            WHERE p.sofloc    = '&1'                      --dealer 
                          AND p.sofdate   = TO_DATE('&4', 'dd/mm/yy') --ohpost (DD/MM/RR)
                          AND p.difdate   = TO_DATE('&3', 'dd/mm/yy') --difdate (DD/MM/RR)
                          AND p.item      = i.item)
    I am basically trying to add "TRP-D" or "TRP-F" onto the front of every SOFMSG in PAC.SOF if the P_PMVACODE on the ITEM table is either 14,17,24,27, or 87, for a given SOFLOC, SOFDATE, and DIFDATE. I am getting this error: ORA-01427: single-row subquery returns more than one row

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Maybe by not 'duplicating' the pac.sof table?:

    Code:
    UPDATE PAC.SOF p
       SET p.sofmsg = (
           SELECT DECODE(INSTR(',14,17,24,27,87,',','||I.P_PMVACODE||','),0, p.sofmsg ||' ',
                  DECODE(I.P_FODENLEYIND,1,DECODE(I.P_DAFEHVIND,0,
                  DECODE(I.P_DAFLEYIND, 0,'TRP-F ','TRP-D '),'TRP-D '),'TRP-D ') || p.sofmsg || ' ')
             FROM stsc.item i
            WHERE i.item  = p.item)  
     WHERE p.sofloc    = '&1'                      --dealer 
       AND p.sofdate   = TO_DATE('&4', 'dd/mm/yy') --ohpost (DD/MM/RR)
       AND p.difdate   = TO_DATE('&3', 'dd/mm/yy') --difdate (DD/MM/RR)

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Bingo! Thanks

Posting Permissions

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