DSXchange

Hi,
when i implement the following oracle function in DS Transformer level i am getting the following error message.
the oracle function is ----period||trim(to_char(week_in_mn,'0')) .
here the datatype of period ,week_in_mn is decimal and double respectively.
i have used the following function in transformer

period:trim(char(week_in_mn,'0'))

I got the following compliation error

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 21:05:16(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:05:16(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:05:16(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 21:05:16(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 21:05:16(004) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 21:05:16(007) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##W IIS-DSEE-TFTM-00012 21:05:16(009) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##E IIS-DSEE-TFFN-00008 21:05:16(010) <transform> Error when checking composite operator: The number of arguments must be 1; [line 104,character 67].
##E IIS-DSEE-TFSR-00019 21:05:16(011) <main_program> Could not check all operators because of previous error(s)
##E IIS-DSEE-TCOS-00029 21:05:16(012) <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_8;
0028: decimal InterVar0_9;
0029: decimal InterVar0_10;
0030: string InterVar0_13;
0031: ustring InterVar0_14;
0032: uint8 InterVar0_17;
0033: ustring InterVar0_18;
0034: ustring InterVar0_19;
0035: ustring InterVar0_20;
0036: ustring InterVar0_21;
0037:
0038: // initialise constant values which require conversion
0039: InterVar0_0 = 01;
0040: InterVar0_1 = 02;
0041: InterVar0_2 = 03;
0042: InterVar0_3 = 04;
0043: InterVar0_4 = 05;
0044: InterVar0_5 = 06;
0045: InterVar0_6 = 07;
0046: InterVar0_8 = 10;
0047: InterVar0_9 = 11;
0048: InterVar0_10 = 12;
0049: InterVar0_13 = "0";
0050: InterVar0_14 = 0000;
0051: InterVar0_17 = "0";
0052: InterVar0_18 = "Y";
0053: InterVar0_19 = "N";
0054: InterVar0_20 = "CSPR3";
0055: InterVar0_21 = "S";
0056: // Stage variable declaration and initialisation
0057: timestamp StageVar0_vCurDt;
0058: StageVar0_vCurDt = current_timestamp();
0059: string StageVar0_vGfy;
0060: StageVar0_vGfy = "";
0061: }
0062:
0063: mainloop {
0064:
0065: // declare our intermediate variables for this section
0066: string InterVar0_7;
0067: dfloat InterVar0_11;
0068: string InterVar0_12;
0069: ustring InterVar0_15;
0070: int32 InterVar0_16;
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))) {
0074: InterVar0_7 = in_Xfm_2.FYEAR;
0075: StageVar0_vGfy = InterVar0_7;
0076: } else {
0077: if ((((in_Xfm_2.MO_NBR == InterVar0_8) || (in_Xfm_2.MO_NBR == InterVar0_9)) || (in_Xfm_2.MO_NBR == InterVar0_10))) {
0078: InterVar0_11 = in_Xfm_2.FYEAR;
0079: InterVar0_12 = InterVar0_11 + 1;
0080: StageVar0_vGfy = InterVar0_12;
0081: } else {
0082: StageVar0_vGfy = InterVar0_13;
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_14;
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: InterVar0_15 = in_Xfm_2.PERIOD;
0103: InterVar0_16 = in_Xfm_2.WEEK_IN_MO_NO;
0104: out_D_CSPR_CALENDAR.PRD_WK_IN_MO = (InterVar0_15 + u_trimc_string(u_char_from_num(InterVar0_16 , InterVar0_17)));
0105: 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))) {
0106: out_D_CSPR_CALENDAR.LAST_MO_FLG = InterVar0_18;
0107: } else {
0108: out_D_CSPR_CALENDAR.LAST_MO_FLG = InterVar0_19;
0109: }
0110: //;
0111: 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))) {
0112: out_D_CSPR_CALENDAR.LAST_3_FLG = InterVar0_18;
0113: } else {
0114: out_D_CSPR_CALENDAR.LAST_3_FLG = InterVar0_19;
0115: }
0116: //;
0117: if ((in_Xfm_2.DTL_TBL_DT <= in_Xfm_2.LAST_MO_END_DT)) {
0118: out_D_CSPR_CALENDAR.ITD_FLG = InterVar0_18;
0119: } else {
0120: out_D_CSPR_CALENDAR.ITD_FLG = InterVar0_19;
0121: }
0122: //;
0123: out_D_CSPR_CALENDAR.SRC_SYS_ID = InterVar0_20;
0124: out_D_CSPR_CALENDAR.LOAD_ERROR_FLG = InterVar0_19;
0125: out_D_CSPR_CALENDAR.DATA_ORIGIN_CD = InterVar0_21;
0126: out_D_CSPR_CALENDAR.CREATED_EW_DTTM = StageVar0_vCurDt;
0127: out_D_CSPR_CALENDAR.LASTUPD_EW_DTTM = StageVar0_vCurDt;
0128: out_D_CSPR_CALENDAR.BATCH_SK = pBATCH_SID;
0129: writerecord 0;
0130: RowRejected0 = 0;
0131: }
0132:
0133: finish {
0134: }
0135:
*** End of Internal Generated Transformer Code

please give me a solution

Thanks,
srini[/quote]

iskapalli wrote:when i implement the following oracle function in DS Transformer level i am getting the following error message.

Sorry but why would you think you could use an Oracle function in a Transformer? Look up the equivalent DataStage function and use that. Or are you saying you are attempting to do that?

The DataStage function char() allows only one argument, but you have two arguments in your logic. I bet this derivation didn't pass validation within the GUI, so it certainly wouldn't pass validation during compilation!

As Craig suggests, you need the appropriate DataStage function/logic for the Oracle to_char() function. Do you understand what to_char() is doing within the Oracle logic?

Regards,

Just FYI.. The CHAR function in DataStage returns an ASCII character from its numeric code value. Is this what you want to do?

Oracle is...
... different

Do you understand what the to_char() function in Oracle does? Google it if you don't, then keep in mind that there are many datatype conversions that DataStage handles intrinsically (automatically).

Regards,