Page 2 of 2
Posted: Mon Mar 31, 2008 3:46 pm
by pavans
ray.wurlod wrote:Trim() either in DataStage or in the extraction SQL. ...
I have used the below Function but not able to solve the issue yet.
Let me know if i am wrong.
Trim(LnkTrmPurchaseOrder.OrderNo, ' ', 'R' )
Posted: Mon Mar 31, 2008 4:42 pm
by ray.wurlod
Your Trim() function is wasted while the data type is CHAR because DataStage will pad a char field to the appropriate length. You need the data type to be VARCHAR for the effect of the Trim() function to persist.
Posted: Mon Mar 31, 2008 7:58 pm
by pavans
ray.wurlod wrote:Your Trim() function is wasted while the data type is CHAR because DataStage will pad a char field to the appropriate length. You need the data type to be VARCHAR for the effect of the Trim() functio ...
I am not a Premium Member..........
Can you let me know is there any way to solve this issue.......
Posted: Mon Mar 31, 2008 8:34 pm
by ray.wurlod
Become a premium member. It is not expensive, at less than 30c (Rs12) per day, and you will - among other things - be able to read the entirety of the five premium posters' posts. All revenue from premium memberships is devoted to defraying the hosting and bandwidth costs incurred by DSXchange so, by taking premium membership, you are helping to keep DSXchange alive. Corporate discounts are available for multiple memberships, should be you able to convince your employer to purchase the same (there is a link from the Home page with more information on this).
Posted: Mon Mar 31, 2008 9:34 pm
by pavans
ray.wurlod wrote:Become a premium member. It is not expensive, at less than 30c (Rs12) per day, and you will - among other things - be able to read the entirety of the five premium posters' posts. All revenue from premium memberships is devoted to defraying the hosting and bandwidth costs incurred by DSXchange so, by taking premium membership, you are helping to keep DSXchange alive. Corporate discounts are available for multiple memberships, should be you able to convince your employer to purchase the same (there is a link from the Home page with more information on this).
Thanks for the Info.
I will become Premium Member ASAP.
Posted: Mon Apr 07, 2008 1:14 pm
by pavans
ray.wurlod wrote:Your Trim() function is wasted while the data type is CHAR because DataStage will pad a char field to the appropriate length. You need the data type to be VARCHAR for the effect of the Trim() functio ...
Peek_112,3: sqlcode:1403 ORD_I:5000311 REQ_SHIP_D:2008-03-01 REQ_CAN_D:2008-03-05 PRMS_D:2008-03-19 REQ_DLVR_D:2008-03-20 ORD_D:2008-03-05 SHIP_NODE_T:He MODF_D:2008-03-05 VEND_COMM_METH_T:0 VEND_CNTC_EMAIL_I:
Manohar.Chakravarthy@target.com IS_FORCE_RECV_F: ITEM_DESC_T:abc VEND_STYL_I:13213 TCBI_T:N CMT_D:2008-03-01 OrderNo:5000311 EPRS_C:Target.com DOC_TYPE_T:0005 ORD_TYPE_T:TTR BYR_ORG_C:Target.com SELR_ORG_C:116472 STAT_T:Draft ReqShipDate:03/01/2008 ReqCancelDate:03/05/2008 ReqDeliveryDate:03/20/2008 SRCH_CRTR_1_T:1 CUST_PO_I:12 OrderDate:03/05/2008 ACT_T:CREATE ShipNode:He SHIP_TO_I:WHSE5 RECV_NODE_T:021 CRTE_USER_I:TargetTTR CRTE_USER_N:TagetTTR Modifyts:03/05/2008 ORD_Q: 0000000003.0000 ITEM_ACT_T:MODIFY ITEM_STAT_T:Draft Order Created PSLZ_F:Y RECV_Q: 0000000003.0000 ExtnIsForceRevieved: 0000000000.0000 AMZN_STD_INV_I:Y DPCI_I:Dep-Cl-Item ExtnVendorStyleNo:13213 ExtnTCBI:N CSPK_Q: 0000000012.0000 FORCE_RECV_Q: 0000000001.0000 TOT_RETL_A: 0000000001233.000000 PO_RDY_F:N TRF_1_I:2222 TRF_2_I: TRF_3_I: TRF_4_I: TRF_5_I: ASRT_ITEM_F:N KIT_ITEM_F:Y MIN_ORD_Q: 0000000002.0000 PREP_C:Y VISA_1_T:1233 VISA_2_T: VISA_3_T: VISA_4_T: VISA_5_T: COLR_T:green SIZE_T:45 REQ_DOC_N: REQ_DOC_T: ITEM_I:1 UOM_T:EACH ItemShortDesc:abc UPC_C:123456789125 PRC_UOM_T:EACH UNIT_PRC_A: 000000100.000 RETL_PRC_A: 000000100.000 LINE_TOT_A: 000000100.000 CommittedDate:03/01/2008 ITEM_NOTE_T: ORD_STYP_T:Special HOT_PO_F:N RSV_PO_F:Y RXMT_T:None ITEM_TOT_RETL_A: 000001234.000 ExtnPromiseDate:03/19/2008 SHIP_FROM_I:021 CNTC_EMAIL_I:
rayg@1908studios.com CNTC_TELE_I:3458445 VEND_N:1908 STUDIOS VEND_TRM_T:term EDI860_CPLY_F:Y EDI850_CPLY_F:y GMS_VEND_I: VEND_NOTE_T:This is reservePO VEND_ACTV_F:Y VEND_IMP_F:N PYMT_TYPE_C: FOB_TYPE_C: FOB_CTRY_C:US FOB_ST_C:WS FOB_CITY_N:New York FOB_ZIP_C:23423 FRT_TRM_C:Term VEND_CNTC_N:Manohar VEND_CNTC_ADDR_1_T:33 South VEND_CNTC_ADDR_2_T:Downtown VEND_CNTC_ADDR_3_T: VEND_CNTC_CITY_N:Minneapolis VEND_CNTC_ST_N:Minesotta VEND_CNTC_ZIP_C:4354444444 VEND_CNTC_CTRY_N:mn ExtnVendorContactEmail:
Manohar.Chakravarthy@target.com VEND_CNTC_TELE_I:4343444 LINE_STOT_A: 000000010.000 NOTE_T:notes BILL_TO_FRST_N:Target Corporation BILL_TO_ADDR_1_T:33 South BILL_TO_ADDR_2_T:6th Street BILL_TO_CITY_N:MS CC-2325 BILL_TO_ZIP_C:55402 BILL_TO_ST_N:Minneapolis BILL_TO_CTRY_N:MN SHIP_TO_CO_N:Savannah SHIP_TO_ADDR_1_T:756 Third St. SHIP_TO_ADDR_2_T:ABC SHIP_TO_CITY_N:New York SHIP_TO_ZIP_C:23423 SHIP_TO_ST_N:WS SHIP_TO_CTRY_N:US SHIP_FROM_CO_N:Amazon SHIP_FROM_ADDR_1_T:12 East SHIP_FROM_ADDR_2_T:Block SHIP_FROM_CITY_N:Houston SHIP_FROM_ZIP_C:12321 SHIP_FROM_ST_N:WW SHIP_FROM_CTRY_N:US PRIM_LINE_I:2
Peek_112,3: sqlcode:1403 ORD_I:5000311 REQ_SHIP_D:2008-03-01 REQ_CAN_D:2008-03-05 PRMS_D:2008-03-19 REQ_DLVR_D:2008-03-20 ORD_D:2008-03-05 SHIP_NODE_T:He MODF_D:2008-03-05 VEND_COMM_METH_T:0 VEND_CNTC_EMAIL_I:
Manohar.Chakravarthy@target.com IS_FORCE_RECV_F: ITEM_DESC_T:abc VEND_STYL_I:13213 TCBI_T:N CMT_D:2008-03-01 OrderNo:5000311 EPRS_C:Target.com DOC_TYPE_T:0005 ORD_TYPE_T:TTR BYR_ORG_C:Target.com SELR_ORG_C:116472 STAT_T:Draft ReqShipDate:03/01/2008 ReqCancelDate:03/05/2008 ReqDeliveryDate:03/20/2008 SRCH_CRTR_1_T:1 CUST_PO_I:12 OrderDate:03/05/2008 ACT_T:CREATE ShipNode:He SHIP_TO_I:WHSE5 RECV_NODE_T:021 CRTE_USER_I:TargetTTR CRTE_USER_N:TagetTTR Modifyts:03/05/2008 ORD_Q: 0000000003.0000 ITEM_ACT_T:MODIFY ITEM_STAT_T:Draft Order Created PSLZ_F:Y RECV_Q: 0000000003.0000 ExtnIsForceRevieved: 0000000000.0000 AMZN_STD_INV_I:Y DPCI_I:Dep-Cl-Item ExtnVendorStyleNo:13213 ExtnTCBI:N CSPK_Q: 0000000012.0000 FORCE_RECV_Q: 0000000001.0000 TOT_RETL_A: 0000000001233.000000 PO_RDY_F:N TRF_1_I:2222 TRF_2_I: TRF_3_I: TRF_4_I: TRF_5_I: ASRT_ITEM_F:N KIT_ITEM_F:Y MIN_ORD_Q: 0000000002.0000 PREP_C:Y VISA_1_T:1233 VISA_2_T: VISA_3_T: VISA_4_T: VISA_5_T: COLR_T:green SIZE_T:45 REQ_DOC_N: REQ_DOC_T: ITEM_I:1 UOM_T:EACH ItemShortDesc:abc UPC_C:123456789125 PRC_UOM_T:EACH UNIT_PRC_A: 000000100.000 RETL_PRC_A: 000000100.000 LINE_TOT_A: 000000100.000 CommittedDate:03/01/2008 ITEM_NOTE_T: ORD_STYP_T:Special HOT_PO_F:N RSV_PO_F:Y RXMT_T:None ITEM_TOT_RETL_A: 000001234.000 ExtnPromiseDate:03/19/2008 SHIP_FROM_I:021 CNTC_EMAIL_I:
rayg@1908studios.com CNTC_TELE_I:3458445 VEND_N:1908 STUDIOS VEND_TRM_T:term EDI860_CPLY_F:Y EDI850_CPLY_F:y GMS_VEND_I: VEND_NOTE_T:This is reservePO VEND_ACTV_F:Y VEND_IMP_F:N PYMT_TYPE_C: FOB_TYPE_C: FOB_CTRY_C:US FOB_ST_C:WS FOB_CITY_N:New York FOB_ZIP_C:23423 FRT_TRM_C:Term VEND_CNTC_N:Manohar VEND_CNTC_ADDR_1_T:33 South VEND_CNTC_ADDR_2_T:Downtown VEND_CNTC_ADDR_3_T: VEND_CNTC_CITY_N:Minneapolis VEND_CNTC_ST_N:Minesotta VEND_CNTC_ZIP_C:4354444444 VEND_CNTC_CTRY_N:mn ExtnVendorContactEmail:
Manohar.Chakravarthy@target.com VEND_CNTC_TELE_I:4343444 LINE_STOT_A: 000000010.000 NOTE_T:notes BILL_TO_FRST_N:Target Corporation BILL_TO_ADDR_1_T:33 South BILL_TO_ADDR_2_T:6th Street BILL_TO_CITY_N:MS CC-2325 BILL_TO_ZIP_C:55402 BILL_TO_ST_N:Minneapolis BILL_TO_CTRY_N:MN SHIP_TO_CO_N:Savannah SHIP_TO_ADDR_1_T:756 Third St. SHIP_TO_ADDR_2_T:ABC SHIP_TO_CITY_N:New York SHIP_TO_ZIP_C:23423 SHIP_TO_ST_N:WS SHIP_TO_CTRY_N:US SHIP_FROM_CO_N:Amazon SHIP_FROM_ADDR_1_T:12 East SHIP_FROM_ADDR_2_T:Block SHIP_FROM_CITY_N:Houston SHIP_FROM_ZIP_C:12321 SHIP_FROM_ST_N:WW SHIP_FROM_CTRY_N:US PRIM_LINE_I:1
My ORD_I datatype is defined as Char in Oracle.
its coming as Number from Dataset.
Is there a way to handle this.
I tried Trim function but its not working.
Kindly let me know the possible workaround.
Posted: Mon Apr 07, 2008 1:32 pm
by chulett
Handle
what? As noted, one
cannot trim a CHAR field.
Posted: Mon Apr 07, 2008 2:31 pm
by pavans
chulett wrote:Handle
what? As noted, one
cannot trim a CHAR field.
Is there a work around for this.
Do we need to change the datatype of ORD_I in the Oracle to Varchar?
Posted: Mon Apr 07, 2008 2:52 pm
by chulett
Are you sure you need a 'workaround' for this? It's a character field for a reason, which more than likely means you actually have no issue. If somehow that data now needs to be stored in your database without trailing spaces, then yes you would need to arrange to have the field changed to a VARCHAR2 field.
Posted: Mon Apr 07, 2008 3:05 pm
by pavans
chulett wrote:Are you sure you need a 'workaround' for this? It's a character field for a reason, which more than likely means you actually have no issue. If somehow that data now needs to be stored in your database without trailing spaces, then yes you would need to arrange to have the field changed to a VARCHAR2 field.
-------------------------------------------------------
Say i wanted to store data with trialing spaces without records going to peak stage.i think that would be Great. But can it happen?
Or
If i request my DBA to change the ORD_I field to varchar2, i just wanted to confirm will this solve the issue.
Or all the numeric values coming into CHAR fields needs to be changed to Varchar2 in table.
Let me know your inputs.
Thanks...
Posted: Tue Apr 08, 2008 2:31 pm
by kumar_s
Are you trying to store the integer data with a trailing space?
Or, are you trying to store varchar data with Trailing space?
Posted: Tue Apr 08, 2008 2:57 pm
by pavans
kumar_s wrote:Are you trying to store the integer data with a trailing space?
Or, are you trying to store varchar data with Trailing space? ...
The value is ORD_I = 5000311 which is getting stored in Oracle Table where datatype is Char(40).
For the first time records are getting inserted.
WHen i am running the job second time with the same data records are going to Peak.
I tried to TRIM but it didnt work.
Posted: Tue Apr 08, 2008 3:40 pm
by kumar_s
Does your table getting inserted without any error or warning for the first time?
Any change in the input data for the second time?
Try to clear all the data in the table and do the load twice.
And post error/warning if you get any.