Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Unanswered: Access to Oracle equivalent

    Hi,

    UPDATE PRE_STG_FIF_GL_CROSS_REF A INNER JOIN STG_FIF_GL_CROSS_REF B ON (A.TRAN_REF_NO = B.TRAN_REF_NO) AND (A.LINE_TYPE = B.LINE_TYPE) AND (A.COST_RETAIL_FLAG = B.COST_RETAIL_FLAG) AND (A.TRAN_CODE = B.TRAN_CODE) AND (A.LOCATION = A.LOCATION) AND (A.SUBCLASS = B.SUBCLASS) AND (A.CLASS = B.CLASS) AND (A.DEPT = B.DEPT)
    SET B.DR_CCID = A.DR_CCID, B.DR_SEQUENCE1 = A.DR_SEQ1, B.DR_SEQUENCE2 = A.DR_SEQ2, B.DR_SEQUENCE3 = A.DR_SEQ3, B.DR_SEQUENCE4 = A.DR_SEQ4, B.DR_SEQUENCE5 = A.DR_SEQ5, B.DR_SEQUENCE6 = A.DR_SEQ6, B.DR_SEQUENCE7 = A.DR_SEQ7,B.CR_CCID = A.CR_CCID, B.CR_SEQUENCE2 = A.CR_SEQ2, B.CR_SEQUENCE3 = A.CR_SEQ3, B.CR_SEQUENCE4 = A.CR_SEQ4, B.CR_SEQUENCE5 = A.CR_SEQ5, B.CR_SEQUENCE6 = A.CR_SEQ6, B.CR_SEQUENCE7 = A.CR_SEQ7, B.LST_UPDT_ID = A.Userid, B.LST_UPDT_DTTM = A.Date_Time
    WHERE ((B.UPDT_ACT_CD)='I' Or (B.UPDT_ACT_CD)='U')

    I migrated the above access query to below oracle query and it gives me an error.

    UPDATE STG_FIF_GL_CROSS_REF c
    SET
    (
    DR_CCID,
    DR_SEQUENCE1, DR_SEQUENCE2, DR_SEQUENCE3, DR_SEQUENCE4, DR_SEQUENCE5, DR_SEQUENCE6, DR_SEQUENCE7,
    CR_CCID,
    CR_SEQUENCE1, CR_SEQUENCE2, CR_SEQUENCE3, CR_SEQUENCE4, CR_SEQUENCE5, CR_SEQUENCE6, CR_SEQUENCE7,
    LST_UPDT_ID,LST_UPDT_DTTM
    ) =
    (
    SELECT A.DR_CCID,
    A.DR_SEQ1,A.DR_SEQ2,A.DR_SEQ3,A.DR_SEQ4,A.DR_SEQ5, A.DR_SEQ6,A.DR_SEQ7,
    A.CR_CCID,
    A.CR_SEQ1,A.CR_SEQ2,A.CR_SEQ3,A.CR_SEQ4,A.CR_SEQ5, A.CR_SEQ6,A.CR_SEQ7,
    A.USERID,A.DATE_TIME
    FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A,STG_FIF_GL_CROSS_REF B WHERE
    B.TRAN_REF_NO = A.TRAN_REF_NO AND
    B.LINE_TYPE = A.LINE_TYPE AND
    B.TRAN_CODE = A.TRAN_CODE AND
    B.LOCATION = A.LOCATION AND
    B.SUBCLASS = A.SUBCLASS AND
    B.CLASS = A.CLASS AND
    B.DEPT = A.DEPT AND
    B.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG)
    WHERE (c.UPDT_ACT_CD='I' Or c.UPDT_ACT_CD='U')

    It throws,ORA-01427: single-row subquery returns more than one row. Please help me to convert this access query to oracle equivalent.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Your statement is wrong, try this one:
    Code:
    UPDATE STG_FIF_GL_CROSS_REF C
    SET
    DR_CCID = (SELECT A.DR_CCID 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE1 = (SELECT A.DR_SEQ1 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE2 = (SELECT A.DR_SEQ2 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE3 = (SELECT A.DR_SEQ3 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE4 = (SELECT A.DR_SEQ4 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE5 = (SELECT A.DR_SEQ5 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE6 = (SELECT A.DR_SEQ6 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    DR_SEQUENCE7 = (SELECT A.DR_SEQ7 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_CCID = (SELECT A.CR_CCID 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE1 = (SELECT A.CR_SEQ1 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE2 = (SELECT A.CR_SEQ2 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE3 = (SELECT A.CR_SEQ3 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE4 = (SELECT A.CR_SEQ4 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE5 = (SELECT A.CR_SEQ5 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE6 = (SELECT A.CR_SEQ6 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    CR_SEQUENCE7 = (SELECT A.CR_SEQ7 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    LST_UPDT_ID = (SELECT A.USERID 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG),
    LST_UPDT_DTTM = (SELECT A.DATE_TIME 
    		   FROM xrfsys.PRE_STG_FIF_GL_CROSS_REF A 
    		   WHERE
    		  		  C.TRAN_REF_NO = A.TRAN_REF_NO AND
    				  C.LINE_TYPE = A.LINE_TYPE AND
    				  C.TRAN_CODE = A.TRAN_CODE AND
    				  C.LOCATION = A.LOCATION AND
    				  C.SUBCLASS = A.SUBCLASS AND
    				  C.CLASS = A.CLASS AND
    				  C.DEPT = A.DEPT AND
    				  C.COST_RETAIL_FLAG = A.COST_RETAIL_FLAG)
    WHERE (C.UPDT_ACT_CD='I' Or C.UPDT_ACT_CD='U')
    Last edited by madafaka; 11-01-05 at 09:51.

Posting Permissions

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