on 01-25-2010 5:45 PM
Hi All!
I´m updating a taget table from a source table using the Table Comparison object, but I want to avoid updating when the source COLUMN is NULL
I can exclude record using a Query, but I dont know how to exclude individual columns using the Table Comparison transform.
any idea?
Thanks in advance, Pablo.
Hello,
How about using auto correct load? It gives the option to Ignore columns with value, and Ignore columns with null.
you can do a search on the technical manual on "auto correct load"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks noob, I tried auto correct load option and "Ignore columns with null" = Yes defined in the target table with not success.
My idea is to avoid replacing the target value (ie: "ADDRESS1") with a NULL source value.
what do you suggest? do i must be verifing something else?
Thanks for your time!
Thanks Manoj!
1) how can i get the optimized SQL in order to review what is sent to the DB?
2) do you know where can i get more information about the auto correct load issue?
I found there is a SP1 for version 12.2, but when trying to get the document : SAP BusinessObjects XI 3.2 Service Pack 1 Data Services Release Notes from help.sap.com I need to authenticate.
3) How can i get this pdf?
Thanks in advance! Pablo.
Edited by: pablo.erba on Jan 28, 2010 12:19 PM
I need to look at the Optimised SQL that is getting generated first, then only I can confirm if you are running into that issue or not
to view the optimised SQL open the Data Flow (Drill down into Data flow), click on Validation Menu option and select Dispaly Optimized SQL
This will open a New window with Optimized SQL, copy that, and post that
The Fix was Internal Fix so I don't think its documented in the Release Notes
The issue was since the Syntax was incorrect, the Auto Correct was not working ? Inserts and Updates were not happening, is this happening in you case ?
This is the optimizex SQL:
SELECT "ENTITY_1"."BUSINESS_TYPE_CODE" , "ENTITY_1"."COUNTY" , "ENTITY_1"."NATURE_OF_BUSINESS" , "ENTITY_1"."WC_FILING_NUMBER" , "ENTITY_1"."ENTITY_ID" , "ENTITY_1"."ENTITY_TABLE_ID" , "ENTITY_1"."LAST_NAME" , "ENTITY_1"."LAST_NAME_SOUNDEX" , "ENTITY_1"."FIRST_NAME" , "ENTITY_1"."ALSO_KNOWN_AS" , "ENTITY_1"."ABBREVIATION" , "ENTITY_1"."COST_CENTER_CODE" , "ENTITY_1"."ADDR1" , "ENTITY_1"."ADDR2" , "ENTITY_1"."CITY" , "ENTITY_1"."COUNTRY_CODE" , "ENTITY_1"."STATE_ID" , "ENTITY_1"."ZIP_CODE" , "ENTITY_1"."PARENT_EID" , "ENTITY_1"."TAX_ID" , "ENTITY_1"."EMAIL_TYPE_CODE" , "ENTITY_1"."EMAIL_ADDRESS" , "ENTITY_1"."SEX_CODE" , "ENTITY_1"."BIRTH_DATE" , "ENTITY_1"."PHONE1" , "ENTITY_1"."PHONE2" , "ENTITY_1"."FAX_NUMBER" , "ENTITY_1"."DTTM_RCD_ADDED" , "ENTITY_1"."DTTM_RCD_LAST_UPD" , "ENTITY_1"."UPDATED_BY_USER" , "ENTITY_1"."DELETED_FLAG" , "ENTITY_1"."SEC_DEPT_EID" , "ENTITY_1"."EFF_START_DATE" , "ENTITY_1"."EFF_END_DATE" , "ENTITY_1"."PARENT_1099_EID" , "ENTITY_1"."REPORT_1099_FLAG" , "ENTITY_1"."MIDDLE_NAME" , "ENTITY_1"."RM_USER_ID" , "ENTITY_1"."CONTACT"
FROM "DBO"."ENTITY" "ENTITY_1"
WHERE ( "ENTITY_1"."JOBID" = $gJobID ) and ( "ENTITY_1"."INVALID_ROW" <> 1) and ( "ENTITY_1"."UPDATE_ROW" = 1)
Mi issue is that the Ignore columns with NULL = Yes is not working, Im trying to avoid that source NULLs values replaces existing non-nulls values.
the fix done in 12.2.0.1 is not related to your case, in your case its doing row by row processing, something else is going wrong in your case
if you enable the trace for loader it will print the SQL that is used, do this will 1 or 2 rows from source and post the trace, I can check if its handling the ignore column nulls in that or not
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.