Char To Date Converstion

pradeep_nov18 · Post by **pradeep_nov18** » Wed Aug 13, 2008 12:25 am

Hi Datastage EXperts,

I am converting the char(16) to Date(10) the derivation i am using is shown below:

StringToDate(If (TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT)) = '00000000' Then SetNull() Else (TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [1,4] :"-" : TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [5,2] : "-" : TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [7,2]),"%yyyy-%mm-%dd")

But it is showing some compilation error

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 23:05:28(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.0.1.4760
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved

##I IIS-DSEE-TUTL-00031 23:05:28(001) <main_program> The open files limit is 1024; raising to 65535.
##I IIS-DSEE-TOSH-00002 23:05:29(000) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 23:05:29(001) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 23:05:29(002) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 23:05:29(005) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##E IIS-DSEE-TBLD-00076 23:05:33(000) <main_program> Error when checking composite operator: Subprocess command failed with exit status 256.
##E IIS-DSEE-TFSR-00019 23:05:33(001) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFTM-00012 23:05:33(002) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##W IIS-DSEE-TBLD-00000 23:05:33(003) <main_program> Error when checking composite operator: Output from subprocess: "/local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.C", line 775: Error: InterVar0local0InterVar0_1 is not defined.
"/local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.C", line 775: Error: set_null is not a member of APT_TOFunctions.
2 Error(s) detected.

##W IIS-DSEE-TBLD-00000 23:05:33(004) <main_program> Error when checking composite operator: Output from subprocess: ld: fatal: file /local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.tmp.o: open failed: No such file or directory
ld: fatal: File processing errors. No output written to /local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.so

##I IIS-DSEE-TBLD-00079 23:05:33(005) <transform> Error when checking composite operator: /opt/SUNWspro/bin/CC -L/local/projects/ebi_prjs/ebi/RT_BP1945.O/ -L/local/apps/IBM/InformationServer/Server/PXEngine/lib -L/local/apps/IBM/InformationServer/Server/PXEngine/user_lib -G -library=iostream -lorchsun4 -lorchcoresun4 -lorchbuildopsun4 /local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.tmp.o -o /local/projects/ebi_prjs/ebi/RT_BP1945.O/V39S2_mat_unmat_o.so.
##E IIS-DSEE-TCOS-00029 23:05:33(006) <main_program> Creation of a step finished with status = FAILED. (mat_unmat.o)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V39S2_mat_unmat_o transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 SAP_ATL_COST_SAVING_In;
0007: outputname 0 DSLink4;
0008: outputname 1 DSLink2;
0009:
0010: initialize {
0011: // define our row rejected variable
0012: int8 RowRejected0;
0013:
0014: // define our null set variable
0015: int8 NullSetVar0;
0016:
0017: // declare our intermediate variables for this section
0018: string InterVar0_0;
0019: string InterVar0_2;
0020: string InterVar0_3;
0021:
0022: // initialise constant values which require conversion
0023: InterVar0_0 = "00000000";
0024: InterVar0_2 = "-";
0025: InterVar0_3 = "%yyyy-%mm-%dd";
0026: }
0027:
0028: mainloop {
0029: // initialise our row rejected variable
0030: RowRejected0 = 1;
0031:
0032: // declare our intermediate variables for this section
0033: string InterVar0_1;
0034:
0035: // evaluate columns (no constraints) for link: DSLink4
0036: NullSetVar0 = 0;
0037: if ((trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) == InterVar0_0)) {
0038: NullSetVar0 = 1;
0039: } else {
0040: DSLink4.ZPRT_COSTSAVINGS_BEDAT = trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT);
0041: }
0042: DSLink4.ZPRT_COSTSAVINGS_BEDAT = (NullSetVar0 == 1) ? set_null() : DSLink4.ZPRT_COSTSAVINGS_BEDAT;
0043: writerecord 0;
0044: RowRejected0 = 0;
0045: // evaluate columns (no constraints) for link: DSLink2
0046: NullSetVar0 = 0;
0047: if ((trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) == InterVar0_0)) {
0048: NullSetVar0 = 1;
0049: } else {
0050: InterVar0_1 = ((((substring_1(trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) , 1 , 4) + InterVar0_2) + substring_1(trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) , 5 , 2)) + InterVar0_2) + substring_1(trim_leading_trailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) , 7 , 2));
0051: }
0052: DSLink2.ZPRT_COSTSAVINGS_BEDAT = date_from_string((NullSetVar0 == 1) ? set_null() : InterVar0_1 , InterVar0_3);
0053: writerecord 1;
0054: RowRejected0 = 0;
0055: }
0056:
0057: finish {
0058: }
0059:
*** End of Internal Generated Transformer Code

Can anyone tel how to acheive the compilation error free.

Thanks in advanace

ray.wurlod · Post by **ray.wurlod** » Wed Aug 13, 2008 12:39 am

You can't supply NULL to StringToDate() - you therefore need to re-arrange your logic and only do StringToDate() in the Else part of your conditional (If) expression.

The error message relating to set_null comes from the conversion process from your "BASIC" expression to C++ code (operators).

pradeep_nov18 · Post by **pradeep_nov18** » Wed Aug 13, 2008 1:07 am

ray.wurlod wrote:You can't supply NULL to StringToDate() - you therefore need to re-arrange your logic and only do StringToDate() in the Else part of your conditional (If) expression.

The error message relating to ...

Thanks for reply Ray,

But the changed the derivation to:
If ((TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT)) = '00000000' )Then SetNull() Else StringToDate((TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [1,4] :"-" : TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [5,2] : "-" : TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) [7,2]),"%yyyy-%mm-%dd")

but the column Values such has '00000000' is gettin truncated in date field it is nopt setting null (fro eg:i have 17083 rec but 11080 are '00000000' except this zeros rest of the values are coming i am unable to set null in date(10) field but the target is char(10) it is setting to Null())

Please how to achieve null and any suggestion values more

ray.wurlod · Post by **ray.wurlod** » Wed Aug 13, 2008 1:48 am

OK, let's simplify things a bit. Declare a stage variable as type VarChar(8) and assign SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT to it (applying TrimLeadingTrailing if you must).

Your expression will then be much simpler (and thus easier to maintain.

Code: Select all

If sv="00000000" Then SetNull() Else sv[1,4]:"-":sv[5,2]:"-":sv[7,2]

Is your Date field on the output link defined as nullable?

What stage type is on the other end of this link? If a Sequential File stage have you configured the Null Field Value property?

dspxlearn · Post by **dspxlearn** » Wed Aug 13, 2008 1:57 am

Pradeep,

As Ray said, we cannot apply any kind of functions on a Null value. As per your code, if a null comes you will be Trimming which will lead you into fatal.
In you derivation first check for Null values then do rest of the coding. Also, if you can get rid of redundant brackets, your code will look more simplified. I would code in the below way:

Code: Select all

If IsNotNull(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) Then If Trim(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) <> '00000000' Then
StringToDate(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT[1,4] :"-" : SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT[5,2] : "-" : SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT[7,2],"%yyyy-%mm-%dd") Else SetNull() Else SetNull()

pradeep_nov18 · Post by **pradeep_nov18** » Wed Aug 13, 2008 2:19 am

ray.wurlod wrote:OK, let's simplify things a bit. Declare a stage variable as type VarChar(8) and assign SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT to it (applying TrimLeadingTrailing if you must).

Your express ...

Hi Ray,

Thanks For the Reply

I tried with stagevar(i have test<Svname> and derv<TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) with datatypevarchar(8)) and column derviation i have entered as below
If (test = '00000000' )Then SetNull() Else StringToDate(test [1,4] :"-" : test [5,2] : "-" : test [7,2],"%yyyy-%mm-%dd")

But it is still not working

ijob is sucessful with warning

Sequential_File_0,0: Field "ZPRT_COSTSAVINGS_BEDAT" is null but no null export handling is defined
Sequential_File_0,0: Export was unsuccessful at record 1; contin

how to handle this warning and null export handling.

Thanks

amarpatkar · Post by **amarpatkar** » Wed Aug 13, 2008 3:05 am

pradeep_nov18 wrote:
ray.wurlod wrote:OK, let's simplify things a bit. Declare a stage variable as type VarChar(8) and assign SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT to it (applying TrimLeadingTrailing if you must).

Your express ...
Hi Ray,

Thanks For the Reply

I tried with stagevar(i have test<Svname> and derv<TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) with datatypevarchar(8)) and column derviation i have entered as below
If (test = '00000000' )Then SetNull() Else StringToDate(test [1,4] :"-" : test [5,2] : "-" : test [7,2],"%yyyy-%mm-%dd")

But it is still not working

ijob is sucessful with warning

Sequential_File_0,0: Field "ZPRT_COSTSAVINGS_BEDAT" is null but no null export handling is defined
Sequential_File_0,0: Export was unsuccessful at record 1; contin

how to handle this warning and null export handling.

Thanks

Mate,

This would be one way of doing it.

IF
TRIMLEADINGTRAILING(inputColumn) = someValue
THEN
SETNULL()
ELSE
STRINGTODATE(TRIMLEADINGTRAILING(inputColumn[1,10],"%YYYY%MM%DD")

ray.wurlod · Post by **ray.wurlod** » Wed Aug 13, 2008 3:09 am

Read the error message. NO EXPORT HANDLING IS DEFINED.

This is what I was getting at when asking you about whether Null Field Value is specified.

The problem now is in your Sequential File stage. This is pellucid in the error message.

amarpatkar · Post by **amarpatkar** » Wed Aug 13, 2008 3:15 am

amarpatkar wrote:
pradeep_nov18 wrote:
ray.wurlod wrote:OK, let's simplify things a bit. Declare a stage variable as type VarChar(8) and assign SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT to it (applying TrimLeadingTrailing if you must).

Your express ...
Hi Ray,

Thanks For the Reply

I tried with stagevar(i have test<Svname> and derv<TrimLeadingTrailing(SAP_ATL_COST_SAVING_In.ZPRT_COSTSAVINGS_BEDAT) with datatypevarchar(8)) and column derviation i have entered as below
If (test = '00000000' )Then SetNull() Else StringToDate(test [1,4] :"-" : test [5,2] : "-" : test [7,2],"%yyyy-%mm-%dd")

But it is still not working

ijob is sucessful with warning

Sequential_File_0,0: Field "ZPRT_COSTSAVINGS_BEDAT" is null but no null export handling is defined
Sequential_File_0,0: Export was unsuccessful at record 1; contin

how to handle this warning and null export handling.

Thanks
Mate,

This would be one way of doing it.

IF
TRIMLEADINGTRAILING(inputColumn) = someValue
THEN
SETNULL()
ELSE
STRINGTODATE(TRIMLEADINGTRAILING(inputColumn[1,10],"%YYYY%MM%DD")

Sorry, I would also add null checking and rewrite it as:

IF
isNotNull(inputColumn)
THEN
(
IF
TRIMLEADINGTRAILING(inputColumn) = someValue
THEN
SETNULL()
ELSE
STRINGTODATE(TRIMLEADINGTRAILING(inputColumn[1,10],"%YYYY%MM%DD")
)
ELSE
inputColumn

pradeep_nov18 · Post by **pradeep_nov18** » Wed Aug 13, 2008 4:10 am

Thanks All :D !!!!!!!!!!!!!!!!!
I implemented for my scenario.Thanks for your kind reply.You guys are really masters in DS.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 13, 2008 4:32 am

Might you therefore contemplate marking this thread as Resolved? Use the big green button at the top of the page.

pradeep_nov18 · Post by **pradeep_nov18** » Wed Aug 13, 2008 4:53 am

ya i have done that.
Thanks once again