DELETE Transformation: Different Data Type

[R_Chung]

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

[LinusH]

Obviously they aren't of the same data type. Be aware of that Metadata and physical data might not be on sync.

[ballardw]

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.

[R_Chung]

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

[Patrick]

@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.

[Patrick]

@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.

[VishnuPriyaP]

Late to the party - but here I am. I get the same error with the dame data type as well.

What does work for me is use an IN operator + a select query.

Somehow the = column or the subquery builder doesn't update the code with the correct column name in the condition. 

Less than ideal, but this works.

Has anyone figured this out already? 

[Patrick]

Please don't piggy-back on old questions but ask a new question. You can always reference other discussions. 

You need also to provide more information like the error message (SAS log) and the full portion of the code creating the error for us to give any advice. 

8 sub-selects in a where clause are certainly anything than ideal. If the lookup tables are voluminous then performance will be seriously bad especially when also using a distinct.

From the looks of it you should be able to define a simple inner join for what you need and given your column names I'd also assume that you don't need a distinct because the combination of the variables from the work table look like identifying a unique row ("unique key").

If you really have different data types in your table then fix this in the code that creates the Work table - but if they were different then also the sub-select lookup wouldn't work.