|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/29/2006 2:44:00 AM
Posts: 4,
Visits: 1
|
|
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
Rocky
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
Hi Raul,
yes, should be possible.
Could you post some more details, file layout, example records etc.
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/29/2006 2:44:00 AM
Posts: 4,
Visits: 1
|
|
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
Rocky
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/29/2006 2:44:00 AM
Posts: 4,
Visits: 1
|
|
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.
Rocky
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/29/2006 2:44:00 AM
Posts: 4,
Visits: 1
|
|
thanks dieter for prompt reply .
as u have asked i stduy jere in INDIA in IIT Bombay
Rocky
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
|
Would be appreciable if you throw some light please..............
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/24/2009 7:41:51 AM
Posts: 12,
Visits: 55
|
|
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
Regards
Ankit
|
|
|
|