Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage version is 4.1 and source database running on UNIDATA.
Thanks,
Celeste Lim
Selecting the most recent key
Nothing specific about datastage here...
Ive seen this problem many times, I can never figure out why a current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103. Anyway, at a fundemental level, you just need to create a definition that returns your "select true" condition where the
key=primary_key_component:(sub_component+1) doesnt return a record.
Kev.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Friday, 16 November 2001 12:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage version is 4.1 and source database running on UNIDATA.
Thanks,
Celeste Lim
Ive seen this problem many times, I can never figure out why a current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103. Anyway, at a fundemental level, you just need to create a definition that returns your "select true" condition where the
key=primary_key_component:(sub_component+1) doesnt return a record.
Kev.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Friday, 16 November 2001 12:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage version is 4.1 and source database running on UNIDATA.
Thanks,
Celeste Lim
Celeste
I have not used Unidata with DataStage but normally you would ORDER BY field DESC. If you turn off cache then you select ascending. This the last record over the previous. I assume you are writing with the same key. With the write cache on DataStage does not overwrite a previous written key.
To be safe you should split this into 2 fields.
id=Key1
f1=I
f2=field(@ID,"*",1)
f3=
f4=Key1
f5=20L
f6=S
id=Key2
f1=I
f2=field(@ID,"*",2)
f3=
f4=Key1
f5=4R
f6=S
Then select with Key2 descending. If you do not want to do a user defined query then in the selection field put: 1=1 ORDER BY Key2
Let me know if this works.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Thursday, November 15, 2001 7:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage version is 4.1 and source database running on UNIDATA.
Thanks,
Celeste Lim
I have not used Unidata with DataStage but normally you would ORDER BY field DESC. If you turn off cache then you select ascending. This the last record over the previous. I assume you are writing with the same key. With the write cache on DataStage does not overwrite a previous written key.
To be safe you should split this into 2 fields.
id=Key1
f1=I
f2=field(@ID,"*",1)
f3=
f4=Key1
f5=20L
f6=S
id=Key2
f1=I
f2=field(@ID,"*",2)
f3=
f4=Key1
f5=4R
f6=S
Then select with Key2 descending. If you do not want to do a user defined query then in the selection field put: 1=1 ORDER BY Key2
Let me know if this works.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Thursday, November 15, 2001 7:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage version is 4.1 and source database running on UNIDATA.
Thanks,
Celeste Lim
Hi Kev,
Thanks for your help. But I do not understand what are you saying here. I have written a routine and place it at Constraint at Transformer stage, but it didnt work. My main concern is to get the most recent key. This is the transaction data and Im really trying very hard to get this problem solve. Ok, im reading a UNIDATA file as the primary source, goes through a transformer, whereby I want to extract the most recent key and output to a Hashed file.
Hope this gives you a better picture.
Thanks,
Celeste
Kevin Punter wrote:
> Nothing specific about datastage here...
>
> Ive seen this problem many times, I can never figure out why a
> current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103.
> Anyway, at a fundemental level, you just need to create a definition
> that returns your "select true" condition where the
> key=primary_key_component:(sub_component+1) doesnt return a record.
>
> Kev.
>
> -----Original Message-----
> From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> Sent: Friday, 16 November 2001 12:40 PM
> To: datastage-users@oliver.com
> Subject: Selecting the most recent key
>
> Hi there!
>
> Can anyone help me with this problem?
>
> Pls refer illustration below.
>
> KEY GROSS.AMT
> === =========
> A-0-00-H-000082*101 100.00
> A-0-00-H-000082*103 500.00
> A-0-00-H-000082*102 300.00
>
> I want to retrieve the most up-to-date KEY, which is
> A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> version is 4.1 and source database running on UNIDATA.
>
> Thanks,
> Celeste Lim
Thanks for your help. But I do not understand what are you saying here. I have written a routine and place it at Constraint at Transformer stage, but it didnt work. My main concern is to get the most recent key. This is the transaction data and Im really trying very hard to get this problem solve. Ok, im reading a UNIDATA file as the primary source, goes through a transformer, whereby I want to extract the most recent key and output to a Hashed file.
Hope this gives you a better picture.
Thanks,
Celeste
Kevin Punter wrote:
> Nothing specific about datastage here...
>
> Ive seen this problem many times, I can never figure out why a
> current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103.
> Anyway, at a fundemental level, you just need to create a definition
> that returns your "select true" condition where the
> key=primary_key_component:(sub_component+1) doesnt return a record.
>
> Kev.
>
> -----Original Message-----
> From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> Sent: Friday, 16 November 2001 12:40 PM
> To: datastage-users@oliver.com
> Subject: Selecting the most recent key
>
> Hi there!
>
> Can anyone help me with this problem?
>
> Pls refer illustration below.
>
> KEY GROSS.AMT
> === =========
> A-0-00-H-000082*101 100.00
> A-0-00-H-000082*103 500.00
> A-0-00-H-000082*102 300.00
>
> I want to retrieve the most up-to-date KEY, which is
> A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> version is 4.1 and source database running on UNIDATA.
>
> Thanks,
> Celeste Lim
Hi Celeste,
I have a suggestion, hopefully I interpret your requirements
accurately. However there are some turnarounds for you to get the data.
Step 1
=======
DataStage read from the Unidata file, and goes through a transformer,
and output to a flat file. (I suppose your key number is increasing
sequentially)
In the transformer try to split the Key field into two field which you
can use the Field function - (look at the help file for the Field
function). i.e Field(KeyColumn, "*", 1) and Field(KeyColumn, "*", 2)
eg) A-0-00-H-000082*103
you can split to two fields which become the following format in
the flat file
Field1 Field2
====== ======
A-0-00-H-000082 103
: :
: :
Step 2
=======
Then use the flat file to link into an aggregator stage, and from the
aggregator you can use the Max function of Field2 to get the most
recent key. When you use the Max function, you have to group by Field1.
Step 3
=======
Then concatenate these two fields and reference back to the Unidata
file to get the Gross Amount out.
The job design suppose to look like this:
Unidata file --> Transformer --> Flat File
|
V
Aggregator
|
V
Flat File
|
V
Transformer (This is where you do
your field concatenation)
|
V
Transformer Hi Kev,
>
> Thanks for your help. But I do not understand what are you saying
> here. I
> have written a routine and place it at Constraint at Transformer
> stage, but
> it didnt work. My main concern is to get the most recent key.
> This is the
> transaction data and Im really trying very hard to get this
> problem solve.
> Ok, im reading a UNIDATA file as the primary source, goes through a
> transformer, whereby I want to extract the most recent key and
> output to a
> Hashed file.
>
> Hope this gives you a better picture.
> Thanks,
> Celeste
>
> Kevin Punter wrote:
>
> > Nothing specific about datastage here...
> >
> > Ive seen this problem many times, I can never figure out why a
> current> items table doesnt exist, ie; Key=A-0-00-H-
> 000082,Data=103. Anyway, at a
> > fundemental level, you just need to create a definition that
> returns your
> > "select true" condition where the
> > key=primary_key_component:(sub_component+1) doesnt return a record.
> >
> > Kev.
> >
> > -----Original Message-----
> > From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> > Sent: Friday, 16 November 2001 12:40 PM
> > To: datastage-users@oliver.com
> > Subject: Selecting the most recent key
> >
> > Hi there!
> >
> > Can anyone help me with this problem?
> >
> > Pls refer illustration below.
> >
> > KEY GROSS.AMT
> > === =========
> > A-0-00-H-000082*101 100.00
> > A-0-00-H-000082*103 500.00
> > A-0-00-H-000082*102 300.00
> >
> > I want to retrieve the most up-to-date KEY, which is
> > A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> > version is 4.1 and source database running on UNIDATA.
> >
> > Thanks,
> > Celeste Lim
>
>
>
>
I have a suggestion, hopefully I interpret your requirements
accurately. However there are some turnarounds for you to get the data.
Step 1
=======
DataStage read from the Unidata file, and goes through a transformer,
and output to a flat file. (I suppose your key number is increasing
sequentially)
In the transformer try to split the Key field into two field which you
can use the Field function - (look at the help file for the Field
function). i.e Field(KeyColumn, "*", 1) and Field(KeyColumn, "*", 2)
eg) A-0-00-H-000082*103
you can split to two fields which become the following format in
the flat file
Field1 Field2
====== ======
A-0-00-H-000082 103
: :
: :
Step 2
=======
Then use the flat file to link into an aggregator stage, and from the
aggregator you can use the Max function of Field2 to get the most
recent key. When you use the Max function, you have to group by Field1.
Step 3
=======
Then concatenate these two fields and reference back to the Unidata
file to get the Gross Amount out.
The job design suppose to look like this:
Unidata file --> Transformer --> Flat File
|
V
Aggregator
|
V
Flat File
|
V
Transformer (This is where you do
your field concatenation)
|
V
Transformer Hi Kev,
>
> Thanks for your help. But I do not understand what are you saying
> here. I
> have written a routine and place it at Constraint at Transformer
> stage, but
> it didnt work. My main concern is to get the most recent key.
> This is the
> transaction data and Im really trying very hard to get this
> problem solve.
> Ok, im reading a UNIDATA file as the primary source, goes through a
> transformer, whereby I want to extract the most recent key and
> output to a
> Hashed file.
>
> Hope this gives you a better picture.
> Thanks,
> Celeste
>
> Kevin Punter wrote:
>
> > Nothing specific about datastage here...
> >
> > Ive seen this problem many times, I can never figure out why a
> current> items table doesnt exist, ie; Key=A-0-00-H-
> 000082,Data=103. Anyway, at a
> > fundemental level, you just need to create a definition that
> returns your
> > "select true" condition where the
> > key=primary_key_component:(sub_component+1) doesnt return a record.
> >
> > Kev.
> >
> > -----Original Message-----
> > From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> > Sent: Friday, 16 November 2001 12:40 PM
> > To: datastage-users@oliver.com
> > Subject: Selecting the most recent key
> >
> > Hi there!
> >
> > Can anyone help me with this problem?
> >
> > Pls refer illustration below.
> >
> > KEY GROSS.AMT
> > === =========
> > A-0-00-H-000082*101 100.00
> > A-0-00-H-000082*103 500.00
> > A-0-00-H-000082*102 300.00
> >
> > I want to retrieve the most up-to-date KEY, which is
> > A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> > version is 4.1 and source database running on UNIDATA.
> >
> > Thanks,
> > Celeste Lim
>
>
>
>
Celeste,
In the situation you describe, I would do the following...
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
In the Unidata stage, order the data by the Key so that it will be reordered as such.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*102 300.00
A-0-00-H-000082*103 500.00
In the hash stage, define three fields KeyPart1, KeyPart2 and Data (and only define KeyPart1 as a Key field). The transforms for each will be as follows:
KeyPart1 = InputLink.Key[1,16]
KeyPart2 = InputLink.Key[17,3]
Data = InputLink.Data
This will send 3 rows to the hash file, but since all have the same key (A-0-00-H-000082*) each row will overlay the previous and only one row will exist at the end (the last row you write).
"Celeste Lim (COS-HO-IT)" wrote:
Hi Kev,
Thanks for your help. But I do not understand what are you saying here. I have written a routine and place it at Constraint at Transformer stage, but it didnt work. My main concern is to get the most recent key. This is the transaction data and Im really trying very hard to get this problem solve. Ok, im reading a UNIDATA file as the primary source, goes through a transformer, whereby I want to extract the most recent key and output to a Hashed file.
Hope this gives you a better picture.
Thanks,
Celeste
Kevin Punter wrote:
> Nothing specific about datastage here...
>
> Ive seen this problem many times, I can never figure out why a
> current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103.
> Anyway, at a fundemental level, you just need to create a definition
> that returns your "select true" condition where the
> key=primary_key_component:(sub_component+1) doesnt return a record.
>
> Kev.
>
> -----Original Message-----
> From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> Sent: Friday, 16 November 2001 12:40 PM
> To: datastage-users@oliver.com
> Subject: Selecting the most recent key
>
> Hi there!
>
> Can anyone help me with this problem?
>
> Pls refer illustration below.
>
> KEY GROSS.AMT
> === =========
> A-0-00-H-000082*101 100.00
> A-0-00-H-000082*103 500.00
> A-0-00-H-000082*102 300.00
>
> I want to retrieve the most up-to-date KEY, which is
> A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> version is 4.1 and source database running on UNIDATA.
>
> Thanks,
> Celeste Lim
---------------------------------
Do You Yahoo!?
Find the one for you at Yahoo! Personals.
In the situation you describe, I would do the following...
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
In the Unidata stage, order the data by the Key so that it will be reordered as such.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*102 300.00
A-0-00-H-000082*103 500.00
In the hash stage, define three fields KeyPart1, KeyPart2 and Data (and only define KeyPart1 as a Key field). The transforms for each will be as follows:
KeyPart1 = InputLink.Key[1,16]
KeyPart2 = InputLink.Key[17,3]
Data = InputLink.Data
This will send 3 rows to the hash file, but since all have the same key (A-0-00-H-000082*) each row will overlay the previous and only one row will exist at the end (the last row you write).
"Celeste Lim (COS-HO-IT)" wrote:
Hi Kev,
Thanks for your help. But I do not understand what are you saying here. I have written a routine and place it at Constraint at Transformer stage, but it didnt work. My main concern is to get the most recent key. This is the transaction data and Im really trying very hard to get this problem solve. Ok, im reading a UNIDATA file as the primary source, goes through a transformer, whereby I want to extract the most recent key and output to a Hashed file.
Hope this gives you a better picture.
Thanks,
Celeste
Kevin Punter wrote:
> Nothing specific about datastage here...
>
> Ive seen this problem many times, I can never figure out why a
> current items table doesnt exist, ie; Key=A-0-00-H-000082,Data=103.
> Anyway, at a fundemental level, you just need to create a definition
> that returns your "select true" condition where the
> key=primary_key_component:(sub_component+1) doesnt return a record.
>
> Kev.
>
> -----Original Message-----
> From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
> Sent: Friday, 16 November 2001 12:40 PM
> To: datastage-users@oliver.com
> Subject: Selecting the most recent key
>
> Hi there!
>
> Can anyone help me with this problem?
>
> Pls refer illustration below.
>
> KEY GROSS.AMT
> === =========
> A-0-00-H-000082*101 100.00
> A-0-00-H-000082*103 500.00
> A-0-00-H-000082*102 300.00
>
> I want to retrieve the most up-to-date KEY, which is
> A-0-00-H-000082*103. So, how do i do that in DataStage? My DataStage
> version is 4.1 and source database running on UNIDATA.
>
> Thanks,
> Celeste Lim
---------------------------------
Do You Yahoo!?
Find the one for you at Yahoo! Personals.
Celeste
When you put something in the selection field then look at the generated query. It will add a WHERE before the selection clause. You can trick it by adding a 1=1 and then your ORDER BY clause. The ORDER BY Key2 needs a DESC to order in descending order. The generated query will look like this.
SELECT field1, field2 ... FROM table WHERE 1=1 ORDER BY Key2 DESC;
This will work in most databases. What most of the solutions presented here are trying to do is split that field into 2 fields then you can process it easier. Most likely you do not need to split it. It should sort okay with it as just one field. The trick is to sort the data as it comes in. If you sort the field ascending then the last record is the one you want. If you sort descending the first record is the only one you want to write. Try it both ways and see which works. Add the ORDER BY and let us know what happened.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Friday, November 16, 2001 1:36 AM
To: kduke@premierhome.net
Subject: Re: Selecting the most recent key
Dear Kim,
Thanks for your advise. But I dont understand what are you trying to say. Esp this,
Then select with Key2 descending. If you do not want to do a user defined
query then in the selection field put:
1=1 ORDER BY Key2
In UNIDATA, how do I select Key2 descending and 1=1 ORDER BY Key2 is blurr for me. Pls advise.
TQ
celeste
Kim Duke wrote:
Celeste
I have not used Unidata with DataStage but normally you would ORDER BY field
DESC. If you turn off cache then you select ascending. This the last record
over the previous. I assume you are writing with the same key. With the
write cache on DataStage does not overwrite a previous written key.
To be safe you should split this into 2 fields.
id=Key1
f1=I
f2=field(@ID,"*",1)
f3=
f4=Key1
f5=20L
f6=S
id=Key2
f1=I
f2=field(@ID,"*",2)
f3=
f4=Key1
f5=4R
f6=S
Then select with Key2 descending. If you do not want to do a user defined
query then in the selection field put:
1=1 ORDER BY Key2
Let me know if this works.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Thursday, November 15, 2001 7:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is
A-0-00-H-000082*103.
So, how do i do that in DataStage? My DataStage version is 4.1 and
source database running on UNIDATA.
Thanks,
Celeste Lim
When you put something in the selection field then look at the generated query. It will add a WHERE before the selection clause. You can trick it by adding a 1=1 and then your ORDER BY clause. The ORDER BY Key2 needs a DESC to order in descending order. The generated query will look like this.
SELECT field1, field2 ... FROM table WHERE 1=1 ORDER BY Key2 DESC;
This will work in most databases. What most of the solutions presented here are trying to do is split that field into 2 fields then you can process it easier. Most likely you do not need to split it. It should sort okay with it as just one field. The trick is to sort the data as it comes in. If you sort the field ascending then the last record is the one you want. If you sort descending the first record is the only one you want to write. Try it both ways and see which works. Add the ORDER BY and let us know what happened.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Friday, November 16, 2001 1:36 AM
To: kduke@premierhome.net
Subject: Re: Selecting the most recent key
Dear Kim,
Thanks for your advise. But I dont understand what are you trying to say. Esp this,
Then select with Key2 descending. If you do not want to do a user defined
query then in the selection field put:
1=1 ORDER BY Key2
In UNIDATA, how do I select Key2 descending and 1=1 ORDER BY Key2 is blurr for me. Pls advise.
TQ
celeste
Kim Duke wrote:
Celeste
I have not used Unidata with DataStage but normally you would ORDER BY field
DESC. If you turn off cache then you select ascending. This the last record
over the previous. I assume you are writing with the same key. With the
write cache on DataStage does not overwrite a previous written key.
To be safe you should split this into 2 fields.
id=Key1
f1=I
f2=field(@ID,"*",1)
f3=
f4=Key1
f5=20L
f6=S
id=Key2
f1=I
f2=field(@ID,"*",2)
f3=
f4=Key1
f5=4R
f6=S
Then select with Key2 descending. If you do not want to do a user defined
query then in the selection field put:
1=1 ORDER BY Key2
Let me know if this works.
Thanks Kim.
-----Original Message-----
From: Celeste Lim (COS-HO-IT) [mailto:celeste@kurnia.com.my]
Sent: Thursday, November 15, 2001 7:40 PM
To: datastage-users@oliver.com
Subject: Selecting the most recent key
Hi there!
Can anyone help me with this problem?
Pls refer illustration below.
KEY GROSS.AMT
=== =========
A-0-00-H-000082*101 100.00
A-0-00-H-000082*103 500.00
A-0-00-H-000082*102 300.00
I want to retrieve the most up-to-date KEY, which is
A-0-00-H-000082*103.
So, how do i do that in DataStage? My DataStage version is 4.1 and
source database running on UNIDATA.
Thanks,
Celeste Lim