Re: why data translate error when selecting from view?

Is the contr table static? Could it have changed between creating the view and doing the select?

The other thing that would help is finding the record that's causing the problem. The 2nd level text might show the rrn of the record in error.   Knowing that, you can figure out what it's complaining about and that will probably let you know the problem.

Also, just because the length of trim(cpcsid) is 10 doesn't necessarily mean  those 10 characters are all numeric.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 5/14/2019 8:08 AM, Steve Richter wrote:

why would selecting from a view result in a data translate error when I am
able to select all the rows and columns of that view and copy to another
table? ( the view has a WHERE clause which selects rows with valid data )

table CONTR has a customer number that stored in a character field. And
some of the values in that char field are not all digits. So I create a
view that selects only the rows where that customer number field actually
contains a customer number. And in the view, I convert the character cust
number field to a decimal field.

I prove this works by creating a new table by selecting all rows from the
view:
create table aplusb1fcc/contrpx as (
select a.* from contrv1 a
) with data
Table CONTRPX in APLUSB1FCC created but was not journaled.

but when I select from the view I get an error:
select a.itcd, a.itno, a.startDate, a.expireDate
from contrv1 a
where a.csno = 1346100
and a.expiredate >= current date
Selection error involving field *N.

Select or omit error on field Cast(Translate(CONTR_1.CPCSID, *UNNAMED
Table) AS member CONTRV1.

here is the view:

create OR REPLACE view aplusb1fcc/contrv1 as
select a.cpcono cono,
case when length(trim(a.cpcsid)) = 10
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,
a.cpitcd itcd, a.cpitno itno,
a.cpprcd prcd, a.cpngpr ngpr,

DATE(digits(a.cpstcc) || substr(digits(a.cpstdt),1,2) ||
'-' || substr(digits(a.cpstdt),3,2) ||
'-' || substr(digits(a.cpstdt),5,2)) startDate,

case when a.cpexdt = 0 then date('2050-12-31')
else
DATE(digits(a.cpexcc) || substr(digits(a.cpexdt),1,2) ||
'-' || substr(digits(a.cpexdt),3,2) ||
'-' || substr(digits(a.cpexdt),5,2)) end expireDate,

a.cpcnqt cnqt, a.cpprbk prbk

from contr a
where length(trim(a.cpcsid)) = 10 ;

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus