All Forums Database
gmoney 23 posts Joined 12/10
25 Apr 2012
Change all periods "." in a column to forward slash "/"

I am running a query that returns blling data with an account number that contains "." in no set position. What I need to do is replace the period "." in each instance that it occurs with a forward slash "/".

I have read many items related to using UDF's, however I do not own the server and have no idea if any UDFs are actually loaded.

The Release is 12.00.02.44

Version 12.00.02.40

 

Example of current results:

 

BILLING_NO

BILL_CONTH_DT

BILL_CYCLE_DT

SUM(CHRGE_ACT)

ACCOUNT_NO

123456789

4/1/2012

4/10/2012

240

108.T4.ABCDEFGHINM.KJIHABCDERG

 

 

 

 

 

Desired Reults:

BILLING_NO

BILL_CONTH_DT

BILL_CYCLE_DT

SUM(CHRGE_ACT)

ACCOUNT_NO

123456789

4/1/2012

4/10/2012

240

108/T4/ABCDEFGHINM/KJIHABCDERG

I am not a regular user of Teradata so I would appreciate a detailed explination - I am willing to learn :) !

Thanks for your time.

 

 

dnoeth 4628 posts Joined 11/04
25 Apr 2012

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

 

Dieter

gmoney 23 posts Joined 12/10
25 Apr 2012

I just checked - nothing there.

gmoney 23 posts Joined 12/10
25 Apr 2012

Anyone have any suggestions or thoughts on how to accomplish this?

I appreciate your time and thanks for looking.

 

Gmoney

dnoeth 4628 posts Joined 11/04
25 Apr 2012

Why do you need the change?

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

Dieter

Dieter

CarlosAL 512 posts Joined 04/08
26 Apr 2012

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.
 

gmoney 23 posts Joined 12/10
26 Apr 2012

Dieter,

I need the change everytime the query runs. Primarily because this will end up being part of a cross-query against an Oracle query that will depend on the account_no being formated with the slashes.

For all intensive purposes the account_no in the Teradata db is a PK to the FK account_no in the Oracle db.

 

Carols,

I believe I see your reasoning but I do not believe it will work for this situation because I have various account_no formats to deal with, and the strng lengths are different.

For example another version could look like: 00.ABCD.123456.YZ .

Thanks for the follow up to both of you.

dnoeth 4628 posts Joined 11/04
26 Apr 2012

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

Dieter

CarlosAL 512 posts Joined 04/08
26 Apr 2012

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.

gmoney 23 posts Joined 12/10
26 Apr 2012

Carlos

Thanks for your follow up! I appreciate your patience with the newbie. What it boils down to then is that I did not know how to apply this to my existing query. After playing with it a little, of course, it does work as you and Dieter both said.

I also manged to "adjust it" to cover the variances in formats of ACCOUNT_NO.

Here is the query as I am running:

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')), --> format not working no $    BILL_DTL.ACCOUNT_NO,
        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

 

 

 

Now for the dumb question - I only want to display my original columns such as :

 

BILLING_NO

BILL_CONTH_DT

BILL_CYCLE_DT

SUM(CHRGE_ACT)

ACCOUNT_NO

 

How do I prevent the SEP1, PART 1 from displaying in the result set?

while keeping CHANGED  column in the display?

 

Regards,

GMoney

 

CarlosAL 512 posts Joined 04/08
26 Apr 2012

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.

gmoney 23 posts Joined 12/10
26 Apr 2012

I will try the wrapping. I found a couple instance where I am getting TERADATA SUBSTR: string subscript out of bounds.

So it looks as though the inconsistent formating is going to cause some issues. As you know the substr works example #3 below, when using 5 Sep/6 Parts but errors on # 1&2.

If I cut it back to 4 sep/5 parts then al 3 work, with the exception of #3 leaving the "period" at the end.

 

Some examples:

1) 000.AB.ABCDEFGHIJKL.ABCDEFGHIJKL
2) 00.ABCD.123456.   .WXYZ
3) 00.ABCD.123456.   .WXYZ. 

 

Any thoughts on how to handle that?

GMoney

gmoney 23 posts Joined 12/10
26 Apr 2012

I tried the wrapping, but to no avail. I received the error:

[Teradata Database] [3706] Syntax error: expected something between '(' and the 'SELECT' keyword.

I suspect the reason is that Scalar subqueries are not supported?

Release is 12.00.02.44 Version 12.00.02.40  

 

When I try to nest it in the From statement, I get this error:

[Teradata Database] [3707] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the

(I have no UDF's and cannot add any - bummer cause it sounds like it would be much easier if i could)

This is fun!!! :)

So as it stands right now using Carlos' script from above modified to have 4 seperators and 5 parts resulting in one format returning:

 

1) 000/AB/ABCDEFGHIJKL/ABCDEFGHIJKL
2) 00/ABCD/123456/   /WXYZ
3) 00/ABCD/123456/   /WXYZ.  -- > Note the hanging "." at the end.

 

Suggestions anyone?

gmoney 23 posts Joined 12/10
26 Apr 2012

Carlos,

I am not sure if my last post made sense or not. Basically what I am seeing in my data is for my part 4

the data either appears as:

123.A1.ABCDEFGHIJK.ABCDEFGHIJK

99.ABDC.654321.   .ABCD

00.HIJK.123456.   .ABCD.

0001.AB--CD.ABCDEFGHIJK.00.ABCDEFGHIJK

So running it with your orignal code gives me:

123/A1/ABCDEFGHIJK/ABCDEFGHIJK - (Perfect)

99/ABDC/654321/   .ABCD - (1 Decimal Remaining)

00/HIJK/123456/   .ABCD. - (2 Decimals Remaining)

0001/AB--CD/ABCDEFGHIJK/00.ABCDEFGHIJK - (2 Decimals Remaining)

Of course if I increase the SUBSTR  then I get an error if there are less . (Decimals) then my # of SUBSTRs.

Hope that makes it clearer what I am seeing - thoughts on resolving this?

Regards,

GMoney

 

CarlosAL 512 posts Joined 04/08
26 Apr 2012

>>"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.

gmoney 23 posts Joined 12/10
27 Apr 2012

Carlos,

Thanks.

I wish I could Sanitize the data or use oReplace/oTranslate UDFs - neither of which is an option.

So I am left with trying to 'make the best of it' as they say.

 

Regards,

Greg

 

 

ulrich 816 posts Joined 09/09
27 Apr 2012

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


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sayandeep 7 posts Joined 04/12
30 Apr 2012

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

 

 

gmoney 23 posts Joined 12/10
15 May 2012

Sorry - I got distracted by another issue.

Thanks folks - I will check both of these methods and respond back soon.

You must sign in to leave a comment.