Page 1 of 1
Address nameless columns in Hashed files
Posted: Mon Apr 25, 2011 3:27 pm
by mavrick21
Hello Gurus,
There's a hashed file which has two columns - Col1 and Col2. Col1 contains vachar data and is a key. Col2 is a timestamp field. I need to update Col2 data to '1700-01-01 00:00:00'.
When I do LIST.DICT here is what I get
Code: Select all
Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..
@ID D 0 SDKMaxLastUpdDt 10L S
tm_E
1 records listed.
My try with the following code was unsuccessful:
UPDATE <hashed file> SET Col2 = '1700-01-01 00:00:00'
Got error like - Invalid token at Col2.
How do I address columns in a Hashed file which has no names?
Thanks
-Mav
Posted: Mon Apr 25, 2011 4:49 pm
by ray.wurlod
If you want to use SQL to insert or update, you will need to provide column names in the file dictionary. Search DSXchange for "INSERT INTO DICT" for examples.
Otherwise you can use DataStage BASIC code (which does not need column names) or an operating system command such as UVwrite.
Posted: Mon Apr 25, 2011 5:54 pm
by chulett
Or just build a mapping... err, job... to do that.
Posted: Tue Apr 26, 2011 12:28 am
by zulfi123786
ray.wurlod wrote:Otherwise you can use DataStage BASIC code (which does not need column names) or an operating system command such as UVwrite.
Was wondering how would an update work without providing the column name, how does it reference what has to be updated?
Posted: Tue Apr 26, 2011 6:46 am
by chulett
Hashed files columns are referenced positionally, not by name. Ever wondered why the 'column names' you use in the stage don't really matter? That's why and I assume part of the answer.
Posted: Tue Apr 26, 2011 6:52 am
by zulfi123786
When we try to update a column in a Hashed file, there should be an identifier that holds the position, right?
Posted: Tue Apr 26, 2011 6:48 pm
by ray.wurlod
Not necessarily. Depends how the hashed file was created in the first place and whether the metadata were available at that time.
Re: Address nameless columns in Hashed files
Posted: Thu Apr 28, 2011 6:55 pm
by suresh.angadi
Hi,
delete the hash file with delete.file <File Name> then recreate and populate the data and then try to update the hash file.
Regards
Suresh
Posted: Thu Apr 28, 2011 8:11 pm
by ray.wurlod
That does not address the issue, which is the lack of metadata in the hashed file's file dictionary.
Posted: Fri Apr 29, 2011 2:56 am
by zulfi123786
say the hashfile was created with a path and no VOC entry and i need to update the hashfile (xyz.hs) for the key column value=100 to set a column no 3 to 'A'
to achieve this using SQL i guess we need to make a VOC entry first, define the metadata and then fire the update right?
Posted: Fri Apr 29, 2011 5:22 am
by ray.wurlod
Ordinarily the entry for the field definition would be in the file dictionary rather than the VOC file. If you want to use SQL to change the record, then you will also need a VOC entry for the hashed file itself.
Posted: Fri Apr 29, 2011 5:38 am
by zulfi123786
Thanks Ray.