Page 3 of 3
Posted: Sat Sep 27, 2008 3:10 am
by ray.wurlod
Substrings and concatenation will be the most efficient, and remember that there are no data types within DataStage server jobs.
Code: Select all
Right(InLink.TheString,2) : "/" : InLink.TheString[5,2] : "/" : Left(InLink.TheString,4)
Another nice thing about this solution is that it works both for YYYYMMDD date strings and for YYYYDDMM date strings.
Posted: Wed Oct 01, 2008 6:48 am
by pxraja
ray.wurlod wrote:Substrings and concatenation will be the most efficient, and remember that there are no data types within DataStage server jobs.
Code: Select all
Right(InLink.TheString,2) : "/" : InLi ...[/quote]
Hi Ray,
I am using custom query in the ODBC STAGE, in that I am using
TO_DATE(Field1,'YYYYMMDD') getting as Inlink.Field1 and in the transformer I am using
Inlink.Field1[8,2]:"/":Inlink3.Field[5,2]:"/":Left(Inlink.Field,4) and getting to Outlink.Field1 whose datatype is DATE in Oracle database.
as you are saying I tried for running 1000 rows, It has passed from Inlink.Field1 upto transformer but the problem is from transformer to target ODBC no rows were populated.
while viewing in the ODBC Stage the data appears as
2008-02-22 00:00:00
whats wrong with the query? any suggestions plz?
Thanks in advance
Posted: Wed Oct 01, 2008 7:14 am
by chulett
chulett wrote:When you use TO_DATE() all that matters is that your mask/picture matches the data.
Yours still doesn't.
Posted: Wed Oct 01, 2008 8:06 am
by deva
Hi Can you try with the follwoing formate
Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or
Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")
Posted: Wed Oct 01, 2008 8:09 am
by deva
Hi Can you try with the follwoing formate
Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or
Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")
Posted: Wed Oct 01, 2008 8:19 am
by chulett
Not going to help.
Posted: Sat Oct 04, 2008 1:01 am
by pxraja
deva wrote:Hi Can you try with the follwoing formate
Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or
Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")
i found my nls_session_parameters having date format DD-MON-RR, and tried for
my custom query having TO_DATE(Inlink.Field1,'YYYYMMDD')
Oconv(Iconv(Substrings(Inlink.Field1,1,10),'D-YMD[4,2,2]'),'D-DMY[2,3,2]')
this code is working fine with tabel having Outlink.Field1 with varchar datatype, tested for 1000 rows Outlink.Field1 having 1000 rows with desired output
when I changed to table having Outlink.Field1 with DATE datatype its not inserting records into the table,i.e, Outlink.Field1 is NULL with 1000 rows.
I couldnot get into this behaviour, any suggestions please
thanks in advance
Posted: Sat Oct 04, 2008 1:32 am
by ray.wurlod
If you want alphbetic month characters you have to specify the same. One way to do this is:
Code: Select all
Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D-DMY[2,A3,2]')
Another way is
Code: Select all
Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D2-DMBY')
Don't use Substrings() - it's less efficient because it includes code to handle multi-valued data. Prefer regular substring (the square bracket notation) or Left() or Right() function where relevant.
Posted: Sat Oct 04, 2008 2:47 am
by pxraja
ray.wurlod wrote:If you want alphbetic month characters you have to specify the same. One way to do this is:
Code: Select all
Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D-DMY[2,A3,2] ...[/quote]
I tried your code, but its not populating records into Outlink.Field1.
my nls_date_format is like DD-MON-RR but when viewing in TOAD editor it display's like 22/09/2008 why is it so? anyrelation with the record not entering the table?
Any suggestions for understanding this ?
Thanks in Advance
Posted: Sat Oct 04, 2008 3:30 am
by ray.wurlod
Get your head around this idea.
A date is a date is a date.
Inside Oracle (or any other database, pretty much) it is stored as a binary value.
Different tools have different requirements and standards as to how the date should be represented for human consumption. Usually these are configurable (for example in Oracle via the TO_DATE and TO_CHAR functions with different date format pictures). Where they are not configurable (for example ODBC) then you must play the game by those rules. Dates supplied to ODBC must be in YYYY-MM-DD format. No exceptions.
Posted: Sat Oct 04, 2008 3:51 am
by pxraja
ray.wurlod wrote:Get your head around this idea.
A date is a date is a date.
Inside Oracle (or any other database, pretty much) it is stored as a binary value.
Different tools have different requirements and ...
my question is why its not populating the records into the target table in oracle?
No lights blowing on my head.
any suggestions please
thanks in advance
Posted: Sat Oct 04, 2008 4:33 am
by ray.wurlod
Suggestion: buy a premium membership so you can read my suggestions. The answer is, I believe, in those parts of the answer that you can not see.
Posted: Sat Oct 04, 2008 8:02 am
by chulett
Never mind the fact that I've already explained what you need to do to make this work. Can't believe we're still going round and round and up to 3 pages now.
