How to convert an integer date to a real date using OS/390 D
How to convert an integer date to a real date using OS/390 D
This is a topic for an orphaned message.
The solution Ray gave you only works for the server component not on the mainframe.
I do not think Ray read your heading 8^)
Phil
>
> From: Paul Ko
> Date: Mon, 1 Oct 2001 10:06:30 +1200
> To: "datastage-users@oliver.com"
> CC: "ray.wurlod@Informix.Com"
> Subject: RE: How to convert an integer date to a real date using OS/390 Da
> taStage
>
> Hello Ray,
>
> It does not work that way in OS/390. I believe it works in the server
> component.
>
> I tried both of your options but both of them returned me with 100008
> error code - An expression is required for the statement to be valid
> or complete.
>
> My syntax is:
> Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]")
>
> Cheers,
> Paul.
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Monday, 01 October, 2001 9:46 a.m.
> To: datastage-users@oliver.com
> Cc: pko@clear.co.nz
> Subject: RE: How to convert an integer date to a real date using
> OS/390 DataStage
>
>
> There was an error in previous "solution". Shouldve read it before
> clicking Send! :-~
>
> You need to convert the "integer form" into a DataStage internal form
> before converting it into an external form.
> Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
>
> You can also use substrings and concatenation. Syntax is:
> int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Monday, 01 October 2001 07:41
> To: datastage-users@oliver.com
> Cc: pko@clear.co.nz
> Subject: RE: How to convert an integer date to a real date using
> OS/390 DataStage
>
>
> All you need is Oconv(int_field,"DYMD") in a Transformer stage.
>
> There are no data types within DataStage; the recorded data types are
> user in interacting with databases. The Oconv() function has specific
> rules for wholly numeric date forms, of which the above solution makes
> use. Eight and six digits are regarded as "regular" dates (so you
> need the YMD to assert that your current locale order (DMY in NZ) is
> not to be followed) with four and two digits in the year respectively.
> Seven and five digits are interpreted as the ordinal day number within
> the year and the year number.
>
> The cast function is not known to DataStage BASIC expressions. You
> could use it in the selecting SQL (but then the "cat" operator will
> need to be the "||" operator (unless "cat" is known to the SQL of DB2,
> which is outside my knowledge). But this would be in the user-defined
> SQL field, not in a column derivation subsequent to extraction.
>
> (I assume "from 2 for 4" is a typo!)
>
> -----Original Message-----
> From: Paul Ko [mailto:PKO@clear.co.nz]
> Sent: Monday, 01 October 2001 07:25
> To: datastage-users@oliver.com
> Subject: How to convert an integer date to a real date using OS/390
> DataStage
>
>
> > Hello,
> >
> > There is a field in a DB2 table that is defined as integer. The
> > field is currently storing a valid date. For example, 20010909.
> >
> > I am trying to use DataStage to convert this to a real date format.
> > How can it be done using the OS/390 DataStage component.
> >
> > My idea is to use substring and cast function to cut and merge the
> > integer field into a date field. However, DataStage does not accept
> > my syntax.
> >
> > My syntax is:
> > substring(cast(int_field as char) from 2 for 4) cat "-" cat
> > substring(cast(int_field as char) from 6 for 2) cat "-" cat
> > substring(cast(int_field as char) from 8 for 2)
> >
> > Please advise on solution.
> >
> > Cheers,
> > Paul.
> >
>
>
I do not think Ray read your heading 8^)
Phil
>
> From: Paul Ko
> Date: Mon, 1 Oct 2001 10:06:30 +1200
> To: "datastage-users@oliver.com"
> CC: "ray.wurlod@Informix.Com"
> Subject: RE: How to convert an integer date to a real date using OS/390 Da
> taStage
>
> Hello Ray,
>
> It does not work that way in OS/390. I believe it works in the server
> component.
>
> I tried both of your options but both of them returned me with 100008
> error code - An expression is required for the statement to be valid
> or complete.
>
> My syntax is:
> Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]")
>
> Cheers,
> Paul.
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Monday, 01 October, 2001 9:46 a.m.
> To: datastage-users@oliver.com
> Cc: pko@clear.co.nz
> Subject: RE: How to convert an integer date to a real date using
> OS/390 DataStage
>
>
> There was an error in previous "solution". Shouldve read it before
> clicking Send! :-~
>
> You need to convert the "integer form" into a DataStage internal form
> before converting it into an external form.
> Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
>
> You can also use substrings and concatenation. Syntax is:
> int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Monday, 01 October 2001 07:41
> To: datastage-users@oliver.com
> Cc: pko@clear.co.nz
> Subject: RE: How to convert an integer date to a real date using
> OS/390 DataStage
>
>
> All you need is Oconv(int_field,"DYMD") in a Transformer stage.
>
> There are no data types within DataStage; the recorded data types are
> user in interacting with databases. The Oconv() function has specific
> rules for wholly numeric date forms, of which the above solution makes
> use. Eight and six digits are regarded as "regular" dates (so you
> need the YMD to assert that your current locale order (DMY in NZ) is
> not to be followed) with four and two digits in the year respectively.
> Seven and five digits are interpreted as the ordinal day number within
> the year and the year number.
>
> The cast function is not known to DataStage BASIC expressions. You
> could use it in the selecting SQL (but then the "cat" operator will
> need to be the "||" operator (unless "cat" is known to the SQL of DB2,
> which is outside my knowledge). But this would be in the user-defined
> SQL field, not in a column derivation subsequent to extraction.
>
> (I assume "from 2 for 4" is a typo!)
>
> -----Original Message-----
> From: Paul Ko [mailto:PKO@clear.co.nz]
> Sent: Monday, 01 October 2001 07:25
> To: datastage-users@oliver.com
> Subject: How to convert an integer date to a real date using OS/390
> DataStage
>
>
> > Hello,
> >
> > There is a field in a DB2 table that is defined as integer. The
> > field is currently storing a valid date. For example, 20010909.
> >
> > I am trying to use DataStage to convert this to a real date format.
> > How can it be done using the OS/390 DataStage component.
> >
> > My idea is to use substring and cast function to cut and merge the
> > integer field into a date field. However, DataStage does not accept
> > my syntax.
> >
> > My syntax is:
> > substring(cast(int_field as char) from 2 for 4) cat "-" cat
> > substring(cast(int_field as char) from 6 for 2) cat "-" cat
> > substring(cast(int_field as char) from 8 for 2)
> >
> > Please advise on solution.
> >
> > Cheers,
> > Paul.
> >
>
>
Hi Phil,
on Datastage OS/390 you cant use Datastage Server Function/Routine.
Phil try to "move" the input field to a stage variable defined as string; then "manipulate" this stage variable as you like better.
I think it can help you.
Regards,
Riccardo
----- Original Message -----
From: "Phil Walker"
To:
Sent: Monday, October 01, 2001 3:02 AM
Subject: Re: RE: How to convert an integer date to a real date using OS/390 Da taStage
> The solution Ray gave you only works for the server component not on
> the
mainframe.
>
> I do not think Ray read your heading 8^)
>
> Phil
> >
> > From: Paul Ko
> > Date: Mon, 1 Oct 2001 10:06:30 +1200
> > To: "datastage-users@oliver.com"
> > CC: "ray.wurlod@Informix.Com"
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390
Da
> > taStage
> >
> > Hello Ray,
> >
> > It does not work that way in OS/390. I believe it works in the
> > server component.
> >
> > I tried both of your options but both of them returned me with
> > 100008
error
> > code - An expression is required for the statement to be valid or
complete.
> >
> > My syntax is:
> > Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]
> > ")
> >
> > Cheers,
> > Paul.
> >
> > -----Original Message-----
> > From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> > Sent: Monday, 01 October, 2001 9:46 a.m.
> > To: datastage-users@oliver.com
> > Cc: pko@clear.co.nz
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390 DataStage
> >
> >
> > There was an error in previous "solution". Shouldve read it before
> > clicking Send! :-~
> >
> > You need to convert the "integer form" into a DataStage internal
> > form
before
> > converting it into an external form.
> > Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
> >
> > You can also use substrings and concatenation. Syntax is:
> > int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
> >
> > -----Original Message-----
> > From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> > Sent: Monday, 01 October 2001 07:41
> > To: datastage-users@oliver.com
> > Cc: pko@clear.co.nz
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390 DataStage
> >
> >
> > All you need is Oconv(int_field,"DYMD") in a Transformer stage.
> >
> > There are no data types within DataStage; the recorded data types
> > are
user
> > in interacting with databases.
> > The Oconv() function has specific rules for wholly numeric date
> > forms,
of
> > which the above solution makes use. Eight and six digits are
> > regarded
as
> > "regular" dates (so you need the YMD to assert that your current
> > locale order (DMY in NZ) is not to be followed) with four and two
> > digits in the year respectively. Seven and five digits are
> > interpreted as the ordinal
day
> > number within the year and the year number.
> >
> > The cast function is not known to DataStage BASIC expressions. You
could
> > use it in the selecting SQL (but then the "cat" operator will need
> > to be
the
> > "||" operator (unless "cat" is known to the SQL of DB2, which is
> > outside
my
> > knowledge). But this would be in the user-defined SQL field, not in
> > a column derivation subsequent to extraction.
> >
> > (I assume "from 2 for 4" is a typo!)
> >
> > -----Original Message-----
> > From: Paul Ko [mailto:PKO@clear.co.nz]
> > Sent: Monday, 01 October 2001 07:25
> > To: datastage-users@oliver.com
> > Subject: How to convert an integer date to a real date using OS/390
> > DataStage
> >
> >
> > > Hello,
> > >
> > > There is a field in a DB2 table that is defined as integer. The
> > > field
is
> > > currently storing a valid date. For example, 20010909.
> > >
> > > I am trying to use DataStage to convert this to a real date
> > > format.
How
> > > can it be done using the OS/390 DataStage component.
> > >
> > > My idea is to use substring and cast function to cut and merge the
integer
> > > field into a date field. However, DataStage does not accept my
syntax.
> > >
> > > My syntax is:
> > > substring(cast(int_field as char) from 2 for 4) cat "-" cat
> > > substring(cast(int_field as char) from 6 for 2) cat "-" cat
> > > substring(cast(int_field as char) from 8 for 2)
> > >
> > > Please advise on solution.
> > >
> > > Cheers,
> > > Paul.
> > >
> >
> >
>
>
on Datastage OS/390 you cant use Datastage Server Function/Routine.
Phil try to "move" the input field to a stage variable defined as string; then "manipulate" this stage variable as you like better.
I think it can help you.
Regards,
Riccardo
----- Original Message -----
From: "Phil Walker"
To:
Sent: Monday, October 01, 2001 3:02 AM
Subject: Re: RE: How to convert an integer date to a real date using OS/390 Da taStage
> The solution Ray gave you only works for the server component not on
> the
mainframe.
>
> I do not think Ray read your heading 8^)
>
> Phil
> >
> > From: Paul Ko
> > Date: Mon, 1 Oct 2001 10:06:30 +1200
> > To: "datastage-users@oliver.com"
> > CC: "ray.wurlod@Informix.Com"
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390
Da
> > taStage
> >
> > Hello Ray,
> >
> > It does not work that way in OS/390. I believe it works in the
> > server component.
> >
> > I tried both of your options but both of them returned me with
> > 100008
error
> > code - An expression is required for the statement to be valid or
complete.
> >
> > My syntax is:
> > Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]
> > ")
> >
> > Cheers,
> > Paul.
> >
> > -----Original Message-----
> > From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> > Sent: Monday, 01 October, 2001 9:46 a.m.
> > To: datastage-users@oliver.com
> > Cc: pko@clear.co.nz
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390 DataStage
> >
> >
> > There was an error in previous "solution". Shouldve read it before
> > clicking Send! :-~
> >
> > You need to convert the "integer form" into a DataStage internal
> > form
before
> > converting it into an external form.
> > Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
> >
> > You can also use substrings and concatenation. Syntax is:
> > int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
> >
> > -----Original Message-----
> > From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> > Sent: Monday, 01 October 2001 07:41
> > To: datastage-users@oliver.com
> > Cc: pko@clear.co.nz
> > Subject: RE: How to convert an integer date to a real date using
> > OS/390 DataStage
> >
> >
> > All you need is Oconv(int_field,"DYMD") in a Transformer stage.
> >
> > There are no data types within DataStage; the recorded data types
> > are
user
> > in interacting with databases.
> > The Oconv() function has specific rules for wholly numeric date
> > forms,
of
> > which the above solution makes use. Eight and six digits are
> > regarded
as
> > "regular" dates (so you need the YMD to assert that your current
> > locale order (DMY in NZ) is not to be followed) with four and two
> > digits in the year respectively. Seven and five digits are
> > interpreted as the ordinal
day
> > number within the year and the year number.
> >
> > The cast function is not known to DataStage BASIC expressions. You
could
> > use it in the selecting SQL (but then the "cat" operator will need
> > to be
the
> > "||" operator (unless "cat" is known to the SQL of DB2, which is
> > outside
my
> > knowledge). But this would be in the user-defined SQL field, not in
> > a column derivation subsequent to extraction.
> >
> > (I assume "from 2 for 4" is a typo!)
> >
> > -----Original Message-----
> > From: Paul Ko [mailto:PKO@clear.co.nz]
> > Sent: Monday, 01 October 2001 07:25
> > To: datastage-users@oliver.com
> > Subject: How to convert an integer date to a real date using OS/390
> > DataStage
> >
> >
> > > Hello,
> > >
> > > There is a field in a DB2 table that is defined as integer. The
> > > field
is
> > > currently storing a valid date. For example, 20010909.
> > >
> > > I am trying to use DataStage to convert this to a real date
> > > format.
How
> > > can it be done using the OS/390 DataStage component.
> > >
> > > My idea is to use substring and cast function to cut and merge the
integer
> > > field into a date field. However, DataStage does not accept my
syntax.
> > >
> > > My syntax is:
> > > substring(cast(int_field as char) from 2 for 4) cat "-" cat
> > > substring(cast(int_field as char) from 6 for 2) cat "-" cat
> > > substring(cast(int_field as char) from 8 for 2)
> > >
> > > Please advise on solution.
> > >
> > > Cheers,
> > > Paul.
> > >
> >
> >
>
>