Page 1 of 1
Column concatenation help
Posted: Wed Jan 11, 2006 10:33 am
by 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
Posted: Wed Jan 11, 2006 10:57 am
by 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
Posted: Wed Jan 11, 2006 11:00 am
by 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.
Posted: Wed Jan 11, 2006 11:14 am
by 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;
Posted: Wed Jan 11, 2006 1:58 pm
by 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.
Posted: Wed Jan 11, 2006 2:36 pm
by 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.
Posted: Wed Jan 11, 2006 4:27 pm
by jzparad
If you use Ken's suggestion you will end up with two columns - KEY and DATA. The DATA column will look something like
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
Now why you would put something like that in the database, I don't know!
Posted: Wed Jan 11, 2006 6:56 pm
by ray.wurlod
colorblue wrote: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.