Replace a String in column

General
Teradata Employee

Replace a String in column

Hi 

I have a a huge task to re-write all the views on the databases  to "Locking row for access" ,I have been working all this week to get a script for this puproose ,I have been able to dynamically eliminate all the invalid views and dump the data into a flat file ..so my file looks like this now ...

Databasename | Tablename | Requesttext (i.e., the definition of view )

I have loaded this data intoa temp table again and am trying to replace the string "AS ( SELECT " with " AS LOCKING ROW FOR ACCESS ( SELECT "

SEL oreplace  (REQUESTTEXT, 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ') FROM Mydatabase.temp_table;

but somehow  i get this instead 

SELECT Failed. 7504:  in UDF/XSP/UDM SYSLIB.oreplace2: SQLSTATE ."Par: 

Is there a better way of doing this ? I dont want to manually update the views ,there are thousands of them !!!

PLease help 

9 REPLIES
Enthusiast

Re: Replace a String in column

Which TD Version you are using? Do you have oreplace2? You can check with your DBA if you do not have.

You can use regexp_replace thus:

select regexp_replace('replace view as (select','as \(select','as locking row for access select (', 1,1,'c')

Enthusiast

Re: Replace a String in column

From which version the string functions like 'replace' and 'translate' added. I not able to see them working in  TD14 even. Is there a seperate licence for it.

--Ashok

Enthusiast

Re: Replace a String in column

It does not require license. You have in vmware too. If I am not wrong,in  TD 14 it's introduced. I work on 14.10 and it is very much there.

Junior Contributor

Re: Replace a String in column

Does your site follow the recommendation to create 1:1 views for every base table?

Then there should be a process already to create those and you might simply add the access lock to it.

When there are additional views on top of those base views you don't need to add an access lock again.

Regarding your approach, what's your TD release? In TD14 your OREPLACE should work as-is. 

Teradata Employee

Re: Replace a String in column

Hi 

I am on 13.10.05.04, While I had no issue doing this .

 sel oreplace(requesttext,';;',';') from Mydatabase.temp_table; 

but somehow this does not work....

SEL oreplace  (REQUESTTEXT, 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ') FROM Mydatabase.temp_table;

SELECT Failed. 7504:  in UDF/XSP/UDM SYSLIB.oreplace2: SQLSTATE ."Par: 

No , they dont follow 1:1 

Junior Contributor

Re: Replace a String in column

Check the definition of your OReplace UDF, it might not work with Unicode columns or the maximum size for the input or output column is smaller then the size of the requesttext. 

There will be workarounds like using POSITION and SUBSTRING instead of OReplace, but as you already exported the data to a flat file, it might be easier to do the processing directly on Unix using awk or Perl or whatever tool you know (and then simply submit the resulting file using BTEQ)...

Teradata Employee

Re: Replace a String in column

Thanks Dieter 

That's exactly what i did ,I replaced these strings using "sed" filter and now running an explain to see if it comes out clean however Now  there's is just one type of views that i cant imagine how to modify ...those are like 

replace view view1 (col1,col2...) AS LOCK TABLE <databasename.tablename> FOR ACCESS....

Here <databasename.tablename> is a variable string so i cant just do the usual find and replace ...

By the way my export file looks like this

Databasename|tablename|<requesttext>

Not sure if i should be worrying about these type of views ,will they cause session blockings ? ....should i leave them the way they are ?

Enthusiast

Re: Replace a String in column

Hi Vishaman,

i checked on your requirement , i got the desired O/P as per your need.

please correct me if i am worng .

--Thanks

Razzendra

Enthusiast

Re: Replace a String in column

Hi vishman,

here i tried with real code which has more number of columns in requesttext , still i could able to see expected result.

+++++++++++++++++++++++++++++++++++++++++++

SEL oreplace  ('REPLACE VIEW sales.retail AS ( SELECT

  column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

, column

from sales_app.retail;', 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ')

;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

oreplace('REPLACE VIEW sales.retail AS ( SELECT   column , c

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

REPLACE VIEW sales.retail AS LOCKING ROW FOR ACCESS (SELECT    column , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  from sales_app.retail;

+++++++++++++++++++++++++++++++++++++++++++

---- Rajendra Reddy