Wrong dates in Hash File

[boligoma]

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...

[chulett]

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:

Code: Select all

DIGITS(Link_SecuentialFile.Date[1,10])

or this converts all dashes to empty strings, effectively removing them from the string.

Code: Select all

Convert("-","",Link_SecuentialFile.Date[1,10])

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?

[ArndW]

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.

[chulett]

You shouldn't be using SUBSTRINGS function, that is specifically for dynamic array strings.

AHA!

[JoshGeorge]

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.

[boligoma]

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.

[chulett]

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.

[ray.wurlod]

The only reason it could be taking the other field is because the developer told it to. Check the output column and/or stage variable derivations carefully.

[sachin1]

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.

[ray.wurlod]

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))