Date values out of and into Microsoft Access
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
Date values out of and into Microsoft Access
Hi
I'm doing a small prototype to update a date dimension in a Microsoft Access database.
I read from the Access table and check the latest date value using the SQL max function. That works fine, as seen from my breakpoint.
My transformer does the following:
Logic: If the dimension has a value, insert a new successive date value.
Constraint: Not(IsNull(Latest_date.DateID))
Derivation: Latest_date.DateID + 1
Logic: If the dimension is empty, insert an initial hard-coded value.
Constraint: IsNull(Latest_date.DateID)
Derivation: Iconv("4 JUL 1996", "D")
Neither of these work, however. They give the following log warnings:
For an empty dimension:
Invalid character value for cast specification (null)
DateID = "13335"
So it looks like it's not accepting the format in this case.
For a dimension with an existing value:
Invalid character value for cast specification (null)
DateID = "1"
It looks like it doesn't add the 1 to the existing date value.
It seems to be a typecasting issue, but I'm relatively new to DataStage so I don't really know how to do this.
I'm doing a small prototype to update a date dimension in a Microsoft Access database.
I read from the Access table and check the latest date value using the SQL max function. That works fine, as seen from my breakpoint.
My transformer does the following:
Logic: If the dimension has a value, insert a new successive date value.
Constraint: Not(IsNull(Latest_date.DateID))
Derivation: Latest_date.DateID + 1
Logic: If the dimension is empty, insert an initial hard-coded value.
Constraint: IsNull(Latest_date.DateID)
Derivation: Iconv("4 JUL 1996", "D")
Neither of these work, however. They give the following log warnings:
For an empty dimension:
Invalid character value for cast specification (null)
DateID = "13335"
So it looks like it's not accepting the format in this case.
For a dimension with an existing value:
Invalid character value for cast specification (null)
DateID = "1"
It looks like it doesn't add the 1 to the existing date value.
It seems to be a typecasting issue, but I'm relatively new to DataStage so I don't really know how to do this.
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
I've now been trying to use the Timestamp DS Routine to accomplish my goal:
To insert the new value:
Timestamp((Timestamp(Latest_date.DateID, "DATE") + 1), "TIMESTAMP")
To insert an initial value:
Timestamp(Iconv("4 JUL 1996", "D"), "TIMESTAMP")
For some reason, I can see this DS Routine in Manager, under Routines\Examples\Functions. But in Designer, I can't see it, only some of the other routines such as ConvertQuarter etc. As a result (I think!! ) the two derivations mentioned above are marked red as errors.
To insert the new value:
Timestamp((Timestamp(Latest_date.DateID, "DATE") + 1), "TIMESTAMP")
To insert an initial value:
Timestamp(Iconv("4 JUL 1996", "D"), "TIMESTAMP")
For some reason, I can see this DS Routine in Manager, under Routines\Examples\Functions. But in Designer, I can't see it, only some of the other routines such as ConvertQuarter etc. As a result (I think!! ) the two derivations mentioned above are marked red as errors.
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
I simplified the insertion of the initial value by just using a timestamp string from the start.
To insert the new value:
Timestamp((Timestamp(Latest_date.DateID, "DATE") + 1), "TIMESTAMP")
To insert an initial value:
"1996-07-04 00:00:00"
I still can't see the Timestamp function in Designer, and I'm pretty sure I'm looking in the right place since I can see the others...
I must be going mad, since I'm talking to myself in this thread.
To insert the new value:
Timestamp((Timestamp(Latest_date.DateID, "DATE") + 1), "TIMESTAMP")
To insert an initial value:
"1996-07-04 00:00:00"
I still can't see the Timestamp function in Designer, and I'm pretty sure I'm looking in the right place since I can see the others...
I must be going mad, since I'm talking to myself in this thread.
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
I solved it by salvaging code from Timestamp in Manager.
To insert the new value:
Oconv(Iconv(MatchField(Latest_date.DateID,"0X' '2N':'2N':'2N",1)
,"D-YMD[4,2,2]") + 1,"D-YMD[4,2,2]"):" 00:00:00"
To insert an initial value:
"1996-07-04 00:00:00"
I STILL can't see the Timestamp function in Designer, and I'm pretty sure I'm looking in the right place since I can see the others... so I'd really appreciate some pointing in the right direction.
It's official; I'm as mad as a cow in England.
To insert the new value:
Oconv(Iconv(MatchField(Latest_date.DateID,"0X' '2N':'2N':'2N",1)
,"D-YMD[4,2,2]") + 1,"D-YMD[4,2,2]"):" 00:00:00"
To insert an initial value:
"1996-07-04 00:00:00"
I STILL can't see the Timestamp function in Designer, and I'm pretty sure I'm looking in the right place since I can see the others... so I'd really appreciate some pointing in the right direction.
It's official; I'm as mad as a cow in England.
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
Hi,
1. You have mentioned that you cannot see the TimeStamp function in Designer and it is marked as red when you use it.
?? What happens when you run the job uisng the TIMESTAMP function in the transformer, even though it is marked as red.
I have seen many times, even if their is red signal in the transformer, I am able to save, compile and execute the job.
?? Do the job runs or gives errors.
2. Are their other functions/routines you are missing in Designer?
Ketfos
1. You have mentioned that you cannot see the TimeStamp function in Designer and it is marked as red when you use it.
?? What happens when you run the job uisng the TIMESTAMP function in the transformer, even though it is marked as red.
I have seen many times, even if their is red signal in the transformer, I am able to save, compile and execute the job.
?? Do the job runs or gives errors.
2. Are their other functions/routines you are missing in Designer?
Ketfos
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
MS Access timestamps include milliseconds, so anything you generate to perform comparisons also need milliseconds. The easiest way to do this is to concatenate ".000" to an ISO8601 timestamp.
Beware, too, of case sensitivity. "Timestamp" and "TimeStamp" are not the same thing.
Beware, too, of case sensitivity. "Timestamp" and "TimeStamp" are not the same thing.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
My code is case-sensitive. It's called Timestamp in Manager, and Timestamp in my code. No joy.
I tried compiling it with the red error signals, but it doesn't compile.
The functions I can see in Manager's Routines\Examples\Functions:
ConvertMonth
ConvertQuarter
ConvertTag
ConvertWeek
ConvertYear
QuarterTag
Timestamp
WeekTag
The functions I can see in Designer's DS Routine...\Examples\Functions:
ConvertMonth
ConvertQuarter
ConvertTag
ConvertWeek
ConvertYear
QuarterTag
WeekTag
So it's only Timestamp that's missing. It's a standard Ascential-provided function, as you know. Do you have to register/include/add a routine or something?
I tried compiling it with the red error signals, but it doesn't compile.
The functions I can see in Manager's Routines\Examples\Functions:
ConvertMonth
ConvertQuarter
ConvertTag
ConvertWeek
ConvertYear
QuarterTag
Timestamp
WeekTag
The functions I can see in Designer's DS Routine...\Examples\Functions:
ConvertMonth
ConvertQuarter
ConvertTag
ConvertWeek
ConvertYear
QuarterTag
WeekTag
So it's only Timestamp that's missing. It's a standard Ascential-provided function, as you know. Do you have to register/include/add a routine or something?
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Here it is!
Code: Select all
The functions I can see in Manager's Routines\Examples\Functions:
ConvertMonth
ConvertQuarter
ConvertTag
ConvertWeek
ConvertYear
QuarterTag
Timestamp <<----------
WeekTag
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Possibly the indexing in the Repository needs re-building. Use the Cleanup button in the Administrator client, or the Re-Build Indexes option from the DS.TOOLS menu on the server.
Are you selecting "DS Routine" from the expression editor's operand menu?
Are you selecting "DS Routine" from the expression editor's operand menu?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 81
- Joined: Wed Jul 14, 2004 3:18 am
- Location: Johannesburg, South Africa
- Contact:
Please allow me to ask a question that will surely make obvious my level 1 status. How do I get to this DS.TOOLS menu?ray.wurlod wrote:... or the Re-Build Indexes option from the DS.TOOLS menu on the server.
Yes, and it wasn't there. And I tried compiling it with the statement just typed in the derivation (taking the case sensitivity into consideration) and that didn't do it, either.ray.wurlod wrote:Are you selecting "DS Routine" from the expression editor's operand menu?
Morney le Roux
There are only 10 kinds of people: Those who understand binary and those who don't.
There are only 10 kinds of people: Those who understand binary and those who don't.
Goto the command line and type uv to enter universe from there type DS.TOOLS which will bring you the menu. Type QUIT to come out of universe.
Alternatively you can use the Datastage Administer to run the command. Go to program folder of Datastage and inside click Datastage ADministrator from there click PRojects tab and you will see command button in it. Press the button and type DS.TOOLS there.
Cheers
Siva
Alternatively you can use the Datastage Administer to run the command. Go to program folder of Datastage and inside click Datastage ADministrator from there click PRojects tab and you will see command button in it. Press the button and type DS.TOOLS there.
Cheers
Siva