Home » Developer & Programmer » Forms » ORA-01445 on execute_query (Forms 6i)
ORA-01445 on execute_query [message #396338] Mon, 06 April 2009 03:35 Go to next message
irlrobins
Messages: 5
Registered: June 2007
Junior Member
Morning all,

Having an issue with a form (Forms 6i) I'm trying to debug at the moment.

When an execute_query is performed to populate a datablock on the form, I get an ORA-01445: cannot select rowid from a join view without a key-preserved table error.

Now I know a solution is to not select rowid, but it's being selected by the execute_query and not explicitly selected.

The actual SQL statement is

SELECT ROWID,PAY_ADDR_1,PAY_ADDR_2,AMT,PAY_ADDR_3,PAY_ADDR_4,TAX_DEDUCTED_AMT,PTAX_DEDUCTED_AMT,PAYEE,PAYMENT_STATUS_CODE,PAYEE_TYPE,ASSOC_P AYMENT_NO,PAYMENT_NO,PAYMENT_STATUS_DESC,SECTION FROM V_PAYMENT_LIST1 WHERE PAYEE = 'XXXXXXXXX' AND PAYEE_TYPE = 'EMPR' AND PAYMENT_STATUS_CODE = 'PD' AND (SECTION = 'I' OR SECTION IS NULL) order by empe_name asc, empr_name asc, erep_name asc

V_PAYMENT_LIST1 is a view. It's create script is:

DROP VIEW MYSCHEMA.V_PAYMENT_LIST1;

CREATE OR REPLACE FORCE VIEW MYSCHEMA.V_PAYMENT_LIST1
(
PAYMENT_NO,
PAYMENT_STATUS_CODE,
PAYEE,
AMT,
PAYEE_TYPE,
PTAX_DEDUCTED_AMT,
TAX_DEDUCTED_AMT,
PAY_ADDR_1,
PAY_ADDR_2,
PAY_ADDR_3,
PAY_ADDR_4,
ASSOC_PAYMENT_NO,
SECTION,
PAYMENT_STATUS_DESC,
EMPE_NAME,
EMPR_NAME,
EREP_NAME
)
AS
SELECT PAYMENT.PAYMENT_NO,
PAYMENT.PAYMENT_STATUS_CODE,
PAYMENT.PAYEE,
PAYMENT.AMT,
PAYMENT.PAYEE_TYPE,
PAYMENT.PTAX_DEDUCTED_AMT,
PAYMENT.TAX_DEDUCTED_AMT,
PAYMENT.PAY_ADDR_1,
PAYMENT.PAY_ADDR_2,
PAYMENT.PAY_ADDR_3,
PAYMENT.PAY_ADDR_4,
PAYMENT.ASSOC_PAYMENT_NO,
PAYMENT.SECTION,
PAYMENT_STATUS.PAYMENT_STATUS_DESC,
EMPLOYEE_DTL.SURNAME || ', ' || EMPLOYEE_DTL.FIRSTNAME EMPE_NAME,
EMPLOYER_DTL.EMPLOYER_NAME EMPR_NAME,
EMPLOYERREP.EREP_NAME EREP_NAME
FROM PAYMENT,
PAYMENT_STATUS,
MYSCHEMA_employee_lookup EMPLOYEE_DTL,
MYSCHEMA_employer_lookup EMPLOYER_DTL,
MYSCHEMA_EREP_LOOKUP EMPLOYERREP
WHERE PAYMENT.PAYMENT_STATUS_CODE = PAYMENT_STATUS.PAYMENT_STATUS_CODE
AND EMPLOYEE_DTL.SSNUM(+) = PAYMENT.PAYEE
AND EMPLOYER_DTL.REG(+) = PAYMENT.PAYEE
AND EMPLOYERREP.REG_NO(+) = PAYMENT.PAYEE
WITH CHECK OPTION;


DROP PUBLIC SYNONYM V_PAYMENT_LIST1;

CREATE PUBLIC SYNONYM V_PAYMENT_LIST1 FOR MYSCHEMA.V_PAYMENT_LIST1;

GRANT SELECT ON MYSCHEMA.V_PAYMENT_LIST1 TO MYROLE1;

DESC for payment:

Name Null? Type
------------------------------- -------- ----
PAYMENT_NO NOT NULL NUMBER(6)
PAYMENT_STATUS_CODE NOT NULL VARCHAR2(6)
USERID NOT NULL VARCHAR2(10)
PAYEE NOT NULL VARCHAR2(9)
AMT NOT NULL NUMBER(11,2)
PAYEE_TYPE NOT NULL VARCHAR2(4)
PTAX_DEDUCTED_AMT NUMBER(11,2)
TAX_DEDUCTED_AMT NUMBER(11,2)
PAY_ADDR_1 NOT NULL VARCHAR2(35)
PAY_ADDR_2 VARCHAR2(35)
PAY_ADDR_3 VARCHAR2(35)
PAY_ADDR_4 VARCHAR2(35)
ASSOC_PAYMENT_NO NUMBER(6)
SECTION VARCHAR2(1)
LEVY_DEDUCTED_AMT NUMBER(11,2)

DESC for PAYMENT_STATUS:

Name Null? Type
------------------------------- -------- ----
PAYMENT_STATUS_CODE NOT NULL VARCHAR2(6)
PAYMENT_STATUS_DESC NOT NULL VARCHAR2(15)

DESC for MYSCHEMA_employee_lookup

Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID NUMBER
SSNUM VARCHAR2(12)
FIRSTNAME VARCHAR2(100)
SURNAME VARCHAR2(100)
USER_ID VARCHAR2(30)

DESC for MYSCHEMA_employer_lookup:

Name Null? Type
------------------------------- -------- ----
EMPLOYER_ID NUMBER
REG VARCHAR2(12)
EMPLOYER_NAME VARCHAR2(100)
USER_ID VARCHAR2(30)

DESC for MYSCHEMA_EREP_LOOKUP:

Name Null? Type
------------------------------- -------- ----
USER_ID VARCHAR2(30)
REG_NO VARCHAR2(20)
EREP_NAME VARCHAR2(80)

Is there anyway of getting the execute_query not to select rowid? Or any other way of preventing this error occuring?

Thanks
R
Re: ORA-01445 on execute_query [message #396521 is a reply to message #396338] Tue, 07 April 2009 01:46 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Use code tags and FORMAT YOUR CODE!!

Do you have ALL the columns of the primary keys in your query?

David
Re: ORA-01445 on execute_query [message #399231 is a reply to message #396338] Tue, 21 April 2009 03:43 Go to previous message
irlrobins
Messages: 5
Registered: June 2007
Junior Member
The formatting is not that bad. Razz

Sorry for delay in replying, only getting back to this now.

I resolved this issue by setting the DML Data Target Type on the datablock from 'table' to 'none'. This eliminated the problem and all seems fine now.

Thank you for your reply.

Robin
Previous Topic: Comparing Two Combo Boxes
Next Topic: error capture
Goto Forum:
  


Current Time: Wed Dec 07 12:28:03 CST 2016

Total time taken to generate the page: 0.23405 seconds