You should check if any of the Oracle functions oTranslate or oReplace exist in your system:

select databasename, tablename
from dbc.tablesV
where tablename in ('oReplace', 'oTranslate')
and TableKind = 'F'
 

If they existHopefully they exist (and you got the neccessary access rights) it's easy:
oReplace(account_no, '.', '/') or oTranslate(account_no, '.', '/')

Dieter

Why do you need the change?

Is it one-time-only or do you need it for every query?

Dieter

Sorry for jumping in.

If you cannot use oReplace maybe some approach like this may help you:
 

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ACC ORIGINAL,
       INDEX( ACC,'.') SEP1,
       SEP1+INDEX( SUBSTR(ACC, SEP1 + 1),'.') SEP2,
       SEP2+INDEX( SUBSTR(ACC, SEP2 + 1),'.') SEP3,
       SUBSTR(ACC,1,SEP1-1) PART1,
       SUBSTR(ACC,SEP1+1,SEP2 -(SEP1+1)) PART2,
       SUBSTR(ACC,SEP2+1,SEP3 -(SEP2+1)) PART3,
       SUBSTR(ACC,SEP3+1) PART4,
       PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 CHANGED
  FROM ( SELECT '108.T4.ABCDEFGHINM.KJIHABCDERG' ACC ) a
;

 *** Query completed. One row found. 9 columns returned.
 *** Total elapsed time was 1 second.

ORIGINAL                       SEP1 SEP2 SEP3 PART1 PART2 PART3       PART4       CHANGED
------------------------------ ---- ---- ---- ----- ----- ----------- ----------- ------------------------------
108.T4.ABCDEFGHINM.KJIHABCDERG    4    7   19 108   T4    ABCDEFGHINM KJIHABCDERG 108/T4/ABCDEFGHINM/KJIHABCDERG
 

The performance it's not been taken into account. Only 'self-explained', 'academic' example.

HTH.

Cheers.

Carlos.
 

Now there's another question:
Why the difference between TD and Oracle when it's supposed te be the same kind of information?

You could also use TRANSLATE on Oracle to change '/' to '.' :-)

Carlos suggestion will work if there's always a four-part account_no and could be adjusted to more or less parts. On TD13.10 you could hide the complex calculation in a SQL UDF, but it's still unneccessary overhead. You were talking about a few hundred rows, so this could be acceptable.

Dieter

It does not matter what the length is, it will work as long as there are three separators/four parts (I followed the format you provided):

SELECT ACC ORIGINAL,
       INDEX( ACC,'.') SEP1,
       SEP1+INDEX( SUBSTR(ACC, SEP1 + 1),'.') SEP2,
       SEP2+INDEX( SUBSTR(ACC, SEP2 + 1),'.') SEP3,
       SUBSTR(ACC,1,SEP1-1) PART1,
       SUBSTR(ACC,SEP1+1,SEP2 -(SEP1+1)) PART2,
       SUBSTR(ACC,SEP2+1,SEP3 -(SEP2+1)) PART3,
       SUBSTR(ACC,SEP3+1) PART4,
       PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 CHANGED
  FROM ( SELECT '00.ABCD.123456.YZ' ACC ) a
;

 *** Query completed. One row found. 9 columns returned.
 *** Total elapsed time was 1 second.

ORIGINAL          SEP1 SEP2 SEP3 PART1 PART2 PART3  PART4 CHANGED
----------------- ---- ---- ---- ----- ----- ------ ----- -----------------
00.ABCD.123456.YZ    3    8   15 00    ABCD  123456 YZ    00/ABCD/123456/YZ

If there are other different formats (variable number of separators/parts) I would try a recursive query.

Cheers.

Carlos.

Hi:

You could substitute the 'PARTS' and 'SEPS' (as I said, it was only for 'academic' purposes) with their expressions. On the other hand, you could always use the quick & dirty wrapping (not tested, of course):

SELECT BILL_DTL.CABS_BILLING_NO,
       BILL_DTL.BILL_MONTH_DT,
       BILL_DTL.BILL_CYCLE_DT,
       SUM_CHRGE_AMT,
       CHANGED
 FROM (
         SELECT BILL_DTL.CABS_BILLING_NO,
                BILL_DTL.BILL_MONTH_DT,
                BILL_DTL.BILL_CYCLE_DT,
                sum(BILL_DTL.CHRGE_AMT (FORMAT '$999,999.99')) SUM_CHRGE_AMT,
                 INDEX( ACCOUNT_NO,'.') SEP1,
                SEP1+INDEX( SUBSTR(ACCOUNT_NO, SEP1 + 1),'.') SEP2,
                SEP2+INDEX( SUBSTR(ACCOUNT_NO, SEP2 + 1),'.') SEP3,
                SEP3+INDEX( SUBSTR(ACCOUNT_NO, SEP3 + 1),'.') SEP4,
                SUBSTR(ACCOUNT_NO,1,SEP1-1) PART1,
                SUBSTR(ACCOUNT_NO,SEP1+1,SEP2 -(SEP1+1)) PART2,
                SUBSTR(ACCOUNT_NO,SEP2+1,SEP3 -(SEP2+1)) PART3,
                SUBSTR(ACCOUNT_NO,SEP3+1,SEP4 -(SEP3+1)) PART4,
                SUBSTR(ACCOUNT_NO,SEP4+1) PART5,
                PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 || '/' || PART5 CHANGED
         FROM edw_vwmc.BILL_DTL BILL_DTL
         WHERE   (BILL_DTL.CABS_BILLING_NO = '60020555s3')
                AND (BILL_DTL.BILL_MONTH_DT =
                        calendar__gregorian__current_month__)
                AND (BILL_DTL.ACCOUNT_NO LIKE '%442529%')
         GROUP BY BILL_DTL.BILL_MONTH_DT,
                  BILL_DTL.CABS_BILLING_NO,
                  BILL_DTL.BILL_CYCLE_DT,
                  BILL_DTL.ACCOUNT_NO

) a

HTH

Cheers

Carlos.

>>"thoughts on resolving this?"

Yes: Sanitize your data.

I tried to give you a hint with the (little) info you provided in the first post, not to solve all your problems. I think I showed you the way, now you should go on...

BTW: I think Dieter's first advice (oReplace/oTranslate UDFs) is what I would do If I was in your shoes (especially with all this different cases that you are now revealing).

Cheers.

Carlos.

As Carlos already indicated recursive could be used - but I don't have a clue how the performance looks like on bigger data volumes.

This solution assumes that the max str length is 100. Longer fields would need adjustmenst.

A two step approach is needed

1. split the string into single chars, change . to / and store the result in volatile table

The SQL below is creating some test data

create volatile table ctl 
as
(
select t.unique_id, t.text,c.id, characters(t.text)as c_len, case when substr(t.text,id,1) = '.' then '/' else substr(t.text,id,1) end as str 
from (select day_of _calendar as unique_id, substr('240 \t108.T4.ABCDEFGHINM.KJIHsdkskdsd.sds.ds.d.s.d.s.dsddfsf.sfs..sfsdgdfgdhte43343 .34..34.34..r34rfsd.rfgd..gdf.g..re3 .34.r.3.34..fr.sf.regdfgerge..erg.e.rgegergerge.rge..ABCDERG',random(1,100),random(15,50)) as text
      from sys_calendar.calendar
      where calendar_date - current_date between 1 and 1000
      ) as t
      join 
     (select calendar_date - current_date as id
      from sys_calendar.calendar
      where id between 1 and 100
      ) as c
		on c.id <= characters(t.text)
) with data
primary index (unique_id)
on commit preserve rows

Now use a recusive query for recreate the string

WITH RECURSIVE base (unique_id,text,id,c_len,str) as
(select unique_id,text,id, c_len, cast(str as varchar(100))
 from ctl 
 where id = 1
 union all
 select c.unique_id,
        c.text,
        c.id, 
        c.c_len,
        b.str !! c.str
 from ctl c
      join
      base b
         on c.id = b.id +1
            and c.unique_id = b.unique_id
 )
select unique_id, text, str
from base
where id = c_len

result should look like

unique_id	text	str
41655	Hsdkskdsd.sds.d	Hsdkskdsd/sds/d
41683	d.sds.ds.d.s.d.	d/sds/ds/d/s/d/
41489	.s.dsddfsf.sfs.	/s/dsddfsf/sfs/
41411	sfs..sfsdgdfgdh	sfs//sfsdgdfgdh
41187	40 	108.T4.ABCD	40 	108/T4/ABCD

Hi,

Can we replace the '.' with '/' by the following query,

ABC.D can be replaced by the column value,

SELECT (SELECT SUBSTR('ABC.D',1,((SELECT INDEX('ABC.D','.') AS CNT)-1)))||'/'||(SELECT SUBSTR('ABC.D',((SELECT INDEX('ABC.D','.') AS CNT)+1),(SELECT CHAR_LENGTH('ABC.D'))))

Regards,

Sayandeep