SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DELETE Transformation: Different Data Type

Reply
Contributor
Posts: 48

DELETE Transformation: Different Data Type

[ Edited ]

Hello, everyone. I am using SAS Data Integration Studio - delete transformation.

 

This is my work flow:

source table(Table A) --> extract(Temp Table B) --> delete --> destination table(Table C)

 

First, I extract a field from the source table and suppose it will only yield one result, say id = 2.

 

Second, I make a setting (where clause) in the delete transformation block, C.id = B.id (which is 2 from Table A)

 

However, it always yields a result that ERROR: Expression using equals (=) has components that are of different data types.

 

I have checked the setting of both id of both table, also the temp table on extract transformation, in SAS, they are the same.

Meanwhile,

I have checked the setting of both id of both table in Database, they are also the same.

 

Could anyone suggest the problem for me?

 

Thankyou

 

 

Super User
Posts: 5,430

Re: DELETE Transformation: Different Data Type

Obviously they aren't of the same data type. Be aware of that Metadata and physical data might not be on sync.
Data never sleeps
Super User
Posts: 11,343

Re: DELETE Transformation: Different Data Type

I don't use DI so this may seem odd as a question. It appears to me that you are creating table C so where do you tell Table C what type of variable ID in table C may be? Your description makes it sound to me that you may be using a variable, C.id before it exists.

 

If you ID variable in Table A and B are both character it may be that table C thinks the variable should be numeric if not explicitly stated to be character somewhere prior.

 

Also you may want to consider this:

C.id = B.id (which is 2 from Table A)

is somewhat confusing. IF 2 is from Table A why are you calling it B.id or C.id? Since we do not have your actual data sets this looks very odd.

Contributor
Posts: 48

Re: DELETE Transformation: Different Data Type

The table C is not created. It is a registered table.

Respected Advisor
Posts: 4,173

Re: DELETE Transformation: Different Data Type

@R_Chung

I assume you mean by "Registered Table" that the SAS Table Metadata object has been created based on an existing physical table. What that means: As long as you don't change the Metadata definition or recreate the underlying physical table with different attributes, SAS Metadata and Physical table attributes will be in sync.

 

If that's the case then ensure that your table used for deletion got the same attributes than your table C (both in metadata - and this can also be a green table - and in underlying physical table attributes.

Respected Advisor
Posts: 4,173

Re: DELETE Transformation: Different Data Type

[ Edited ]

@R_Chung

Make sure that the column metadata definitions of target table for node "extract(Temp Table B)" are exactly the same than what you have in "Table C".

 

Once Metadata is the same: 

If your node  "extract(Temp Table B)" doesn't always fully re-create the underlying physical SAS table (ie. a truncate and append) then make sure that you manually delete the physical SAS table (target table of node "extract(Temp Table B)") after you've changed the SAS metadata for this table.

 

SAS Metadata gets used for table creation. What can happen with processes that don't always re-create the physical table: You change a Metadata definition but as the underlying physical table doesn't get re-created, Metadata and physical data are no more in sync. On your DIS Metadata level everything looks o.k. but when you run the job then on the physical level column definitions might not yet be in sync and you get errors as the one you've just encountered. 

 

The safest way to avoid this: Delete the physical tables manually after changing column metadata so they get re-created based on the current SAS metadata definitions.

Ask a Question
Discussion stats
  • 5 replies
  • 204 views
  • 0 likes
  • 4 in conversation