DSXchange

Hi,

I have to load data from a single table into multiple table based on a column value from the source table.

Eg,
SOURCE TABLE

code value date
------------------------
XXX 123 4/4/2006
YYY 333 8/7/2006
ZZZ 444 24/12/2006

and the code XXX,YYY,ZZZ indicates to which table the date has to go.
I m using stages as follows

ODBC-->TRANSFORMER->ODBC's (mutiple table)

and my transformer constraint is
If Input.CODE='XXX' Then Input.VALUE Else @FALSE

But I get a error like this

call to SQLExecute failed.
SQL statement:INSERT INTO REF_DB_XXX(VALUE, DATE ) VALUES (?,to_date(?,'dd/mm/yyyy'))
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client]Illegal date/time value

VALUE = "0"
DATE = "0"

I dont know where is this "0" coming from, Coz i dont have any data like this.
And so I m not able to run the jobs furthur.

Can anyone help me.

Thank u

Hello Quest,

in your last post you were leaving the forum in a huff because some of the detailed responses were from premium posters and weren't visible to you. Now, just a couple of hours later, you post another thread with the same question.

First off, a constraint evaluates to either TRUE or FALSE; thus your constraint expression might not always do what you expect, you should be using either @TRUE or some other constant value in that expression.

Does to_date('0','dd/mm/yyy') work in your SQL?

The zeroes are coming from your job design. 0 can arise from a logical expression; it is used to represent False in DataStage. So if your derivation expressions have been constructed as assignment statements, which is not correct, then you would usually expect to see zeroes.

I heartily endorse the earlier recommendation that you enrol in more formal training for DataStage.

quest_ds wrote:and my transformer constraint is
If Input.CODE='XXX' Then Input.VALUE Else @FALSE

This can't be true. Meaning, this can't be in your 'transformer constraint' but rather must be in the derivation expression itself. That's why you are getting zeroes in your field - @FALSE is a System Variable (look them up in the online help) and equates to '0'.

In the Transformer Editor, for each block of fields associated with a given link, the 'header' of the block will show the word 'Constraint' and a place for a single expression. Double-click there to supply the expression we've all been talking about.

And what your constraint should say is - as noted - an expression that evaluates to simply true or false, nothing else. So your link that populates table XXX could say:

Input.CODE = 'XXX'

Which is either true or false. When true, the record for XXX will go down the link for XXX, when false it won't. You should have a corresponding link for YYY with a corresponding constraint expression, such that any given record goes down one or the other. Expand that to as many links as are appropriate.

You can stick with one ODBC stage, all that's needed are multiple links to it.

And if your company has thrown you in the deep end of the pool, get swimming lessons before you drown. Insist on getting sent to some sort of class. Worst case, get your hands on the DataStage Tutorial cdrom and work through them - they will help you tremendously.

<shudder> I just realized the condition posted was a column derivation, not an actual transform constraint. Craig, you caught that one. I thought the date value of '0' was coming from somewhere else, not this line of code...

ray.wurlod wrote:...if your derivation expressions have been constructed as assignment statements, which is not correct, then you would usually expect to see zeroes...

I spotted it even earlier!
FIGJAM

Well, I think we all noticed about the same thing. I just translated it from High Wurlod into something mere mortals speak.

Thank you. My problems solved

<color=blood>Customary practice here is that you make a final post letting future searchers know what the answer is, and mark the thread as Resolved.

Only the original poster can mark the thread as resolved.</color>