Checking for nulls

phyllis · Post by **phyllis** » Mon Apr 05, 2004 11:34 am

I'm using an Oracle database 'view' as my source and the zipcode column is defined as varchar(35) in the view. Some zipcodes are 10 characters (xxxx-xxxx) but I only want the first 5 characters. Null zipcodes are not allowed (the target stage specifies the column as Null=N, as does the table I'm writing to) so I've been asked to change any null zipcode to '00000'

In DS, my source stage has the column defined as varchar(35) and in the target stage it's defined as varchar(10), then I have a derivation in the transform of

If ISNull (BillingAddressesIn.BILLING_ZIP_CODE) then BillingAddressesIn.BILLING_ZIP_CODE = '00000' else Left(BillingAddressesIn.BILLING_ZIP_CODE,5)

I'm running this using an ODBC stage - I imported the ODBC table definition and the column is specified as varchar (10) (someone else set this up before I came on board). I'm getting the following error:

Cannot insert the value NULL into column 'billingzip', column does not allow nulls. INSERT fails. billingzip = NULL

Is the problem that I have an incorrect derivation - my assumption is that I don't need to change the target column to allow null - or the column length in the target?

kcbland · Post by **kcbland** » Mon Apr 05, 2004 11:41 am

Code: Select all

If ISNull (BillingAddressesIn.BILLING_ZIP_CODE) then BillingAddressesIn.BILLING_ZIP_CODE = '00000' else Left(BillingAddressesIn.BILLING_ZIP_CODE,5)

Should be

Code: Select all

If ISNull (BillingAddressesIn.BILLING_ZIP_CODE) then '00000' else Left(BillingAddressesIn.BILLING_ZIP_CODE,5)

phyllis · Post by **phyllis** » Mon Apr 05, 2004 1:31 pm

Thanks - I changed the derivation and am running the job now - don't see any errors being logged - THANKS AGAIN!

ray.wurlod · Post by **ray.wurlod** » Mon Apr 05, 2004 4:58 pm

Expressions in DataStage are exactly that: expressions. Expressions generate values.

They are not assignment statements, and therefore should not contain assignment operators.