odbc insert

kishoreramachandran · Sun Dec 17, 2006 10:34 am

I have got an odbc table as input and I am doing an insert on this table through a sql and this sql is joining many tables then is it required to refer to those table columns somewhere in the input or just the sql will take care of it. basically say input is table emp and I am inserting values to this table so output is also same table emp.the sql is referring to many other tables.

chulett · Post by **chulett** » Sun Dec 17, 2006 11:23 am

I'm sorry, but you'll need to restate your question as it's not really obvious what you need help with. I can pull this piece out which seems to be the heart of the matter:

kishoreramachandran wrote:is it required to refer to those table columns somewhere in the input or just the sql will take care of it

However, still not really clear what your issue is or how 'sql is referring to many other tables' is relevant.

kishoreramachandran · Sun Dec 17, 2006 11:43 am

I have a odbc table and a sql that is going to insert records into that table .The sql is referrring to many other tables to populate this table and I need to check if some of them are null and replace with a different value. then should I have all those tables as input to my transformer.

DSguru2B · Post by **DSguru2B** » Sun Dec 17, 2006 1:11 pm

You can do that in the sql itself. I think using the Collasce function. NOt sure, google for it.

ray.wurlod · Post by **ray.wurlod** » Sun Dec 17, 2006 2:25 pm

Within a Transformer stage, use a NULL-handling expression in each case - maybe create a NullToValue Transform.

But, as Craig suggested, you can do this within the extraction SQL. Depending on the database the function may be called something like NVL(). COALESCE() selects the earliest non-null value from a list of values/column references (or returns null if they are all null).

chulett · Post by **chulett** » Sun Dec 17, 2006 7:56 pm

That was Bill, or Chuck or whatever 'nom de plume' DSGuru2B is operating under today.

As noted, you can do this in your SQL or in your job - your choice. Typical SQL solutions could include NVL or DECODE or COALESCE, for specific answers we would need to know your database.

In the job, IsNull could help or a NullToValue transform could be created, modeled after the existing NullToEmpty / NullToZero transforms.

It really depends on where these 'different values' come from.

DSguru2B · Post by **DSguru2B** » Sun Dec 17, 2006 9:40 pm

chulett wrote:That was Bill, or Chuck or whatever 'nom de plume' DSGuru2B is operating under today.

Still a Mystery

narasimha · Post by **narasimha** » Sun Dec 17, 2006 10:00 pm

Your sql query can refer or join any numnber of tables.
You don't need to have each of the tables you are refering to as an input to the transformer.
You coudld also use Oracle NVL function, which lets you substitute a value when a null value is encountered.
Check Google for the syntax for the Oracle NVL function.

chulett · Post by **chulett** » Sun Dec 17, 2006 11:10 pm

narasimha wrote:You coudld also use Oracle NVL function...

Really? Where did anybody (other than you, that is) mention the 'O' word?

narasimha · Post by **narasimha** » Sun Dec 17, 2006 11:36 pm

chulett wrote:
narasimha wrote:You coudld also use Oracle NVL function...
Really? Where did anybody (other than you, that is) mention the 'O' word?

O..ooo I should read the posts more in details. I am so obsessed with Oracle