cancel
Showing results for 
Search instead for 
Did you mean: 

Need to ignoring NULLS when updating using Table Comparison

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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"

Former Member
0 Kudos

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!

Former Member
0 Kudos

thats what Ignore columns with NULLs option is for

what is the DI/DS version that you are using ?

is the column(s) for which the value is coming as NULL part of Key Column ?

what is the database type for target table ?

enable trace for loader and see what is the update statement this is used

Former Member
0 Kudos

Im using SAP BO 3.2 and my target table is on MS SQL Server 2005.

The column is no part of the Key column.

I´ll try make a trace in order to check the SQL instructions sent to the target DB.

Any comments is welcome!

Former Member
0 Kudos

in 12.2.0.0 version there was a bug in Auto Correct Load for SQL Server for some cases, from the Desginer check what is the Optimised SQL that is getting generated ?

Former Member
0 Kudos

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

Former Member
0 Kudos

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 ?

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Answers (0)