multiple layout in for a singlr file

[rocky]

hi this is raul

is it possible in mload to use different layout for same infile,
i mean i am receving data in which one occurence of column is of datatype integer and the other occurence of data is of datatype varchar -- i hope i made it clear

kindly can u reply fast, it will solve one of the major problem hindering me to go ahead in my university project

[dnoeth]

Hi Raul,
yes, should be possible.
Could you post some more details, file layout, example records etc.

Dieter

[rocky]

dnoeth the following is the records which i am receving

these are just an example records which i am trying to test

12|2.3|b
10|c|t

if u observe in the above record i am getting 2,3 which is of decimal datatype and in second record 'c' which is of character datatype

one solution whic i could come up was i used .accept command of mload like this

.accept flag from ignore 1 to 3 --which will enable me to catch the data prsent in third column i.e either 'b' or 't' --iam doing this because based on 'b'or 't' only my records are going to get varied then iam using following layouts

layout test1
.field c1 * integer
.field c2 * deciaml(4,1)
.filed c3 * varchar(2)

layout test2
field c1 * integer
.field c2 * varchar(4)
.filed c3 * varchar(2)

later iam using if else conditional statements of mload,
it's working fine no errors but while loading data of 1st record type some garbage value is going into my target table

while loading data of second record type it was loading properly

my target table datatypes are

c1:ineteger
c2:varchar(20)
c3:varchar(2)

regards
raul

[dnoeth]

Hi Raul,
ACCEPT and IF/ELSE are the wrong places, you have to redefine the input layout and use different APPLYs, e.g.

.layout test;
.field c1 * integer;
.field c2d * decimal(4,1);
.field c2v 3 varchar(4); -- redefine the input position
.field c3 * varchar(2);

.dml label l1;
sql for c3='b'

.dml label l1;
sql for c3='t'

.IMPORT ...
FORMAT ???
LAYOUT test
APPLY l1 WHERE c3 = 'b'
APPLY l2 WHERE c3 = 't';

Regading the input records, what's the FORMAT: BINARY\TEXT\VARTEXT?
Is it readable information or binary data?

Dieter

[rocky]

thanks dieter

the solution was good, as you have asked regarding the format of data,according to my perception it will in text i guess but it is just an asumption because my prof didnot told me the exact situtaion yet

as a matter of curisosity can u confirm why the .accept and if/else commands was not fealisble for this kind of problems.

[dnoeth]

Hi Raul,
if the input format is readable text or vartext, then you don't specify DECIMAL within the FIELD, it's CHAR or VARCHAR. The cast will be done automatically or by using CAST(col as DEC(..)) within the Insert.

ACCEPT reads a *single* row from input, but you want to load a file with lots of rows in different formats.
Check the manuals for "redefining the layout".

You talked about your prof, so out of curiosity:
Which university offers Teradata classes?

Dieter

[rocky]

thanks dieter for prompt reply .

as u have asked i stduy jere in INDIA in IIT Bombay

[prakhar]

Hi Forum Masters,

I was facing the same problem as described in this post...

I am not getting how this repositioning really works...

Suppose my input records are

12|2.3|b

10|c|t

layout is

.layout test;

.field c1 * integer;

.field c2d * decimal(4,1);

.field c2v 3 varchar(4); -- redefine the input position

.field c3 * varchar(2);

client will read 12 store it in field c1...next is 2.3 stored in c2d after that it will reposition and again come to 3rd position that is 2of decimal 2.3 or directly go to c....basically not able to get what will happen to 'b'

Please throw some insight how exactly it is going on.......

Your help will make all of us better understand this redefining the input concept Gurus

[prakhar]

Would be appreciable if you throw some light please..............

[Anonymous]

Prakhar,

Layout reflects the source file format.
So in the case above it will check data field by field, when it encounters decimal it will go with c2d skips c2v and goes on as you have correctly mentioned.

For the 2nd scenario of c3='t', it will encounter varchar which is not of data type decimal as in layout, so the pointer is lost as it does not matches with the field type, but as we specified position 3, so it will start reading from that field onwards and take the field as varchar.

Hope it helps...

Ankit