decode function

[iskapalli]

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

[jerome_rajan]

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 ?

[iskapalli]

This is the compilation error.




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

[ray.wurlod]

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

[jerome_rajan]

Try this

Code: Select all

 in_Xfm_2.MO_NBR= "08" or in_Xfm_2.MO_NBR= "09" 

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

Code: Select all

in_Xfm_2.MO_NBR= 8 or in_Xfm_2.MO_NBR= 9 

This should work too

[ray.wurlod]

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.

[jerome_rajan]

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

[jerome_rajan]

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.

[ray.wurlod]

Code: Select all

If in_Xfm_2.MO_NBR >= 1 And in_Xfm_2.MO_NBR <= 9 Then in_Xfm_2.FYEAR Else If in_Xfm_2.MO_NBR >= 10 And in_Xfm_2.MO_NBR  <= 12 Then in_Xfm_2.FYEAR + 1 Else 0

[jerome_rajan]

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

[ray.wurlod]

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

[iskapalli]

Thanks for ur solution


Regards,
Srini

[chulett]

So... this is resolved?

[rameshrr3]

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