Wrong dates in Hash File
Moderators: chulett, rschirm, roy
Wrong dates in Hash File
Hi,
I'm having trouble with inserting some dates in a hash file. Let me give you an example.
I have a sequential file with a varchar(255) column that has the date (e.g. 2006-11-13-00.52.50.932000), I'm using the following formula to extract it in a yyyymmdd format:
Substrings(Link_SecuentialFile.Date,1,4):Substrings(Link_SecuentialFile.Date,6,2):Substrings(Link_SecuentialFile.Date,9,2)
(I have to use integers in the date because that's the way it matches with another table).
When I insert in an oracle table it works just fine, but when I insert it in a hash file there are several records that are inserted like this: 00010101. No record in the sequential file has this date and all the records has the same structure.
I'm new in DS but more new in using Hash Files. Thanks for any reply...
I'm having trouble with inserting some dates in a hash file. Let me give you an example.
I have a sequential file with a varchar(255) column that has the date (e.g. 2006-11-13-00.52.50.932000), I'm using the following formula to extract it in a yyyymmdd format:
Substrings(Link_SecuentialFile.Date,1,4):Substrings(Link_SecuentialFile.Date,6,2):Substrings(Link_SecuentialFile.Date,9,2)
(I have to use integers in the date because that's the way it matches with another table).
When I insert in an oracle table it works just fine, but when I insert it in a hash file there are several records that are inserted like this: 00010101. No record in the sequential file has this date and all the records has the same structure.
I'm new in DS but more new in using Hash Files. Thanks for any reply...
What data type did you use in the hashed file? I point out the 'ed' at the end before you-know-who comes along and does the same.
If you used Date try sticking with a Varchar. I'd also suggest using the substring operator "[]" rather than the 'substrings' function, wish I could remember exactly why but the former is preferred. And just substring once and then strip the dashes out. This uses the 'digits' transform to pull numbers out of a string:
or this converts all dashes to empty strings, effectively removing them from the string.
Either way should work for you. Both of these assume the month and day portions of your dates are always two digits. Is that the case?
If you used Date try sticking with a Varchar. I'd also suggest using the substring operator "[]" rather than the 'substrings' function, wish I could remember exactly why but the former is preferred. And just substring once and then strip the dashes out. This uses the 'digits' transform to pull numbers out of a string:
Code: Select all
DIGITS(Link_SecuentialFile.Date[1,10])
Code: Select all
Convert("-","",Link_SecuentialFile.Date[1,10])
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You shouldn't be using SUBSTRINGS function, that is specifically for dynamic array strings. Use the format Link_SecuentialFile.Date[1,4] instead.
If you still have a problem, try replacing the hashed file with a sequential file, and also add in a column containing the original varchar column to see if you can identify the records that are not going out the way you want.
If you still have a problem, try replacing the hashed file with a sequential file, and also add in a column containing the original varchar column to see if you can identify the records that are not going out the way you want.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Regardless of substring operator you are using >>>
Link_SecuentialFile.Date [1,4] : Link_SecuentialFile.Date [6,2] : Link_SecuentialFile.Date [9,2] or
Substrings(Link_SecuentialFile.Date,1,4):Substrings(Link_SecuentialFile.Date,6,2):Substrings(Link_SecuentialFile.Date,9,2) or
Convert("-","",Link_SecuentialFile.Date[1,10])
(As noted, later ones are suggested)
Your problem with getting 00010101 values in hash file (If you have specified the column in hash file as integer) is something else. make sure your source data is of right format. May be you should pick those corresponding records which changes to 00010101 from the source and investigate.
Link_SecuentialFile.Date [1,4] : Link_SecuentialFile.Date [6,2] : Link_SecuentialFile.Date [9,2] or
Substrings(Link_SecuentialFile.Date,1,4):Substrings(Link_SecuentialFile.Date,6,2):Substrings(Link_SecuentialFile.Date,9,2) or
Convert("-","",Link_SecuentialFile.Date[1,10])
(As noted, later ones are suggested)
Your problem with getting 00010101 values in hash file (If you have specified the column in hash file as integer) is something else. make sure your source data is of right format. May be you should pick those corresponding records which changes to 00010101 from the source and investigate.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
The datatype in the hash(ed) file is an integer, I have to use the integer type because that's the type of the date in this company. The source data is always the format I wrote.
The day and the month are always in 2 digits format, and the year in 4 digits format.
I tried to use the digits format but I still got the 00010101 for dates before 2001, I don't know why. Also I investigated and there is another date field (with a different name) that has a 0001-01-01-00:00:00:000000 in every row, I don't know why is taking that field instead of the original one.
The day and the month are always in 2 digits format, and the year in 4 digits format.
I tried to use the digits format but I still got the 00010101 for dates before 2001, I don't know why. Also I investigated and there is another date field (with a different name) that has a 0001-01-01-00:00:00:000000 in every row, I don't know why is taking that field instead of the original one.
Hmm... then it sounds like you gots more investigat'n to do.
And no, you don't have to use an Integer as the Server product is 'typeless' under the covers and basically treats everything as a string, but that's a discussion for a different day.
And no, you don't have to use an Integer as the Server product is 'typeless' under the covers and basically treats everything as a string, but that's a discussion for a different day.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Wrong dates in Hash File
hello i have tried with same case as yours and it works for hash file as well for seq file when i use below conditon
input seq file varchar length (40), 2006-11-13-00.52.50.932000
transformer: DSLink36.date1[1,4]:DSLink36.date1[6,2]:DSLink36.date1[9,2]
output hash file integer length(10): 20061113
in your case it is strange.
input seq file varchar length (40), 2006-11-13-00.52.50.932000
transformer: DSLink36.date1[1,4]:DSLink36.date1[6,2]:DSLink36.date1[9,2]
output hash file integer length(10): 20061113
in your case it is strange.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If InLink.TheColumn has the value "2006-11-13-00.52.50.932000" then a suitable expression would be:
Code: Select all
DIGITS(Field(InLink.TheColumn,"-",1,3))
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.