Looking for a solution in Jailer 3.4.3 that could pull out a valid subset of records from Oracle schema based on certain filtering criteria. By the term valid, I mean that it should maintain the Primary-foreign key relationship among the tables.
While trying to do that I was getting errors for Primary key not found. Created Primary key to all tables in the schema, proceeded to some extent. It again comes up with errors while exporting the subset data.
Error is: ORA-00904: "A"."ID_WF_ACTION": invalid identifier.
This error says that the column name entered is either missing or invalid. This is actually true. This column do not exist, in fact the table only does not exist. Unable to figure out as where is it taking the reference of this table from.
Can someone please help on this?
You seem to have CSS turned off.
Please don't fill out this field.
"A" is an alias for the source table in the SQL statements for collecting associated rows. Please take a look into sql.log file, you should find the incorrect statement there. Post it here. Btw, the current release 3.4.5 offers better error logging.
Thanks for your reply.
The sql.log shows the below qry.
Insert into JAILER_ENTITY_T (r_entitygraph, PK1, birthday, type) Select distinct 1170796854 as GRAPH_ID, B.ID AS PK1, 3 AS TODAY, 'cocpt_29mar10.EG_WF_ACTIONS' AS TYPE From cocpt_29mar10.EG_WF_ACTIONS B left join JAILER_ENTITY_T Duplicate on Duplicate.r_entitygraph=1170796854 and Duplicate.type='cocpt_29mar10.EG_WF_ACTIONS' and Duplicate.PK0 is null and Duplicate.PK1=B.ID and Duplicate.PK2 is null and Duplicate.PK3 is null and Duplicate.PK4 is null and Duplicate.PK5 is null, cocpt_29mar10.EGPAY_SALARYCODES A , JAILER_ENTITY_T E Where (E.r_entitygraph=1170796854 and E.birthday = 2 and E.type='cocpt_29mar10.EGPAY_SALARYCODES' and E.PK0 is null and E.PK1=A.ID and E.PK2 is null and E.PK3 is null and E.PK4 is null and E.PK5 is null) and Duplicate.type is null and (A.ID_WF_ACTION=B.ID)
2010-06-17 17:31:27,046 ERROR - Error executing statement
java.sql.SQLException: ORA-00904: "A"."ID_WF_ACTION": invalid identifier
This shows that "A" is alias to EGPAY_SALARYCODES table. When i describe this table, it shows as…
Name Null? Type
ID NOT NULL NUMBER(12)
HEAD NOT NULL VARCHAR2(64)
CATEGORYID NOT NULL NUMBER(12)
CREATEDBY NOT NULL NUMBER
CREATEDDATE NOT NULL DATE
LASTMODIFIEDBY NOT NULL NUMBER
LASTMODIFIEDDATE NOT NULL DATE
DESCRIPTION NOT NULL NVARCHAR2(1000)
IS_TAXABLE NOT NULL CHAR(1)
CAL_TYPE NOT NULL VARCHAR2(32)
ISATTENDANCEBASED NOT NULL VARCHAR2(1)
ISRECOMPUTED NOT NULL VARCHAR2(1)
ISRECURRING NOT NULL VARCHAR2(1)
CAPTURE_RATE NOT NULL VARCHAR2(1)
Obviously there is an association between "EG_WF_ACTIONS" and "EGPAY_SALARYCODES", where the join-condition is incorrect, as it refers to EGPAY_SALARYCODES.ID_WF_ACTION. (Does the table "EG_WF_ACTIONS" have a column "ID_WF_ACTION"? Maybe the aliases A and B are not correctly used in the join-condition) Did you define the association manually? Or did you modify an association after analysing the schema? If it is possible for you to send the data model. I would like to take a look into it. If you like, you may send the files in the "datamodel" folder to firstname.lastname@example.org Ralf
Is it possible that the column "EGPAY_SALARYCODES"."ID_WF_ACTION" exists in the schema you've analysed, but not in the schema from where you exported data from?
I am reanalysing the schema. As that finishes, I'll mail the files. I am doing the analysis and export using same schema.
Sign up for the SourceForge newsletter: