Join with Different Data Formats

Database
Enthusiast

Join with Different Data Formats

I keep getting the error "Bad character in format or data of VLTC_ER_COV_MO.I_IRD"

An example of a string in i_ird would be "00574286073740714EE19770516F"

There are cases where there is a letter in the substring... this is why the cast will not work...

Any ideas?

THANKS!!!

select i_pol

,c_typ_cov as CovType

,a_bft_mo_nhm as OrigAmt

,i_ird as IRD_ID

,CAST(substr(i_ird, 9, 9) AS INTEGER), 0 as SSN

,max(d_eff_cov) as EffCovDate

from bcpm_ddbo.vltc_er_cov_mo

where CovType <> 'FACILITY'

group by 1,2,3,4,5
5 REPLIES
Enthusiast

Re: Join with Different Data Formats

Hi,
you are right, conversion to integer doesn't support letters. If there are a reasonable number of letters, you can try something like CASE LIKE ANY construction, depending on what do you want in case of letter occurrence, see sample:

SEL
'00574286073740714EE19770516F' AS s
, SUBSTR(s, 9, 9) AS sst
, CASE WHEN sst LIKE ANY ('%A%', '%B%', '%C%', '%D%', '%F%' ) THEN 0 ELSE CAST( sst AS INT) END AS sval

bgrds

Petr
Enthusiast

Re: Join with Different Data Formats

well I would not be selecting the string... But the column that contains it? Also, just add any possibility of letter?
Enthusiast

Re: Join with Different Data Formats

As far as I know, there is no generall construction like on MS SQL server for LIKE allowing character interval, e.g. LIKE (%[A-Z]%), on the other hand you can download and implement UDF Is_numeric() from Teradata site: http://www.teradata.com/DownloadCenter/Topic9236-129-1.aspx

This query works supposing no other then numeric or letter characters appear in column i_ird:
SELECT
i_pol
, c_typ_cov AS CovType
, a_bft_mo_nhm AS OrigAmt
, i_ird AS IRD_ID
, CASE WHEN
UPPER(SUBSTR(i_ird, 9, 9)) LIKE ANY ('%A%','%B%','%C%','%D%','%E%','%F%','%G%','%H%','%I%','%J%','%K%','%L%','%M%','%N%','%O%','%P%','%Q%','%R%','%S%','%T%','%U%','%V%','%W%','%X%','%Y%','%Z%')
THEN 0
ELSE CAST( SUBSTR(i_ird, 9, 9) AS INTEGER)
END AS SSN
, MAX (d_eff_cov) AS EffCovDate
FROM
bcpm_ddbo.vltc_er_cov_mo
WHERE
CovType <> 'FACILITY'
GROUP BY
1,2,3,4,5
;

Enthusiast

Re: Join with Different Data Formats

Excellent!

You are absolutely right on both accounts. We already have the UDF and your example worked too. Thanks a bunch, this is much appreciated!!!
Junior Contributor

Re: Join with Different Data Formats

If a string is supposed to be an integer, it should be defined as an integer.

Do you really need to cast it to an integer? Can't you keep it as a char(9)?

If you still want to cast it to an integer and there might be any charcter, you need one of the existing Oracle UDFs.
There's an old trick using nested TRANSLATEs:

otranslate(column,'0' || otranslate(s,'.0123456789','.'), '0')

Btw,
in the heading you wrote about join, but there's no join...

Dieter