Problem while using stage variables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
Problem while using stage variables
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.
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
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,
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,
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
-
- Participant
- Posts: 222
- Joined: Tue Aug 30, 2005 2:07 am
- Location: pune
- Contact:
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
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
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
Of course they can.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 ?
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...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers