varchar to char coneversion

[sanjumsm]

Hi,
I have one stranege problem designing a job.

Specification:
Source is a fixed width file(Text). In that one field is P_status is of varchar(1) type and nullable is Y. In target the above mentioned field is char(1) type and not nullable. Requirement is to not to process null in that field to target table. So I made following try....

First, I delete the content of P_status of one row and filled one space.
Then I check for nullability and make reject on Trim(P_status, '' 'A') Or IsNull(P_status).... But strangely it is nopt working for me. Blank value is going to target...... When I calculate length of that field len(Trim(P_status, '' 'A') I got the answer as 1 for the blank field. I trieed every combination of the Trim function available in Dstage 8 but still it is not working for me.....

Any suggestion....

Thanks
Sanjeev

[priyadarshikunal]

Hi,
I have one stranege problem designing a job.

Specification:
Source is a fixed width file(Text). In that one field is P_status is of varchar(1) type and nullable is Y. In target the above mentioned field is char(1) type and not nullable. Requirement is to not to process null in that field to target table. So I made following try....

First, I delete the content of P_status of one row and filled one space.
Then I check for nullability and make reject on Trim(P_status, '' 'A') Or IsNull(P_status).... But strangely it is nopt working for me. Blank value is going to target...... When I calculate length of that field len(Trim(P_status, '' 'A') I got the answer as 1 for the blank field. I trieed every combination of the Trim function available in Dstage 8 but still it is not working for me.....

Any suggestion....

Thanks
Sanjeev

Have you tried finding the ascii value of that character?

Try

Code: Select all

            If(seq(P_status)=32) then SetNull() else trim(P_status) 

Regards,

[sanjumsm]

At compile time it generates error...

[priyadarshikunal]

At compile time it generates error...

reply with the error message.

[sanjumsm]

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 14:51:45(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.0.1.4665
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved



##I IIS-DSEE-TUTL-00031 14:51:45(001) <main_program> The open files limit is 2000; raising to 2147483647.
##I IIS-DSEE-TOSH-00002 14:51:45(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 14:51:45(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 14:51:45(004) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 14:51:45(007) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##W IIS-DSEE-TFTM-00012 14:51:45(009) <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 14:51:45(010) <transform> Error when checking composite operator: Converting number to string.
##W IIS-DSEE-TFEV-00023 14:51:45(011) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "String".
##W IIS-DSEE-TFEV-00025 14:51:45(012) <transform> Error when checking composite operator: Converting number to string.
##W IIS-DSEE-TFEV-00023 14:51:45(013) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "String".
##E IIS-DSEE-TFEV-00026 14:51:45(014) <transform> Error when checking composite operator: Setting null to this non-nullable field: StageVar0_svNullCheckProdStat.
##E IIS-DSEE-TFSR-00019 14:51:45(015) <main_program> Could not check all operators because of previous error(s)
##E IIS-DSEE-TCOS-00029 14:51:45(016) <main_program> Creation of a step finished with status = FAILED. (Trans_GRD_PRODUCT_CHGCPT.xfmProduct)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V2S4_Trans_GRD_PRODUCT_CHGCPT_xfmProduct transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 inxfmProduct;
0007: outputname 0 outxfmProduct;
0008: outputname 1 inErrorStartTimestp;
0009: outputname 2 inErrorNullsUPC;
0010: outputname 3 inErrorNullsProdStatus;
0011:
0012: global {
0013: // Job parameter declaration
0014: string TRANSFORMER_ps_BatchID_pBATCHID;
0015: string DSJobName;
0016: }
0017:
0018: initialize {
0019: // define our row rejected variable
0020: int8 RowRejected0;
0021:
0022: // define our null set variable
0023: int8 NullSetVar0;
0024:
0025: // define and initialise each link row count variable required
0026: uint64 RowCount0_0;
0027: RowCount0_0 = 0;
0028:
0029: // declare our intermediate variables for this section
0030: string InterVar0_0;
0031: string InterVar0_1;
0032: string InterVar0_2;
0033: string InterVar0_3;
0034: string InterVar0_4;
0035: string InterVar0_5;
0036: string InterVar0_6;
0037: string InterVar0_7;
0038: string InterVar0_8;
0039: string InterVar0_11;
0040: string InterVar0_12;
0041: string InterVar0_13;
0042: string InterVar0_14;
0043:
0044: // initialise constant values which require conversion
0045: InterVar0_0 = " ";
0046: InterVar0_1 = "A";
0047: InterVar0_2 = "-";
0048: InterVar0_3 = "00:00:00";
0049: InterVar0_4 = "%yyyy-%mm-%dd %hh:%nn:%ss";
0050: InterVar0_5 = "";
0051: InterVar0_6 = 1;
0052: InterVar0_7 = 0;
0053: InterVar0_8 = "*";
0054: InterVar0_11 = "xfmProductTrans";
0055: InterVar0_12 = "Invalid Date format";
0056: InterVar0_13 = "Nulls in Unique Product Code";
0057: InterVar0_14 = "Nulls in Product Status";
0058: // Stage variable declaration and initialisation
0059: string StageVar0_svTrimedHeader;
0060: StageVar0_svTrimedHeader = "";
0061: string StageVar0_svTimestampCheck;
0062: StageVar0_svTimestampCheck = "";
0063: timestamp StageVar0_svHeaderDateCheck;
0064: StageVar0_svHeaderDateCheck = timestamp_from_string("2001-01-01 00:00:01");
0065: string StageVar0_svNullCheckProdStat;
0066: StageVar0_svNullCheckProdStat = "";
0067: string StageVar0_svNullCheckUPC;
0068: StageVar0_svNullCheckUPC = "";
0069: }
0070:
0071: mainloop {
0072: // initialise our row rejected variable
0073: RowRejected0 = 1;
0074:
0075: // declare our intermediate variables for this section
0076: int64 InterVar0_9;
0077: int64 InterVar0_10;
0078:
0079: // evaluate the stage variables first
0080: StageVar0_svTrimedHeader = trimc_string(inxfmProduct.EFFECTIVE_DATE , InterVar0_0 , InterVar0_1);
0081: StageVar0_svTimestampCheck = ((((((substring_1(StageVar0_svTrimedHeader , 1 , 4) + InterVar0_2) + substring_1(StageVar0_svTrimedHeader , 5 , 2)) + InterVar0_2) + substring_1(StageVar0_svTrimedHeader , 7 , 2)) + InterVar0_0) + InterVar0_3);
0082: StageVar0_svHeaderDateCheck = timestamp_from_string(StageVar0_svTimestampCheck , InterVar0_4);
0083: NullSetVar0 = 0;
0084: if ((num_from_char(inxfmProduct.PROD_STAT) == 32)) {
0085: NullSetVar0 = 1;
0086: } else {
0087: StageVar0_svNullCheckProdStat = trimc_string(inxfmProduct.PROD_STAT);
0088: }
0089: StageVar0_svNullCheckProdStat = (NullSetVar0 == 1) ? set_null() : StageVar0_svNullCheckProdStat;
0090: if (((trimc_string(inxfmProduct.UPC , InterVar0_0 , InterVar0_1) == InterVar0_5) || null(inxfmProduct.UPC))) {
0091: StageVar0_svNullCheckUPC = InterVar0_6;
0092: } else {
0093: StageVar0_svNullCheckUPC = InterVar0_7;
0094: }
0095: //;
0096:
0097: // evaluate constraint and columns for link: outxfmProduct
0098: InterVar0_9 = StageVar0_svNullCheckUPC;
0099: InterVar0_10 = StageVar0_svNullCheckProdStat;
0100: if ((((!((count_substring(string_from_timestamp(StageVar0_svHeaderDateCheck) , InterVar0_8) > 1)) && !(InterVar0_9)) && !(InterVar0_10)) && ((RowCount0_0 + 1) < 1001)))
0101: {
0102: outxfmProduct.UPC = inxfmProduct.UPC;
0103: outxfmProduct.PROD_STATUS = inxfmProduct.PROD_STAT;
0104: outxfmProduct.PROD_ITEM_NUM = inxfmProduct.ITEM_NUM;
0105: outxfmProduct.PREV_ITEM_NUM = inxfmProduct.PREV_ITEM_NUM;
0106: outxfmProduct.BOX_HANG_IND = inxfmProduct.BH_IND;
0107: outxfmProduct.RANGE_CODE = inxfmProduct.RANGE_CODE;
0108: writerecord 0;
0109: RowRejected0 = 0;
0110: RowCount0_0 = RowCount0_0 + 1;
0111: }
0112: // evaluate constraint and columns for link: inErrorStartTimestp
0113: if ((count_substring(string_from_timestamp(StageVar0_svHeaderDateCheck) , InterVar0_8) > 1))
0114: {
0115: inErrorStartTimestp.BATCH_ID = TRANSFORMER_ps_BatchID_pBATCHID;
0116: inErrorStartTimestp.IS_JOB_NAME = DSJobName;
0117: inErrorStartTimestp.IS_JOB_STAGE_NAME = (DSJobName + InterVar0_11);
0118: inErrorStartTimestp.REJECT_RECORD_BUSINESS_KEY = inxfmProduct.UPC;
0119: inErrorStartTimestp.REJECT_REASON = InterVar0_12;
0120: inErrorStartTimestp.REJECT_DATETIME = current_date();
0121: writerecord 1;
0122: RowRejected0 = 0;
0123: }
0124: // evaluate constraint and columns for link: inErrorNullsUPC
0125: InterVar0_9 = StageVar0_svNullCheckUPC;
0126: if (InterVar0_9)
0127: {
0128: inErrorNullsUPC.BATCH_ID = TRANSFORMER_ps_BatchID_pBATCHID;
0129: inErrorNullsUPC.IS_JOB_NAME = DSJobName;
0130: inErrorNullsUPC.IS_JOB_STAGE_NAME = (DSJobName + InterVar0_11);
0131: inErrorNullsUPC.REJECT_RECORD_BUSINESS_KEY = inxfmProduct.UPC;
0132: inErrorNullsUPC.REJECT_REASON = InterVar0_13;
0133: inErrorNullsUPC.REJECT_DATETIME = current_date();
0134: writerecord 2;
0135: RowRejected0 = 0;
0136: }
0137: // evaluate constraint and columns for link: inErrorNullsProdStatus
0138: InterVar0_9 = StageVar0_svNullCheckProdStat;
0139: if (InterVar0_9)
0140: {
0141: inErrorNullsProdStatus.BATCH_ID = TRANSFORMER_ps_BatchID_pBATCHID;
0142: inErrorNullsProdStatus.IS_JOB_NAME = DSJobName;
0143: inErrorNullsProdStatus.IS_JOB_STAGE_NAME = (DSJobName + InterVar0_11);
0144: inErrorNullsProdStatus.REJECT_RECORD_BUSINESS_KEY = inxfmProduct.PROD_STAT;
0145: inErrorNullsProdStatus.REJECT_REASON = InterVar0_14;
0146: inErrorNullsProdStatus.REJECT_DATETIME = current_date();
0147: writerecord 3;
0148: RowRejected0 = 0;
0149: }
0150: }
0151:
0152: finish {
0153: }
0154:
*** End of Internal Generated Transformer Code

[priyadarshikunal]

At compile time it generates error...

reply with the error message.

if you are trying to put that in transformer constraint then put it as
IsNull(P_status) or seq(P_status)=32

[sanjumsm]

Even this does not serve my purpose..... Means same reult I got as previous...

[priyadarshikunal]

At compile time it generates error...

reply with the error message.

if you are trying to put that in transformer constraint then put it as
IsNull(P_status) or seq(P_status)=32

can you find something from that code?


Just analyze this

Code: Select all

##W IIS-DSEE-TFEV-00025 14:51:45(010) <transform> Error when checking composite operator: Converting number to string. 
##W IIS-DSEE-TFEV-00023 14:51:45(011) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "String". 
##W IIS-DSEE-TFEV-00025 14:51:45(012) <transform> Error when checking composite operator: Converting number to string. 
##W IIS-DSEE-TFEV-00023 14:51:45(013) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "String". 
##E IIS-DSEE-TFEV-00026 14:51:45(014) <transform> Error when checking composite operator: Setting null to this non-nullable field: StageVar0_svNullCheckProdStat. 

Code: Select all

0084: if ((num_from_char(inxfmProduct.PROD_STAT) == 32)) { 
0085: NullSetVar0 = 1; 
0086: } else { 
0087: StageVar0_svNullCheckProdStat = trimc_string(inxfmProduct.PROD_STAT); 
0088: } 

You are passing null to a non-nullable result

If you want those records then set it to some default value like empty,
or your own value

else put the constraint to reject that one

also

there are some data type mismatch in your transformer
but probably that will be thrown while running your job.

Regards,

[sanjumsm]

Hi
I am putting the constraints whatever you have suggested in Stage Variable and in downprocessing I am taking Not(StageVaraible) and in reject I am taking That Stagevariable...

[priyadarshikunal]

Hi
I am putting the constraints whatever you have suggested in Stage Variable and in downprocessing I am taking Not(StageVaraible) and in reject I am taking That Stagevariable...

But you have declared that stage variable not nullable

svNullCheckProdStat

Code: Select all

Setting null to this non-nullable field: StageVar0_svNullCheckProdStat.

[priyadarshikunal]

first of all check for ascii value of that character

pass seq(PRODSTAT) to another link and check what result you are getting

[sanjumsm]

Thanks, That worked for me... In stage variable I put if IsNull(Col) or seq(col)=0 then @TRUE Else @FALSE....

In output I took Not(StageVar) and in Reject stagevar...

Regards
Sanjeev :D