RE: [sqr-users] Fetched column value was truncated

["Mingazov, Maya" <MMingazov@newsamerica.com>]

I had similar problem and Kevin helped me to solve it:

'
  begin-select
>  'short value'  &x
>  ...  
>  union select  
>  'a longer value'   
>  ...  
>     
>  Be sure that the fields listed in each part of the union are the
same, or if you have constants (like in my example above) be sure to add
enough space in the first part so that it is as long as the values in
the other parts:  
>     
>  begin-select  
>  'short value            '  &x
>   
>  Kevin Reschenberg
'
Hope this helped,

Maya 


-----Original Message-----
From: sqr-users-bounces+mmingazov=newsamerica.com@sqrug.org
[mailto:sqr-users-bounces+mmingazov=newsamerica.com@sqrug.org] On Behalf
Of Alexander, Steven
Sent: Tuesday, July 25, 2006 5:03 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Fetched column value was truncated

When we run this program from our desktop (Peoplesoft's sqrw 8.20.06 on
Windows XP), it works.  When we run it from the Peoplesoft Process
Scheduler
(Peoplesoft's pssqr 8.20.06 on Solaris), it stops with the following
error
message:

 

ORA-01406: fetched column value was truncated
Previous CDV row: 001-294319-DEPT64-4003
 
(SQR 5528) ORACLE OCIStmtFetch error 1406 in cursor 6:
   ORA-01406: fetched column value was truncated
 
Error on line 222:
   (SQR 3725) Bad return fetching row from database.

 

 

Here is the select statement that fails:

 

 

!***********************************************************************
****
****

begin-procedure read_data_warehouse

  move 0 to #i

begin-select on-error=cdv_error

nvl(rtrim(FUND_CODE), ' ')       &CDV.FUND_CODE

nvl(rtrim(ACCOUNT), ' ')         &CDV.ACCOUNT

nvl(rtrim(CSJ_DEPT2), ' ')       &CDV.CSJ_DEPT2

nvl(rtrim(CLASS_FLD), ' ')       &CDV.CLASS_FLD

nvl(rtrim(DESCR), ' ')           &CDV.DESCR

nvl(rtrim(BUDGET_DEPTID), ' ')   &CDV.BUDGET_DEPTID

nvl(rtrim(SUBDIVISION_LBL), ' ') &CDV.SUBDIVISION_LBL

nvl(rtrim(ASSET_NBR), ' ')       &CDV.ASSET_NBR

  let $acct_cd = &CDV.FUND_CODE || '-' || &CDV.ACCOUNT || '-DEPT'

              || &CDV.CSJ_DEPT2 || '-' || &CDV.CLASS_FLD

  if &CDV.CSJ_DEPT2 = '57'

    let $dw_division = cond(instr($divisions, ' ' ||
&CDV.SUBDIVISION_LBL ||
' ', 1) = 0,

                            'ADMIN', &CDV.SUBDIVISION_LBL)

  else

    let $dw_division = ' '

  end-if

  put $acct_cd &CDV.DESCR &CDV.BUDGET_DEPTID $dw_division &CDV.ASSET_NBR
into dw(#i)

  add 1 to #i

 from PS_CSJ_DWGL001M_VW CDV

order by CDV.FUND_CODE, CDV.ACCOUNT, CDV.CSJ_DEPT2, CDV.CLASS_FLD

end-select

end-procedure read_data_warehouse

 

!***********************************************************************
****
****

begin-procedure cdv_error

  add 1 to #num_cdv_errors

  if #num_cdv_errors <= 10

    show $sql-error

    show 'Previous CDV row: ' $acct_cd

    show ' '

  else

    stop

  end-if

end-procedure cdv_error

 

I'm curious as to why my on-error routine doesn't keep the program
running,
but I'm more interested in fixing the error.  One hint is that the
failed
row has text with extended ASCII (value 194), but the successful row
before
it also has that text.  Another possible issue is that I am reading
PS_CSJ_DWGL001M_VW, which is a view of a view in a different database
instance.  I am reading hundreds of rows from that view before the
error,
though.  Here is the script for PS_CSJ_DWGL001M_VW:

 

CREATE OR REPLACE VIEW PS_CSJ_DWGL001M_VW

(FUND_CODE, ACCOUNT, CSJ_DEPT2, CLASS_FLD, DESCR, 

 BUDGET_DEPTID, SUBDIVISION_LBL, ASSET_NBR)

AS 

SELECT DISTINCT F_FUND , substr(CHARGE_CODE , 9 ,6) , substr(CHARGE_CODE
, 7
,2) , AC_SUBACCT , TITLE , R_DEPT , DACREF , D_ASSET_NUMBER 

FROM  <mailto:GL001M07_V@DW02.X1A> GL001M07_V@DW02.X1A 

WHERE AC_SUBACCT IN
('4001','4003','4006','4008','4010','4011','4012','4032','4042','4043','
4044
','4046','4060','4065','4102') 

AND ACTIVE_FLAG = 'A' 

AND ALRE_CODE = 'E'

 

And GL001M07_V is a view with the following script:

 

CREATE OR REPLACE VIEW "FMSDATA"."GL001M07_V" ("DEPT_GROUP",

    "CHARGE_CODE","F_FUND","F_SUBFUND","F_REPORT_CODE","R_DEPT",

    "R_SUBDEPT","R_DEPTFUNC","A_PROGRAM","A_SUBPROG","A_ACTIVITY",

    "D_ASSET_TYPE","D_ASSET_NUMBER","D_ASSET_COMP","P_PROJECT",

    "P_SUBPROJ","AC_ACCOUNT","AC_SUBACCT","AC_DETACCT","S_SOURCE",

    "ACTUAL_ENTERED","ACTUAL_APPROVED","ACTUAL_PROOFED",

    "ACTIVE_FLAG","TITLE","COMMIT_ENTERED","COMMIT_APPROVED",

    "COMMIT_PROOFED","ALRE_CODE","DACREF") AS 

    select dept_group, charge_code, f_fund, f_subfund, f_report_code,
r_dept, 

r_subdept, r_deptfunc, a_program, a_subprog, a_activity, d_asset_type,

d_asset_number, d_asset_comp, p_project, p_subproj, ac_account,
ac_subacct, 

ac_detacct, s_source, actual_entered, actual_approved, actual_proofed,
active_flag, 

title, commit_entered, commit_approved, commit_proofed, alre_code,
dacref 

from FMSDATA.gl001m07 

 WITH READ ONLY

 

 

 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


This message and its attachments may contain legally privileged or confidential 
information.  It is intended solely for the named addressee.  If you are not 
the addressee indicated in this message (or responsible for delivery of the 
message to the addressee), you may not copy or deliver this message or its 
attachments to anyone.  Rather, you should permanently delete this message and 
its attachments and kindly notify the sender by reply e-mail.  Any content of 
this message and its attachments that does not relate to the official business 
of News America Incorporated or its subsidiaries must be taken not to have been 
sent or endorsed by any of them.  No warranty is made that the e-mail or 
attachment(s) are free from computer virus or other defect.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users