concatenation

[iskapalli]

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]

[chulett]

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?

[jwiles]

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,

[jerome_rajan]

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

[ray.wurlod]

Oracle is...
... different

[jwiles]

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,