× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Steve,

I'm curious why you want to stick with the LENGTH(TRIM(A.CPCSID)) instead of TRANSLATE?  I would think the TRANSLATE is safer, in that the LENGTH statement doesn't guarantee that the date is numeric.  I mean, it may work with your data, but in the general case it wouldn't.

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

On 5/17/2019 5:28 AM, Steve Richter wrote:
Tim,

could you show me in this view what changes have to be made to make it
work? I understand if I replace LENGTH(TRIM(A.CPCSID)) with TRANSLATE, it
will work. But how to structure the view while retaining LENGTH and TRIM?

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,
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
from contr a
where length(trim(a.cpcsid)) = 10 ;

thanks,

-Steve





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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.