Column concatenation help

[colorblue]

DS Gurus,

I have an oracle source table

id name1 name2 name3 name4....nameN code
100 sam rob dan NULL ven xyz
101 lob NULL jen lar del ABC

I want the output in the Oracle target table to be

id names code
100 sam,rob,dan,unknown,...,ven XYZ
101 lob,unknown,jen,lar,...,del ABC

Basically, i need to check the column values (There may be n number of columns) and it is null, I need to return like 'unknown' else returns the column value only.

Please let me know, how can i do this???

Thx

[jstage]

Mr Blue,

In the derivation of each column you have to specify a condiition something like
If IsNull(DSLink.InputColumn) Then 'unknown' Else DSLink.InputColumn
You have to specify the condition for all the nullable columns.

-J

[edward_m]

Check nullability in your mapping.
code:
if IsNull(source.name1) then 'unknown' Else souce.name1--name1
if IsNull(source.name2) then 'unknown' Else source.name2--name2
.
.
.
if IsNull(source.nameN) then 'unknown' Else source.nameN--nameN

Thanks.

[jzparad]

Or you could do it with a user-defined query on the OCI stage

Code: Select all

select id, nvl(name1,'unknown'), nvl(name2,'unknown'), ...
from tableX;

[colorblue]

Thanks for all you reply.
The real issue is, i have more than 100 columns and need to process around 10 million records and the problem here is i need to check nvl condition for all the columns. one more issue is except key columns i need to concatenate all the other columns into one single column at the output table. Any more suggestions.

[kcbland]

How about reading this topic

viewtopic.php?p=159175&sid=93faa1a04763 ... a65#159175

which is actively being discussed right now.

In your transformer, you could mass-apply a function to existing derivations. Check it out in the Transformer stage help. Write a NVL type function in DS Manager that can be used to replace NULLs with a passed value.

Not to pick a nit, but there are advantages to reading other posts to gain insight, maybe during a coffee break or something. Something learned today could be applied months from now.

[jzparad]

If you use Ken's suggestion you will end up with two columns - KEY and DATA. The DATA column will look something like

Code: Select all

,field1,field2,,

You could then apply a function to the DATA column like this

Code: Select all

tmp = Arg1

Convert "," to @FM in tmp

Ans = ""

For i = 1 to DCount(tmp,@FM)
   If tmp<i> = "" Then
      Ans := "unknown"
   End Else
      Ans := tmp<i>
   End
Next i

which would give you a result like this

Code: Select all

unknownfield1field2unknownunknown

Now why you would put something like that in the database, I don't know!

[ray.wurlod]

Thanks for all you reply.
The real issue is, i have more than 100 columns and need to process around 10 million records and the problem here is i need to check nvl condition for all the columns. one more issue is except key columns i need to concatenate all the other columns into one single column at the output table. Any more suggestions.

Yes, you DO need to check somehow, somewhere. It's unavoidable unless you can absolutely guarantee that a particular column can never have a null value - so you can skip that column from your checks.

Use stage variables extensively; this will aid the null checks (and allow for exception reporting if required) and will aid the construction of your single everything_except_the_key_is_in_here column, perhaps a few columns at a time to facilitate future maintenance, and definitely to avoid issues with the maximum length of an expression.