DSXchange

Hi,
I want to implement the following oracle function in DS Transformer.

The oracle function is ---- decode(MO_NBR,01 through 09,Fyear +0,
10 through 12, Fyear +1)

While i implement this function as --- if (in_Xfm_2.MO_NBR =01 or in_Xfm_2.MO_NBR= 02 or in_Xfm_2.MO_NBR=03 or in_Xfm_2.MO_NBR= 04 or in_Xfm_2.MO_NBR= 05 or in_Xfm_2.MO_NBR= 06 or in_Xfm_2.MO_NBR= 07 or in_Xfm_2.MO_NBR= 08 or in_Xfm_2.MO_NBR= 09 ) then in_Xfm_2.FYEAR
else if (in_Xfm_2.MO_NBR =10 or in_Xfm_2.MO_NBR=11 or in_Xfm_2.MO_NBR=12 )
then in_Xfm_2.FYEAR+1 else '0'
in DS Transformer. i am getting the following error.

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 21:30:42(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.5.0.5946
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved



##I IIS-DSEE-TFCN-00006 21:30:42(001) <main_program> conductor uname: -s=Linux; -r=2.6.32-220.el6.x86_64; -v=#1 SMP Wed Nov 9 08:03:13 EST 2011; -n=hcl-boeing-ds; -m=x86_64
##I IIS-DSEE-TOSH-00002 21:30:42(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 21:30:42(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 21:30:42(004) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 21:30:42(007) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##E IIS-DSEE-TBLD-00076 21:30:43(000) <main_program> Error when checking composite operator: Subprocess command failed with exit status 256.
##E IIS-DSEE-TFSR-00019 21:30:43(001) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFTM-00012 21:30:43(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-TFEV-00025 21:30:43(003) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(004) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(005) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(006) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(007) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(008) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(009) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(010) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(011) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(012) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(013) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(014) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(015) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(016) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(017) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(018) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(019) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(020) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(021) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(022) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(023) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(024) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(025) <transform> Error when checking composite operator: Possible range limitation.
##W IIS-DSEE-TFEV-00023 21:30:43(026) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 21:30:43(027) <transform> Error when checking composite operator: Converting number to ustring.
##W IIS-DSEE-TFEV-00023 21:30:43(028) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "UString".
##W IIS-DSEE-TFEV-00023 21:30:43(029) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00023 21:30:43(030) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00023 21:30:43(031) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00023 21:30:43(032) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 21:30:43(033) <transform> Error when checking composite operator: Converting ustring to string using codepage UTF-8.
##W IIS-DSEE-TFEV-00023 21:30:43(034) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "String".
##W IIS-DSEE-TFEV-00025 21:30:43(035) <transform> Error when checking composite operator: Converting ustring to number.
##W IIS-DSEE-TFEV-00023 21:30:43(036) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "DFloat".
##W IIS-DSEE-TFEV-00025 21:30:43(037) <transform> Error when checking composite operator: Converting number to string.
##W IIS-DSEE-TFEV-00023 21:30:43(038) <transform> Error when checking composite operator: Implicit conversion from source type "DFloat" to result type "String".
##W IIS-DSEE-TFEV-00023 21:30:43(039) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "Int32".
##W IIS-DSEE-TFEV-00023 21:30:43(040) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "Int32".
##W IIS-DSEE-TFEV-00023 21:30:43(041) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "Int32".
##W IIS-DSEE-TFEV-00023 21:30:43(042) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "Int32".
##W IIS-DSEE-TFEV-00023 21:30:43(043) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "Int32".
##W IIS-DSEE-TFEV-00023 21:30:43(044) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 21:30:43(045) <transform> Error when checking composite operator: Converting decimal to ustring.
##W IIS-DSEE-TFEV-00023 21:30:43(046) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "UString".
##W IIS-DSEE-TFEV-00025 21:30:43(047) <transform> Error when checking composite operator: Converting ustring to decimal.
##W IIS-DSEE-TFEV-00023 21:30:43(048) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "Decimal".
##W IIS-DSEE-TBLD-00000 21:30:43(049) <main_program> Error when checking composite operator: Output from subprocess: /opt/IBM/InformationServer/Server/Projects/EFBIHCL_BASE/RT_BP394.O/V0S115_PXJ_D_CSPR_CALENDAR_ld_dim_Xfm_2.C:585:35:
##W IIS-DSEE-TBLD-00000 21:30:43(050) <main_program> Error when checking composite operator: Output from subprocess: error: invalid digit "8" in octal constant
/opt/IBM/InformationServer/Server/Projects/EFBIHCL_BASE/RT_BP394.O/V0S115_PXJ_D_CSPR_CALENDAR_ld_dim_Xfm_2.C:586:35: error: invalid digit "9" in octal constant

##I IIS-DSEE-TBLD-00079 21:30:43(051) <transform> Error when checking composite operator: g++ -c -Wno-deprecated -m64 -mtune=generic -mcmodel=small -fPIC -O -I/opt/IBM/InformationServer/Server/PXEngine/include /opt/IBM/InformationServer/Server/Projects/EFBIHCL_BASE/RT_BP394.O/V0S115_PXJ_D_CSPR_CALENDAR_ld_dim_Xfm_2.C -o /opt/IBM/InformationServer/Server/Projects/EFBIHCL_BASE/RT_BP394.O/V0S115_PXJ_D_CSPR_CALENDAR_ld_dim_Xfm_2.tmp.o.
##E IIS-DSEE-TCOS-00029 21:30:43(052) <main_program> Creation of a step finished with status = FAILED. (PXJ_D_CSPR_CALENDAR_ld_dim.Xfm_2)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S115_PXJ_D_CSPR_CALENDAR_ld_dim_Xfm_2 transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 in_Xfm_2;
0007: outputname 0 out_D_CSPR_CALENDAR;
0008:
0009: global {
0010: // Job parameter declaration
0011: ustring pBATCH_SID;
0012: }
0013:
0014: initialize {
0015: // define our control variables
0016: int8 RowRejected0;
0017: int8 NullSetVar0;
0018:
0019: // declare our intermediate variables for this section
0020: decimal InterVar0_0;
0021: decimal InterVar0_1;
0022: decimal InterVar0_2;
0023: decimal InterVar0_3;
0024: decimal InterVar0_4;
0025: decimal InterVar0_5;
0026: decimal InterVar0_6;
0027: decimal InterVar0_7;
0028: decimal InterVar0_8;
0029: decimal InterVar0_10;
0030: decimal InterVar0_11;
0031: decimal InterVar0_12;
0032: string InterVar0_15;
0033: ustring InterVar0_16;
0034: ustring InterVar0_17;
0035: ustring InterVar0_18;
0036: ustring InterVar0_19;
0037: ustring InterVar0_20;
0038:
0039: // initialise constant values which require conversion
0040: InterVar0_0 = 01;
0041: InterVar0_1 = 02;
0042: InterVar0_2 = 03;
0043: InterVar0_3 = 04;
0044: InterVar0_4 = 05;
0045: InterVar0_5 = 06;
0046: InterVar0_6 = 07;
0047: InterVar0_7 = 08;
0048: InterVar0_8 = 09;
0049: InterVar0_10 = 10;
0050: InterVar0_11 = 11;
0051: InterVar0_12 = 12;
0052: InterVar0_15 = "0";
0053: InterVar0_16 = 0000;
0054: InterVar0_17 = "Y";
0055: InterVar0_18 = "N";
0056: InterVar0_19 = "CSPR3";
0057: InterVar0_20 = "S";
0058: // Stage variable declaration and initialisation
0059: timestamp StageVar0_vCurDt;
0060: StageVar0_vCurDt = current_timestamp();
0061: string StageVar0_vGfy;
0062: StageVar0_vGfy = "";
0063: }
0064:
0065: mainloop {
0066:
0067: // declare our intermediate variables for this section
0068: string InterVar0_9;
0069: dfloat InterVar0_13;
0070: string InterVar0_14;
0071:
0072: // evaluate the stage variables first
0073: if ((((((((((in_Xfm_2.MO_NBR == InterVar0_0) || (in_Xfm_2.MO_NBR == InterVar0_1)) || (in_Xfm_2.MO_NBR == InterVar0_2)) || (in_Xfm_2.MO_NBR == InterVar0_3)) || (in_Xfm_2.MO_NBR == InterVar0_4)) || (in_Xfm_2.MO_NBR == InterVar0_5)) || (in_Xfm_2.MO_NBR == InterVar0_6)) || (in_Xfm_2.MO_NBR == InterVar0_7)) || (in_Xfm_2.MO_NBR == InterVar0_8))) {
0074: InterVar0_9 = in_Xfm_2.FYEAR;
0075: StageVar0_vGfy = InterVar0_9;
0076: } else {
0077: if ((((in_Xfm_2.MO_NBR == InterVar0_10) || (in_Xfm_2.MO_NBR == InterVar0_11)) || (in_Xfm_2.MO_NBR == InterVar0_12))) {
0078: InterVar0_13 = in_Xfm_2.FYEAR;
0079: InterVar0_14 = InterVar0_13 + 1;
0080: StageVar0_vGfy = InterVar0_14;
0081: } else {
0082: StageVar0_vGfy = InterVar0_15;
0083: }
0084: //;
0085: }
0086: //;
0087:
0088: // initialise the rejected row variable
0089: RowRejected0 = 1;
0090:
0091: // evaluate columns (no constraints) for link: out_D_CSPR_CALENDAR
0092: out_D_CSPR_CALENDAR.SURROGATE_KEY = InterVar0_16;
0093: out_D_CSPR_CALENDAR.ACCT_WEEK_NO = in_Xfm_2.ACCT_WEEK_NO;
0094: out_D_CSPR_CALENDAR.WEEK_IN_YR_NO = in_Xfm_2.WEEK;
0095: out_D_CSPR_CALENDAR.WEEK_IN_MO_NO = in_Xfm_2.WEEK_IN_MO_NO;
0096: out_D_CSPR_CALENDAR.WEND = in_Xfm_2.WEND1;
0097: out_D_CSPR_CALENDAR.MO_NBR = in_Xfm_2.MO_NBR;
0098: out_D_CSPR_CALENDAR.PERIOD = in_Xfm_2.PERIOD;
0099: out_D_CSPR_CALENDAR.MTD_HRS = in_Xfm_2.MTD_HRS;
0100: out_D_CSPR_CALENDAR.GFY = StageVar0_vGfy;
0101: out_D_CSPR_CALENDAR.FQTR = in_Xfm_2.FQTR;
0102: out_D_CSPR_CALENDAR.PRD_WK_IN_MO = in_Xfm_2.PERIOD;
0103: if (((in_Xfm_2.DTL_TBL_DT >= in_Xfm_2.LAST_MO_STR_DT) && (in_Xfm_2.DTL_TBL_DT <= in_Xfm_2.LAST_MO_END_DT))) {
0104: out_D_CSPR_CALENDAR.LAST_MO_FLG = InterVar0_17;
0105: } else {
0106: out_D_CSPR_CALENDAR.LAST_MO_FLG = InterVar0_18;
0107: }
0108: //;
0109: if (((in_Xfm_2.DTL_TBL_DT >= in_Xfm_2.LAST_3_STR_DT) && (in_Xfm_2.DTL_TBL_DT <= in_Xfm_2.LAST_3_END_DT))) {
0110: out_D_CSPR_CALENDAR.LAST_3_FLG = InterVar0_17;
0111: } else {
0112: out_D_CSPR_CALENDAR.LAST_3_FLG = InterVar0_18;
0113: }
0114: //;
0115: if ((in_Xfm_2.DTL_TBL_DT <= in_Xfm_2.LAST_MO_END_DT)) {
0116: out_D_CSPR_CALENDAR.ITD_FLG = InterVar0_17;
0117: } else {
0118: out_D_CSPR_CALENDAR.ITD_FLG = InterVar0_18;
0119: }
0120: //;
0121: out_D_CSPR_CALENDAR.SRC_SYS_ID = InterVar0_19;
0122: out_D_CSPR_CALENDAR.LOAD_ERROR_FLG = InterVar0_18;
0123: out_D_CSPR_CALENDAR.DATA_ORIGIN_CD = InterVar0_20;
0124: out_D_CSPR_CALENDAR.CREATED_EW_DTTM = StageVar0_vCurDt;
0125: out_D_CSPR_CALENDAR.LASTUPD_EW_DTTM = StageVar0_vCurDt;
0126: out_D_CSPR_CALENDAR.BATCH_SK = pBATCH_SID;
0127: writerecord 0;
0128: RowRejected0 = 0;
0129: }
0130:
0131: finish {
0132: }
0133:
*** End of Internal Generated Transformer Code

Please give me solution

Thanks,
Srini

What if MO_NBR is null? Shouldn't you be handling the 'null' scenario before implementing your logic?
Implement a null handling logic before this and you should be able to get through successfully.

Also, are you sure FYEAR is an integer /number ?

This is the compilation error.




when ever n_Xfm_2.MO_NBR= 07 this condition iam getting compilation error

Probably some nested If..Then..Else expressions.

Try this Looks like DataStage is looking for Octal values when you try equating a number padded with a '0' before. Put the non-octal values within double quotes and I'm sure your problem will be solved.

Else IF you can, then just write
This should work too

Since when has 09 been an octal number?

Why do you believe that DataStage is looking for octal numbers? In particular can you cite a manual reference?

iskapalli's main problem is a misplaced belief that Oracle functions can be used directly in DataStage.

I'm not saying 09 is octal. All I'm saying is that 8 and 9 do not appear in the octal system. When we say somethinf like x=08, DataStage seems to look for an octal value. If however, the values are enclosed in quotes or instead of x=08, we use x=8, the problem seems to get solved.

I don't know if there is any official documentation for this because this seems more likely to be a bug. I have however created a test job and implemented a similar scenario before posting the reply. And that's why I am confident it will work

Besides, I think he isn't trying to implement an Oracle function. Rather he's trying to implement the DataStage equivalent of the Oracle Decode function.

That should work too and looks a lot easier on the eye

But I hope I'm right about the Octal part. Would like your feedback, Ray

Not in a position to test that at the moment. Working on a Business Glossary/Information Analyzer/Metadata Workbench installation.

Thanks for ur solution


Regards,
Srini

So... this is resolved?

Wondering if the Modify Stage can handle this logic in a lookup specification ? Or will datatype limitations for this specification play truant ?