Records going to peek stage........

pavans · Post by **pavans** » Thu Mar 27, 2008 12:02 pm

Hi

I have a job design:

Dataset--RemoveDuplicate---Transformer---OracleTable---Peek.

Dataset has 12 records.
When i first ran the job, all the records got inserted to the table.
when i ran the same job again, to check the update the records are going to peek.
Table has upsert logic.
sqlcode:1403 is the error i m getting.
Can someone look into this.

Thanks

Minhajuddin · Post by **Minhajuddin** » Thu Mar 27, 2008 12:06 pm

Have you googled for the return code. I just did and got a lot of answers http://www.google.co.in/search?q=ora-01 ... =firefox-a Isn't that easier than posting this whole thing

pavans · Post by **pavans** » Thu Mar 27, 2008 1:00 pm

Minhajuddin wrote:Have you googled for the return code. I just did and got a lot of answers http://www.google.co.in/search?q=ora-01 ... =firefox-a Isn't that easier than posting this whole thing

Hi
Googled......and tried to solve but unable to resolve the issue.
I am running the job twice. first to check the inserts and next to check the updates.
But the records are going to peek the second time.
The table has two keys.
Let me know if i am missing any.

kumar_s · Post by **kumar_s** » Thu Mar 27, 2008 3:30 pm

Records are going to Peek the second time, because probably it has the same select query as it did the first time with the same table with same data. Do you have any where class to the Peek stage?

Minhajuddin · Post by **Minhajuddin** » Fri Mar 28, 2008 3:38 am

You get this error when the "WHERE" clause in the update statement evaluates to false. Checkout your "WHERE" clause. Try it out in SQL plus and see what you get. Do you have a condition which is always false?(like 1=2)

pavans · Post by **pavans** » Fri Mar 28, 2008 11:14 am

Minhajuddin wrote:You get this error when the "WHERE" clause in the update statement evaluates to false. Checkout your "WHERE" clause. Try it out in SQL plus and see what you get. Do you have a condition which is always false?(like 1=2)

Still having the same problem.

INSERT
INTO
PO_W
(ORD_I, PRIM_LINE_I, EPRS_C, DOC_TYPE_T, ORD_TYPE_T, ORD_STYP_T, BYR_ORG_C, SELR_ORG_C, HOT_PO_F, RSV_PO_F, RXMT_T, STAT_T, REQ_SHIP_D, REQ_CAN_D, PRMS_D, REQ_DLVR_D, SRCH_CRTR_1_T, CUST_PO_I, ORD_D, ACT_T, SHIP_NODE_T, SHIP_TO_I, RECV_NODE_T, NOTE_T, CRTE_USER_I, CRTE_USER_N, MODF_D, VEND_N, CNTC_TELE_I, VEND_TRM_T, VEND_COMM_METH_T, GMS_VEND_I, VEND_NOTE_T, VEND_ACTV_F, VEND_IMP_F, FOB_TYPE_C, PYMT_TYPE_C, FOB_CTRY_C, FOB_ST_C, FOB_CITY_N, FRT_TRM_C, FOB_ZIP_C, VEND_CNTC_N, VEND_CNTC_ADDR_1_T, VEND_CNTC_ADDR_2_T, VEND_CNTC_ADDR_3_T, VEND_CNTC_CITY_N, VEND_CNTC_ST_N, VEND_CNTC_ZIP_C, VEND_CNTC_CTRY_N, CNTC_EMAIL_I, VEND_CNTC_EMAIL_I, VEND_CNTC_TELE_I, BILL_TO_FRST_N, BILL_TO_ADDR_1_T, BILL_TO_CITY_N, BILL_TO_ST_N, BILL_TO_ZIP_C, BILL_TO_CTRY_N, SHIP_TO_CO_N, SHIP_TO_ADDR_1_T, SHIP_TO_CITY_N, SHIP_TO_ST_N, SHIP_TO_ZIP_C, SHIP_TO_CTRY_N, SHIP_FROM_CO_N, SHIP_FROM_ADDR_1_T, SHIP_FROM_CITY_N, SHIP_FROM_ST_N, SHIP_FROM_ZIP_C, SHIP_FROM_CTRY_N, IS_FORCE_RECV_F, ITEM_I, ITEM_DESC_T, UOM_T, AMZN_STD_INV_I, DPCI_I, VEND_STYL_I, UPC_C, TCBI_T, CSPK_Q, ORD_Q, RECV_Q, FORCE_RECV_Q, PO_RDY_F, TRF_1_I, TRF_2_I, TRF_3_I, TRF_4_I, TRF_5_I, ASRT_ITEM_F, KIT_ITEM_F, ITEM_NOTE_T, MIN_ORD_Q, PREP_C, PSLZ_F, CMT_D, ITEM_STAT_T, ITEM_ACT_T, BILL_TO_ADDR_2_T, SHIP_TO_ADDR_2_T, SHIP_FROM_ADDR_2_T, TOT_RETL_A, REQ_DOC_N, REQ_DOC_T, VISA_1_T, VISA_2_T, VISA_3_T, VISA_4_T, VISA_5_T, COLR_T, SIZE_T, LINE_STOT_A, LINE_TOT_A, UNIT_PRC_A, PRC_UOM_T, RETL_PRC_A, ITEM_TOT_RETL_A, SHIP_FROM_I, EDI850_CPLY_F, EDI860_CPLY_F)
VALUES
(ORCHESTRATE.ORD_I, ORCHESTRATE.PRIM_LINE_I, ORCHESTRATE.EPRS_C, ORCHESTRATE.DOC_TYPE_T, ORCHESTRATE.ORD_TYPE_T, ORCHESTRATE.ORD_STYP_T, ORCHESTRATE.BYR_ORG_C, ORCHESTRATE.SELR_ORG_C, ORCHESTRATE.HOT_PO_F, ORCHESTRATE.RSV_PO_F, ORCHESTRATE.RXMT_T, ORCHESTRATE.STAT_T, ORCHESTRATE.REQ_SHIP_D, ORCHESTRATE.REQ_CAN_D, ORCHESTRATE.PRMS_D, ORCHESTRATE.REQ_DLVR_D, ORCHESTRATE.SRCH_CRTR_1_T, ORCHESTRATE.CUST_PO_I, ORCHESTRATE.ORD_D, ORCHESTRATE.ACT_T, ORCHESTRATE.SHIP_NODE_T, ORCHESTRATE.SHIP_TO_I, ORCHESTRATE.RECV_NODE_T, ORCHESTRATE.NOTE_T, ORCHESTRATE.CRTE_USER_I, ORCHESTRATE.CRTE_USER_N, ORCHESTRATE.MODF_D, ORCHESTRATE.VEND_N, ORCHESTRATE.CNTC_TELE_I, ORCHESTRATE.VEND_TRM_T, ORCHESTRATE.VEND_COMM_METH_T, ORCHESTRATE.GMS_VEND_I, ORCHESTRATE.VEND_NOTE_T, ORCHESTRATE.VEND_ACTV_F, ORCHESTRATE.VEND_IMP_F, ORCHESTRATE.FOB_TYPE_C, ORCHESTRATE.PYMT_TYPE_C, ORCHESTRATE.FOB_CTRY_C, ORCHESTRATE.FOB_ST_C, ORCHESTRATE.FOB_CITY_N, ORCHESTRATE.FRT_TRM_C, ORCHESTRATE.FOB_ZIP_C, ORCHESTRATE.VEND_CNTC_N, ORCHESTRATE.VEND_CNTC_ADDR_1_T, ORCHESTRATE.VEND_CNTC_ADDR_2_T, ORCHESTRATE.VEND_CNTC_ADDR_3_T, ORCHESTRATE.VEND_CNTC_CITY_N, ORCHESTRATE.VEND_CNTC_ST_N, ORCHESTRATE.VEND_CNTC_ZIP_C, ORCHESTRATE.VEND_CNTC_CTRY_N, ORCHESTRATE.CNTC_EMAIL_I, ORCHESTRATE.VEND_CNTC_EMAIL_I, ORCHESTRATE.VEND_CNTC_TELE_I, ORCHESTRATE.BILL_TO_FRST_N, ORCHESTRATE.BILL_TO_ADDR_1_T, ORCHESTRATE.BILL_TO_CITY_N, ORCHESTRATE.BILL_TO_ST_N, ORCHESTRATE.BILL_TO_ZIP_C, ORCHESTRATE.BILL_TO_CTRY_N, ORCHESTRATE.SHIP_TO_CO_N, ORCHESTRATE.SHIP_TO_ADDR_1_T, ORCHESTRATE.SHIP_TO_CITY_N, ORCHESTRATE.SHIP_TO_ST_N, ORCHESTRATE.SHIP_TO_ZIP_C, ORCHESTRATE.SHIP_TO_CTRY_N, ORCHESTRATE.SHIP_FROM_CO_N, ORCHESTRATE.SHIP_FROM_ADDR_1_T, ORCHESTRATE.SHIP_FROM_CITY_N, ORCHESTRATE.SHIP_FROM_ST_N, ORCHESTRATE.SHIP_FROM_ZIP_C, ORCHESTRATE.SHIP_FROM_CTRY_N, ORCHESTRATE.IS_FORCE_RECV_F, ORCHESTRATE.ITEM_I, ORCHESTRATE.ITEM_DESC_T, ORCHESTRATE.UOM_T, ORCHESTRATE.AMZN_STD_INV_I, ORCHESTRATE.DPCI_I, ORCHESTRATE.VEND_STYL_I, ORCHESTRATE.UPC_C, ORCHESTRATE.TCBI_T, ORCHESTRATE.CSPK_Q, ORCHESTRATE.ORD_Q, ORCHESTRATE.RECV_Q, ORCHESTRATE.FORCE_RECV_Q, ORCHESTRATE.PO_RDY_F, ORCHESTRATE.TRF_1_I, ORCHESTRATE.TRF_2_I, ORCHESTRATE.TRF_3_I, ORCHESTRATE.TRF_4_I, ORCHESTRATE.TRF_5_I, ORCHESTRATE.ASRT_ITEM_F, ORCHESTRATE.KIT_ITEM_F, ORCHESTRATE.ITEM_NOTE_T, ORCHESTRATE.MIN_ORD_Q, ORCHESTRATE.PREP_C, ORCHESTRATE.PSLZ_F, ORCHESTRATE.CMT_D, ORCHESTRATE.ITEM_STAT_T, ORCHESTRATE.ITEM_ACT_T, ORCHESTRATE.BILL_TO_ADDR_2_T, ORCHESTRATE.SHIP_TO_ADDR_2_T, ORCHESTRATE.SHIP_FROM_ADDR_2_T, ORCHESTRATE.TOT_RETL_A, ORCHESTRATE.REQ_DOC_N, ORCHESTRATE.REQ_DOC_T, ORCHESTRATE.VISA_1_T, ORCHESTRATE.VISA_2_T, ORCHESTRATE.VISA_3_T, ORCHESTRATE.VISA_4_T, ORCHESTRATE.VISA_5_T, ORCHESTRATE.COLR_T, ORCHESTRATE.SIZE_T, ORCHESTRATE.LINE_STOT_A, ORCHESTRATE.LINE_TOT_A, ORCHESTRATE.UNIT_PRC_A, ORCHESTRATE.PRC_UOM_T, ORCHESTRATE.RETL_PRC_A, ORCHESTRATE.ITEM_TOT_RETL_A, ORCHESTRATE.SHIP_FROM_I, ORCHESTRATE.EDI850_CPLY_F, ORCHESTRATE.EDI860_CPLY_F)

UPDATE
PO_W
SET
EPRS_C = ORCHESTRATE.EPRS_C, DOC_TYPE_T = ORCHESTRATE.DOC_TYPE_T, ORD_TYPE_T = ORCHESTRATE.ORD_TYPE_T, ORD_STYP_T = ORCHESTRATE.ORD_STYP_T, BYR_ORG_C = ORCHESTRATE.BYR_ORG_C, SELR_ORG_C = ORCHESTRATE.SELR_ORG_C, HOT_PO_F = ORCHESTRATE.HOT_PO_F, RSV_PO_F = ORCHESTRATE.RSV_PO_F, RXMT_T = ORCHESTRATE.RXMT_T, STAT_T = ORCHESTRATE.STAT_T, REQ_SHIP_D = ORCHESTRATE.REQ_SHIP_D, REQ_CAN_D = ORCHESTRATE.REQ_CAN_D, PRMS_D = ORCHESTRATE.PRMS_D, REQ_DLVR_D = ORCHESTRATE.REQ_DLVR_D, SRCH_CRTR_1_T = ORCHESTRATE.SRCH_CRTR_1_T, CUST_PO_I = ORCHESTRATE.CUST_PO_I, ORD_D = ORCHESTRATE.ORD_D, ACT_T = ORCHESTRATE.ACT_T, SHIP_NODE_T = ORCHESTRATE.SHIP_NODE_T, SHIP_TO_I = ORCHESTRATE.SHIP_TO_I, RECV_NODE_T = ORCHESTRATE.RECV_NODE_T, NOTE_T = ORCHESTRATE.NOTE_T, CRTE_USER_I = ORCHESTRATE.CRTE_USER_I, CRTE_USER_N = ORCHESTRATE.CRTE_USER_N, MODF_D = ORCHESTRATE.MODF_D, VEND_N = ORCHESTRATE.VEND_N, CNTC_TELE_I = ORCHESTRATE.CNTC_TELE_I, VEND_TRM_T = ORCHESTRATE.VEND_TRM_T, VEND_COMM_METH_T = ORCHESTRATE.VEND_COMM_METH_T, GMS_VEND_I = ORCHESTRATE.GMS_VEND_I, VEND_NOTE_T = ORCHESTRATE.VEND_NOTE_T, VEND_ACTV_F = ORCHESTRATE.VEND_ACTV_F, VEND_IMP_F = ORCHESTRATE.VEND_IMP_F, FOB_TYPE_C = ORCHESTRATE.FOB_TYPE_C, PYMT_TYPE_C = ORCHESTRATE.PYMT_TYPE_C, FOB_CTRY_C = ORCHESTRATE.FOB_CTRY_C, FOB_ST_C = ORCHESTRATE.FOB_ST_C, FOB_CITY_N = ORCHESTRATE.FOB_CITY_N, FRT_TRM_C = ORCHESTRATE.FRT_TRM_C, FOB_ZIP_C = ORCHESTRATE.FOB_ZIP_C, VEND_CNTC_N = ORCHESTRATE.VEND_CNTC_N, VEND_CNTC_ADDR_1_T = ORCHESTRATE.VEND_CNTC_ADDR_1_T, VEND_CNTC_ADDR_2_T = ORCHESTRATE.VEND_CNTC_ADDR_2_T, VEND_CNTC_ADDR_3_T = ORCHESTRATE.VEND_CNTC_ADDR_3_T, VEND_CNTC_CITY_N = ORCHESTRATE.VEND_CNTC_CITY_N, VEND_CNTC_ST_N = ORCHESTRATE.VEND_CNTC_ST_N, VEND_CNTC_ZIP_C = ORCHESTRATE.VEND_CNTC_ZIP_C, VEND_CNTC_CTRY_N = ORCHESTRATE.VEND_CNTC_CTRY_N, CNTC_EMAIL_I = ORCHESTRATE.CNTC_EMAIL_I, VEND_CNTC_EMAIL_I = ORCHESTRATE.VEND_CNTC_EMAIL_I, VEND_CNTC_TELE_I = ORCHESTRATE.VEND_CNTC_TELE_I, BILL_TO_FRST_N = ORCHESTRATE.BILL_TO_FRST_N, BILL_TO_ADDR_1_T = ORCHESTRATE.BILL_TO_ADDR_1_T, BILL_TO_CITY_N = ORCHESTRATE.BILL_TO_CITY_N, BILL_TO_ST_N = ORCHESTRATE.BILL_TO_ST_N, BILL_TO_ZIP_C = ORCHESTRATE.BILL_TO_ZIP_C, BILL_TO_CTRY_N = ORCHESTRATE.BILL_TO_CTRY_N, SHIP_TO_CO_N = ORCHESTRATE.SHIP_TO_CO_N, SHIP_TO_ADDR_1_T = ORCHESTRATE.SHIP_TO_ADDR_1_T, SHIP_TO_CITY_N = ORCHESTRATE.SHIP_TO_CITY_N, SHIP_TO_ST_N = ORCHESTRATE.SHIP_TO_ST_N, SHIP_TO_ZIP_C = ORCHESTRATE.SHIP_TO_ZIP_C, SHIP_TO_CTRY_N = ORCHESTRATE.SHIP_TO_CTRY_N, SHIP_FROM_CO_N = ORCHESTRATE.SHIP_FROM_CO_N, SHIP_FROM_ADDR_1_T = ORCHESTRATE.SHIP_FROM_ADDR_1_T, SHIP_FROM_CITY_N = ORCHESTRATE.SHIP_FROM_CITY_N, SHIP_FROM_ST_N = ORCHESTRATE.SHIP_FROM_ST_N, SHIP_FROM_ZIP_C = ORCHESTRATE.SHIP_FROM_ZIP_C, SHIP_FROM_CTRY_N = ORCHESTRATE.SHIP_FROM_CTRY_N, IS_FORCE_RECV_F = ORCHESTRATE.IS_FORCE_RECV_F, ITEM_I = ORCHESTRATE.ITEM_I, ITEM_DESC_T = ORCHESTRATE.ITEM_DESC_T, UOM_T = ORCHESTRATE.UOM_T, AMZN_STD_INV_I = ORCHESTRATE.AMZN_STD_INV_I, DPCI_I = ORCHESTRATE.DPCI_I, VEND_STYL_I = ORCHESTRATE.VEND_STYL_I, UPC_C = ORCHESTRATE.UPC_C, TCBI_T = ORCHESTRATE.TCBI_T, CSPK_Q = ORCHESTRATE.CSPK_Q, ORD_Q = ORCHESTRATE.ORD_Q, RECV_Q = ORCHESTRATE.RECV_Q, FORCE_RECV_Q = ORCHESTRATE.FORCE_RECV_Q, PO_RDY_F = ORCHESTRATE.PO_RDY_F, TRF_1_I = ORCHESTRATE.TRF_1_I, TRF_2_I = ORCHESTRATE.TRF_2_I, TRF_3_I = ORCHESTRATE.TRF_3_I, TRF_4_I = ORCHESTRATE.TRF_4_I, TRF_5_I = ORCHESTRATE.TRF_5_I, ASRT_ITEM_F = ORCHESTRATE.ASRT_ITEM_F, KIT_ITEM_F = ORCHESTRATE.KIT_ITEM_F, ITEM_NOTE_T = ORCHESTRATE.ITEM_NOTE_T, MIN_ORD_Q = ORCHESTRATE.MIN_ORD_Q, PREP_C = ORCHESTRATE.PREP_C, PSLZ_F = ORCHESTRATE.PSLZ_F, CMT_D = ORCHESTRATE.CMT_D, ITEM_STAT_T = ORCHESTRATE.ITEM_STAT_T, ITEM_ACT_T = ORCHESTRATE.ITEM_ACT_T, BILL_TO_ADDR_2_T = ORCHESTRATE.BILL_TO_ADDR_2_T, SHIP_TO_ADDR_2_T = ORCHESTRATE.SHIP_TO_ADDR_2_T, SHIP_FROM_ADDR_2_T = ORCHESTRATE.SHIP_FROM_ADDR_2_T, TOT_RETL_A = ORCHESTRATE.TOT_RETL_A, REQ_DOC_N = ORCHESTRATE.REQ_DOC_N, REQ_DOC_T = ORCHESTRATE.REQ_DOC_T, VISA_1_T = ORCHESTRATE.VISA_1_T, VISA_2_T = ORCHESTRATE.VISA_2_T, VISA_3_T = ORCHESTRATE.VISA_3_T, VISA_4_T = ORCHESTRATE.VISA_4_T, VISA_5_T = ORCHESTRATE.VISA_5_T, COLR_T = ORCHESTRATE.COLR_T, SIZE_T = ORCHESTRATE.SIZE_T, LINE_STOT_A = ORCHESTRATE.LINE_STOT_A, LINE_TOT_A = ORCHESTRATE.LINE_TOT_A, UNIT_PRC_A = ORCHESTRATE.UNIT_PRC_A, PRC_UOM_T = ORCHESTRATE.PRC_UOM_T, RETL_PRC_A = ORCHESTRATE.RETL_PRC_A, ITEM_TOT_RETL_A = ORCHESTRATE.ITEM_TOT_RETL_A, SHIP_FROM_I = ORCHESTRATE.SHIP_FROM_I, EDI850_CPLY_F = ORCHESTRATE.EDI850_CPLY_F, EDI860_CPLY_F = ORCHESTRATE.EDI860_CPLY_F
WHERE
(ORD_I = ORCHESTRATE.ORD_I AND PRIM_LINE_I = ORCHESTRATE.PRIM_LINE_I)

These are my insert and update queries.
Autogenerated queries.
One of the records from peek:
PeekPurchaseOrderWErrors,1: sqlcode:1403 PRIM_LINE_I:1 REQ_SHIP_D:2008-09-01 REQ_CAN_D:2008-09-05 PRMS_D:2008-03-19 REQ_DLVR_D:2008-09-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: 0000013213. TCBI_T:N CMT_D:2008-09-01 ORD_I: 0009000311. PrimeLineNo: 00001. EPRS_C:Target.com DOC_TYPE_T:Purchase Order ORD_TYPE_T:TTR BYR_ORG_C:Target.com SELR_ORG_C: 0000116472. STAT_T:Draft ReqShipDate:09/01/2008 ReqCancelDate:09/05/2008 ReqDeliveryDate:09/20/2008 SRCH_CRTR_1_T: 0000000001. CUST_PO_I: 0000000012. OrderDate:03/05/2008 ACT_T:CREATE ShipNode:He SHIP_TO_I:WHSE_5 RECV_NODE_T: 000000002 CRTE_USER_I:TargetTTR CRTE_USER_N:TagetTTR Modifyts:03/05/2008 ORD_Q: 0000000003.0000 ITEM_ACT_T:CREATE ITEM_STAT_T:Draft Order Created PSLZ_F:Y RECV_Q: 0000000003.0000 ExtnIsForceRevieved: 0000000000.0000 AMZN_STD_INV_I:Y DPCI_I:N ExtnVendorStyleNo: 0000013213. ExtnTCBI:N CSPK_Q: 0000000012.0000 FORCE_RECV_Q: 0000000001.0000 TOT_RETL_A: 0000000001233.000000 PO_RDY_F:N TRF_1_I: 0000002222. 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: 0000001233. VISA_2_T: VISA_3_T: VISA_4_T: VISA_5_T: COLR_T:green SIZE_T: 0000000045. REQ_DOC_N: REQ_DOC_T: ITEM_I: 0000000001. UOM_T:EACH ItemShortDesc:abc UPC_C:1.23457e+11 PRC_UOM_T:EACH UNIT_PRC_A: 000000100.000 RETL_PRC_A: 000000100.000 LINE_TOT_A: 000000100.000 CommittedDate:09/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: 0000000021.0000 CNTC_EMAIL_I:rayg@1908studios.com CNTC_TELE_I: 0003458445.0000 VEND_N:1908 STUDIOS VEND_TRM_T:term EDI860_CPLY_F:Y EDI850_CPLY_F:N GMS_VEND_I: 0000000012.0000 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: 0000023423.0000 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:4.35444e+09 VEND_CNTC_CTRY_N:mn ExtnVendorContactEmail:Manohar.Chakravarthy@target.com VEND_CNTC_TELE_I: 0004343444. 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: 0000055402. 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: 0000023423.0000 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: 0000012321. SHIP_FROM_ST_N:WW SHIP_FROM_CTRY_N:US.

Let me know if find anything from the above.

Thanks,
Pavan

Minhajuddin · Post by **Minhajuddin** » Sun Mar 30, 2008 3:04 am

Looks like you are having problems with the datatypes

What is the datatype of ORD_I and PRIM_LINE_I?

Try running the following queries and see if it returns any rows:

SELECT * FROM P_OW
WHERE
(ORD_I = '0009000311' AND PRIM_LINE_I = '1')

SELECT * FROM P_OW
WHERE
(ORD_I = '0009000311.' AND PRIM_LINE_I = '1')

What do you see

pavans · Post by **pavans** » Sun Mar 30, 2008 3:39 pm

Minhajuddin wrote:Looks like you are having problems with the datatypes

What is the datatype of ORD_I and PRIM_LINE_I?

Try running the following queries and see if it returns any rows:

SELECT * FROM P_OW
WHERE
(ORD_I = '0009000311' AND PRIM_LINE_I = '1')

SELECT * FROM P_OW
WHERE
(ORD_I = '0009000311.' AND PRIM_LINE_I = '1')

What do you see

In the Table :
ord_i char(40)
Prim_line_i Number(5)

I have truncated and loaded the table again.
I made all datatypes as it is with the table. except date columns which are of type varchar. I used StringtoDate conversion.
Again the data is going to Peek when run.

I am copying the table:

Name Null? Type
EPRS_C VARCHAR2(24)
DOC_TYPE_T VARCHAR2(40)
ORD_TYPE_T VARCHAR2(20)
ORD_STYP_T VARCHAR2(25)
BYR_ORG_C CHAR(24)
SELR_ORG_C CHAR(24)
ORD_I NOT NULL CHAR(40)
HOT_PO_F CHAR(1)
RSV_PO_F CHAR(1)
RXMT_T VARCHAR2(25)
STAT_T VARCHAR2(25)
REQ_SHIP_D DATE
REQ_CAN_D DATE
PRMS_D DATE
REQ_DLVR_D DATE
SRCH_CRTR_1_T VARCHAR2(100)
CUST_PO_I VARCHAR2(100)
ORD_D DATE
ACT_T VARCHAR2(15)
SHIP_NODE_T VARCHAR2(24)
SHIP_TO_I VARCHAR2(40)
RECV_NODE_T VARCHAR2(24)
NOTE_T VARCHAR2(2000)
CRTE_USER_I VARCHAR2(40)
CRTE_USER_N VARCHAR2(40)
MODF_D DATE
VEND_N VARCHAR2(25)
CNTC_TELE_I CHAR(40)
VEND_TRM_T VARCHAR2(100)
VEND_COMM_METH_T VARCHAR2(24)
GMS_VEND_I VARCHAR2(24)
VEND_NOTE_T VARCHAR2(100)
VEND_ACTV_F CHAR(1)
VEND_IMP_F CHAR(1)
FOB_TYPE_C CHAR(40)
PYMT_TYPE_C CHAR(40)
FOB_CTRY_C VARCHAR2(15)
FOB_ST_C VARCHAR2(15)
FOB_CITY_N CHAR(35)
FRT_TRM_C CHAR(24)
FOB_ZIP_C VARCHAR2(35)
VEND_CNTC_N VARCHAR2(40)
VEND_CNTC_ADDR_1_T VARCHAR2(70)
VEND_CNTC_ADDR_2_T VARCHAR2(70)
VEND_CNTC_ADDR_3_T VARCHAR2(70)
VEND_CNTC_CITY_N CHAR(35)
VEND_CNTC_ST_N CHAR(35)
VEND_CNTC_ZIP_C VARCHAR2(35)
VEND_CNTC_CTRY_N VARCHAR2(40)
CNTC_EMAIL_I VARCHAR2(40)
VEND_CNTC_EMAIL_I VARCHAR2(150)
VEND_CNTC_TELE_I CHAR(40)
BILL_TO_FRST_N VARCHAR2(40)
BILL_TO_ADDR_1_T VARCHAR2(70)
BILL_TO_CITY_N CHAR(35)
BILL_TO_ST_N CHAR(35)
BILL_TO_ZIP_C VARCHAR2(35)
BILL_TO_CTRY_N VARCHAR2(40)
SHIP_TO_CO_N VARCHAR2(40)
SHIP_TO_ADDR_1_T VARCHAR2(70)
SHIP_TO_CITY_N CHAR(35)
SHIP_TO_ST_N CHAR(35)
SHIP_TO_ZIP_C VARCHAR2(35)
SHIP_TO_CTRY_N VARCHAR2(40)
SHIP_FROM_CO_N VARCHAR2(40)
SHIP_FROM_ADDR_1_T VARCHAR2(70)
SHIP_FROM_CITY_N CHAR(35)
SHIP_FROM_ST_N CHAR(35)
SHIP_FROM_ZIP_C VARCHAR2(35)
SHIP_FROM_CTRY_N VARCHAR2(40)
PRIM_LINE_I NOT NULL NUMBER(5)
IS_FORCE_RECV_F CHAR(1)
ITEM_I CHAR(40)
ITEM_DESC_T VARCHAR2(200)
UOM_T VARCHAR2(40)
AMZN_STD_INV_I VARCHAR2(10)
DPCI_I CHAR(16)
VEND_STYL_I VARCHAR2(40)
UPC_C VARCHAR2(15)
TCBI_T VARCHAR2(40)
CSPK_Q NUMBER(14,4)
ORD_Q NUMBER(14,4)
RECV_Q NUMBER(14,4)
FORCE_RECV_Q NUMBER(14,4)
PO_RDY_F CHAR(1)
TRF_1_I VARCHAR2(40)
TRF_2_I VARCHAR2(40)
TRF_3_I VARCHAR2(40)
TRF_4_I VARCHAR2(40)
TRF_5_I VARCHAR2(40)
ASRT_ITEM_F CHAR(1)
KIT_ITEM_F CHAR(1)
ITEM_NOTE_T VARCHAR2(2000)
MIN_ORD_Q NUMBER(14,4)
PREP_C VARCHAR2(40)
PSLZ_F CHAR(1)
CMT_D DATE
ITEM_STAT_T VARCHAR2(40)
ITEM_ACT_T VARCHAR2(40)
BILL_TO_ADDR_2_T VARCHAR2(70)
SHIP_TO_ADDR_2_T VARCHAR2(70)
SHIP_FROM_ADDR_2_T VARCHAR2(70)
TOT_RETL_A NUMBER(19,6)
REQ_DOC_N VARCHAR2(40)
REQ_DOC_T VARCHAR2(100)
VISA_1_T VARCHAR2(40)
VISA_2_T VARCHAR2(40)
VISA_3_T VARCHAR2(40)
VISA_4_T VARCHAR2(40)
VISA_5_T VARCHAR2(40)
COLR_T VARCHAR2(40)
SIZE_T VARCHAR2(40)
LINE_STOT_A NUMBER(12,3)
LINE_TOT_A NUMBER(12,3)
UNIT_PRC_A NUMBER(12,3)
PRC_UOM_T VARCHAR2(40)
RETL_PRC_A NUMBER(12,3)
ITEM_TOT_RETL_A NUMBER(12,3)
SHIP_FROM_I VARCHAR2(40)
EDI850_CPLY_F CHAR(1)
EDI860_CPLY_F CHAR(1)

I am trying my best to solve. Let me know if u find anything.
Let me know if u want me to post some more info.

Thanks

ray.wurlod · Post by **ray.wurlod** » Sun Mar 30, 2008 4:56 pm

U (the name of one of our posters) has not participated in this thread.

The correct spelling for the second person personal pronoun in English is "you".

pavans · Post by **pavans** » Sun Mar 30, 2008 6:15 pm

ray.wurlod wrote:U (the name of one of our posters) has not participated in this thread.

The correct spelling for the second person personal pronoun in English is "you".

I apologize, if i am wrong in my writing.
Please can someone help me solve the issue.....

vkhandel · Post by **vkhandel** » Sun Mar 30, 2008 11:03 pm

Actually, the problem is with the field - ord_i char(40)

Since it is a CHAR field, database will append "spaces" to the data at the time of insertion.

Thus, the update SQL, should look for the value -

ORD_I = '0009000311 '
and not
ORD_I = '0009000311'

ray.wurlod · Post by **ray.wurlod** » Mon Mar 31, 2008 12:17 am

Did you mean ORD_I = '0009000311_______________________ ' ?

vkhandel · Post by **vkhandel** » Mon Mar 31, 2008 12:28 am

ray.wurlod wrote:Did you mean ORD_I = '0009000311_______________________ ' ? ...

yes.... exactly

pavans · Post by **pavans** » Mon Mar 31, 2008 8:36 am

vkhandel wrote:
ray.wurlod wrote:Did you mean ORD_I = '0009000311_______________________ ' ? ...
yes.... exactly

Is there any conversion function which i can use to solve this.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 31, 2008 3:29 pm

Trim() either in DataStage or in the extraction SQL.