output from hashed file

[mystuff]

When I do a select on a hashed file (through unix)

I don't get the each row in one line. Each field splits up and shows up in the next line. Something like this.

Code: Select all

ROW1      Fie            Fie 
                   ld1           ld2
ROW2      Fie            Fie 
                   ld1           ld2

instead of

Code: Select all

ROW1      Field1         Field2 
ROW2      Field1         Field2 

How can I avoid such scenario?

[ray.wurlod]

Get the metadata correct, or use command line overrides to get the display widths sufficiently large.

Code: Select all

SELECT @ID FMT '15L', F1 FMT '15L', F2 FMT '15L' FROM hashedfile;

[Dsnew]

Can you please tell me how do you select from a hashed file from unix?

[ray.wurlod]

Code: Select all

cd $ProjectDir

. ${DSHOME}/dsenv

${DSHOME}/bin/dssh "SELECT @ID FMT '15L', F1 FMT '15L', F2 FMT '15L' FROM hashedfile;"

[mystuff]

Get the metadata correct, or use command line overrides to get the display widths sufficiently large.
Code:
SELECT @ID FMT '15L', F1 FMT '15L', F2 FMT '15L' FROM hashedfile;

Code: Select all

DataStage/SQL: VOC dictionary descriptors cannot be used with SQL
DataStage/SQL: syntax error.  Unexpected symbol.  Token was "F1".
Scanned command was FROM HashedFile SELECT @ID FMT "15L" , F1 

I am getting these errors, when I try to execute the above statement.

And how can i read the given code, I am unable to understand the above sql.

[ray.wurlod]

You have to use your column names, not mine.

[mystuff]

I don't know the column information of the hashed file. How can I go without it. I am actually writing

Code: Select all

"select * from hashedfile;"

About the formatting query

SELECT @ID FMT '15L', F1 FMT '15L', F2 FMT '15L' FROM hashedfile;

F1, F2 ---> Are these the column names
15L --> what does this stand for (is it 15 characters in length)
there are three fields selected F1, F2 and third doesn't have any anything such as F1, F2

Code: Select all

SELECT @ID FMT '15L'

. Why is that so?

[ray.wurlod]

Select * means that you will get the default column widths.

LIST.DICT hashedfile will give you a "file dictionary" (table definition) listing. You can get the column names from that - anything with a type "D" or "I" can be used in a SELECT query.

"15L" decodes as 15 characters wide, left justified. "15T" is the same, but breaking on word boundaries. "15R" is right justified.

[mystuff]

a) Doing Trials - Since the column width seems to be the issue. I tried to increase the column width. More than the length of the data.

Still keep getting the same scenario.

b) Another mess up - or One interesting thing. I went into the Hashed file setting (in options, where we select dynamic type etc). There I tried to change the record length.

And next thing I know - every fieldappears in new line.

Tried to replace back the usual settings, but the same thing happens i.e every field appears in the new line.

Even tried to delete the job/hashes (its VOC pointer removed).

Recreated the job (infact made a save as of origin hashed file job and then modified it ), but the same thing is happening i.e. every field is written in a new line

[mystuff]

"15L" decodes as 15 characters wide, left justified. "15T" is the same, but breaking on word boundaries. "15R" is right justified.

I have the widths in hashed file defined way higher than the expected values (there are no extra spaces as well in the data, so i am sure that i am using more width than required)

LIST.DICT hashedfile

The column names obtained from the above command are themselves splitted (not in a single line)

LIST.DICT hashedfile will give you a "file dictionary" (table definition) listing. You can get the column names from that - anything with a type "D" or "I" can be used in a SELECT query.

a) I am trying to write a generic script to obtain the contents of a hashed file, length could vary from 1 till 100 or more. I don't want to use a fixed defined length of the fields by specifying something as "15L".

b) Is there any pre-requisite that the hashed file shoudl be set up in such a way that the splitting doesn't occur - i got the first one (a) sufficient length. But this alone is not solving my problem.

c) I don't know the reason, but while trying I have reset "Record Size" and no matter what I do, delete/recreate the job, reset the record size which was earlier, But I keep getting the each field in new line.

Field1. -4927275313
Field2. Not Applicable
Field3. 2008-05-06 09:06:40
Field4. Test1
Field5. 0
Field1. -4829300379
Field2. Not Applicable
Field3. 2008-03-25 07:38:49
Field4. Test2
Field5. 0
Field1. -4813747248
Field2. Not Applicable
Field3. 2008-03-17 10:51:17
Field4. Test3
Field5. 0
Field1. -4702305701
Field2. Not Applicable
Field3. 2008-01-30 13:14:42
Field4. Test4
Field5. 0

FYI : If at all it helps there is no dot '.' after the fieldname 'Field1'

[ArndW]

Your width per line is over the default of 80. Try "SET.TERM.TYPE WIDTH 132".

[mystuff]

Your width per line is over the default of 80. Try "SET.TERM.TYPE WIDTH 132".

This the same result, with additional message

DEC vt100 terminal (vt100)

[ArndW]

The SET.TERM.TYPE command should be on a separate line; and a subsequent GET.TERM.TYPE command will show a new width of 132 (or you can set it higher, if you wish). Right now, 5 columns of width 15 plus the default 3 space separator is over 80, which is why you are getting this vertically aligned output.

[mystuff]

Yes I do have it in seperate line as below.

Code: Select all

LOGTO <ProjectName>
SET.TERM.TYPE WIDTH 132
SELECT * FROM <HashedFile>;

Is there any max limit to it, as I want this to be generic and wouldn't like my process causing a limitations.

Will this as well solve the splitting of the fields? I will try to look at this one and get back to you.

[ArndW]

I chose 132 as that is the (old) common width for wide printouts, before the days of variable width fonts. You can raise that width, but look at the help file to get the maximum allowable value. In this case, 132 should have been ample. Can you do a "LIST DICT yourhashedfile" ?