DSXchange

I am working on PX and facing a problem with the use of stage variables.

In my job i am having 3 flows: One for Insert,One for Update and one for Delete.In case of insertion and updation i am using some stage variables to populate the target columns.These stage variables are common for both insertion and updation operations.In case of insertion its working fine but in case of Updation i am getting problem.The problem is as follows:

Target table has 4 columns:

Col1(Integer,PK) COl2(varchar) Col3(timestamp) Col4(Decimal)

In the first run i am inserting the followong data into the table:

Col1 COl2 Col3 Col4
1 A 10-Oct-2006 1.0
2 B 11-Oct-2006 2.0
3 C 11-Oct-2006 3.0
4 D 11-Oct-2006 4.0

In the second run i am trying to update these records with the following values:

Record1 COl2 update from A to Z
Record2 COl2 Update from B to Null,Col3 update from 11-Oct-2006 to Null
Record2 Col4 update from 3 to NUll

So my target table should look like:

Col1 COl2 Col3 Col4
1 Z 10-Oct-2006 1.0
2 NULL NULL 2.0
3 C 11-Oct-2006 NULL

But my actual view of the table is

Col1 COl2 Col3 Col4
1 Z 10-Oct-2006 1.0
2 Z 10-Oct-2006 2.0
3 C 11-Oct-2006 2.0
4 D 11-Oct-2006 4.0

So you can say where ever i am putting a NULL in the target column it is getting populated with the previous records value which were not NULL.

There is no such problem if i am not using any stage variables and calculating the columns in their derivations only.

Can any one please guide me on this?

Thanks alot in advance.

What datatype and attributes have you specified in your stage variables? Exactly what derivation do you do in the stage variables?

Try runing the transformer in sequential mode.I hade a similar scenario.As far as my knowledge Upsert mode execution needs to be sequential if you have more then two nodes.

But is the update queru is just accessing or hitting only one row, will the order of execution of update cluases make any difference?

Deep are you updating a single row, or is the update SQL affect more than one rows?

Hi,

These are the details of the stage variables which i am using.I have also given the details about the datatype of the columns which i am trying to populate with the help of these stage variables.I have tried running the transformer stage in Sequential mode but its not helping either.

Variable Name: svname

Derivation:
If IsNull(NAME) Or TrimLeadingTrailing(NAME) ='' THEN SetNull() Else NAME
Datatype:Char
Used to populate column with datatype: Char

Variable Name:svlocation
If IsNull(LOCATION) Or TrimLeadingTrailing(LOCATION) ='' THEN SetNull() Else LOCATION
Datatype:Char
Used to populate column with datatype: Char


Variable Name:svFilingDate
If IsNull(FILING_DATE) OR Trim(FILING_DATE)=''Then SetNull() Else TimestampFromDateTime(StringToDate(FILING_DATE),"00:00:00")
Datatype:Timestamp
Used to populate column with datatype: Timestamp

Variable Name:svFormType
If IsNull(FORM_TYPE) Or TrimLeadingTrailing(FORM_TYPE) ='' THEN SetNull() Else FORM_TYPE
Datatype:Char
Used to populate column with datatype: Char


Variable Name:svHeldShr
If IsNull(HELD_SHR) Or TrimLeadingTrailing(HELD_SHR) ='' THEN SetNull() Else StringToDecimal(HELD_SHR)
Datatype:Decimal

Used to populate column with datatype: Decimal.


Thanks in advance,

don't know if it's relevant for the version of DS your using , but at one point I saw a patch about NULL values in stage variables.(some time ago, don't exactly remember the details or which version we were on at that moment)

Maybe best check if this bug is there in your version .

The order of execution of the output links from the Transformer is:
Update Link
Delete Link
Insert Link

Hi Deep,

As I remember, stage variables required not null values. Becuase, there is no concept of of Null/Not null for Stage variables. I am not sure whether stage variable can hold nulls or not ? Just try out u r setting of Null at the derivation level of Column. May be it will help you.

Regards
Nagesh

Nageshsunkoji wrote:As I remember, stage variables required not null values. Becuase, there is no concept of of Null/Not null for Stage variables. I am not sure whether stage variable can hold nulls or not ?

Of course they can.

Not sure this has anything to do with your stage variables, per se, could be the actual derivation of the field, the constraints involved, or the way you've setup the target DB stage...

I could see that, the stage variables are used for Null handling. Where are you updating the other values?
Are you sure, that the value are not inserted after updated in the same run?