Page 1 of 4
output from hashed file
Posted: Mon Jul 28, 2008 3:56 pm
by 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?
Posted: Mon Jul 28, 2008 4:24 pm
by 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;
Posted: Mon Jul 28, 2008 4:30 pm
by Dsnew
Can you please tell me how do you select from a hashed file from unix?
Posted: Mon Jul 28, 2008 4:36 pm
by ray.wurlod
Code: Select all
cd $ProjectDir
. ${DSHOME}/dsenv
${DSHOME}/bin/dssh "SELECT @ID FMT '15L', F1 FMT '15L', F2 FMT '15L' FROM hashedfile;"
Posted: Mon Jul 28, 2008 4:41 pm
by 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.
Posted: Mon Jul 28, 2008 6:06 pm
by ray.wurlod
You have to use your column names, not mine.
Posted: Mon Jul 28, 2008 6:36 pm
by mystuff
I don't know the column information of the hashed file. How can I go without it. I am actually writing
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
. Why is that so?
Posted: Mon Jul 28, 2008 7:42 pm
by 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.
Posted: Mon Jul 28, 2008 11:14 pm
by 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
Posted: Tue Jul 29, 2008 9:40 am
by 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'
Posted: Tue Jul 29, 2008 9:58 am
by ArndW
Your width per line is over the default of 80. Try "SET.TERM.TYPE WIDTH 132".
Posted: Tue Jul 29, 2008 10:11 am
by mystuff
ArndW wrote: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)
Posted: Tue Jul 29, 2008 11:36 am
by 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.
Posted: Tue Jul 29, 2008 12:22 pm
by 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.
Posted: Tue Jul 29, 2008 12:32 pm
by 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" ?