What am I doing wrong.
I first did this and it worked fine unless I encountered an invalid date like '999999' StringToDate(stgAcctDate, '%1950yy%mm%dd')
So, then I added this to take care of that: if IsValid('Date', stgAcctDate) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Everything outputed was then being passed as NULL, even valid dates. So, then I asked myself how does DataStage know 'Date' is in format 'YYMMDD' like I'm passing it.
So, then I did this: if IsValid('Date', stgAcctDate['%yy%mm%dd']) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Still every record being outputed as NULL
So, then I put in a valid date: if IsValid('Date', '100401'['%yy%mm%dd']) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Still every record being outputted as NULL.
What am I doing wrong.
Thanks,
Glenn
IsValid using Date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you use the expression builder to build your expression you will get the syntax for IsValid() function as having two or three arguments - the third argument is a date format string. Further, input column names need to be qualified with the link name, which also happens automatically if you use the expression editor.
There is also an IsValidDate() function - at least in version 8.5 - which expects a date string as its single argument.
Code: Select all
IsValid("Date", InLink.stgAcctDate, "%yy%mm%dd")
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.
Are you writing this code in a Stage Variable? Looks like that since I see the Stage Variable name in your code and not link name. I am surprised you can even able to compile this code on Windows. We had a similar code and job would not even compile on AIX in 8.x environment though it would run fine in 7.5.x environment. Following is the response which I have from IBM support:
The problem here is that Stage variables do not have a "nullable" option
as DataStage considers them to be non-nullable, thus the usage of
SetNull() for stage variables is not supported. IIS 8.1 traps this
condition and will report it as an error. DataStage 7.5 did not trap
this condition at compile time which means that the job had potential
to fail at runtime if a null actually did get assigned.
.
The capability to support SetNull() for stage variables was under
consideration for IIS 8.5, but not for earlier releases. Thus for IIS
8.1 the recommendation is to set the field to '' (2 single quotes)
rather than using SetNull() function.
The problem here is that Stage variables do not have a "nullable" option
as DataStage considers them to be non-nullable, thus the usage of
SetNull() for stage variables is not supported. IIS 8.1 traps this
condition and will report it as an error. DataStage 7.5 did not trap
this condition at compile time which means that the job had potential
to fail at runtime if a null actually did get assigned.
.
The capability to support SetNull() for stage variables was under
consideration for IIS 8.5, but not for earlier releases. Thus for IIS
8.1 the recommendation is to set the field to '' (2 single quotes)
rather than using SetNull() function.
Assume everything I say or do is positive
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
I'll try the latest two examples and let you guys know.
regarding am I writing to a stage variable, no. You are correct in that you can't write a null to a stag variable. This code is simply using a stage variable in the calc to write to an output. In the stage variables I check for Null first, if it is I move '999999' to the field which would force an invalid date, making the output null
regarding am I writing to a stage variable, no. You are correct in that you can't write a null to a stag variable. This code is simply using a stage variable in the calc to write to an output. In the stage variables I check for Null first, if it is I move '999999' to the field which would force an invalid date, making the output null
so it looks like IsValid('Date',StringToDate(stgAcctDate, '%1950yy%mm%dd')) worked
I just get the annoying warning: Conversion error calling conversion routine date_from_string data may have been lost: which I'll have to ignore in the message handler for each date that I have in the transformmer. Other then that the date or NULL gets populated in the table.
thanks for your help
I just get the annoying warning: Conversion error calling conversion routine date_from_string data may have been lost: which I'll have to ignore in the message handler for each date that I have in the transformmer. Other then that the date or NULL gets populated in the table.
thanks for your help