Date values out of and into Microsoft Access

mleroux · Post by **mleroux** » Wed Jul 14, 2004 4:45 am

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.

mleroux · Post by **mleroux** » Wed Jul 14, 2004 8:24 am

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.

mleroux · Post by **mleroux** » Wed Jul 14, 2004 8:45 am

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.

mleroux · Post by **mleroux** » Wed Jul 14, 2004 8:57 am

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.

ketfos · Post by **ketfos** » Wed Jul 14, 2004 10:42 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Jul 14, 2004 2:51 pm

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.

mleroux · Post by **mleroux** » Thu Jul 15, 2004 1:39 am

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?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 15, 2004 4:34 am

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

mleroux · Post by **mleroux** » Thu Jul 15, 2004 5:21 am

ray.wurlod wrote:Here it is!

My point, exactly. If I can see it in Manager, why not in Designer?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 15, 2004 2:52 pm

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?

mleroux · Post by **mleroux** » Fri Jul 16, 2004 12:53 am

ray.wurlod wrote:... or the Re-Build Indexes option from the DS.TOOLS menu on the server.

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:Are you selecting "DS Routine" from the expression editor's operand menu?

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.

rasi · Post by **rasi** » Fri Jul 16, 2004 1:07 am

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