Conversion of SQL Server Query to Teradata Query

Database

Conversion of SQL Server Query to Teradata Query

Hello everyone,

First of all,  I'm sorry. I'm disturbing your. I have a question about conversion of SQL Server Query to Teradata Query.

I'm new to Teradata and If anyone can help me in conversion of query to teradata;

CASE WHEN CHARINDEX ('/' , COLUMN_NAME, CHARINDEX ('/' , COLUMN_NAME) +1) <> 0

THEN SUBSTRING (COLUMN_NAME , CHARINDEX ('/' , RTRIM (COLUMN_NAME)) +1, CHARINDEX ('/' , COLUMN_NAME, CHARINDEX ('/' , COLUMN_NAME) +1 )=0

Thanks in advance

Best Regards;

ISMAİL ARSLAN

9 REPLIES
N/A

Re: Conversion of SQL Server Query to Teradata Query

Hi Ismail,this should be a direct tarnslation:

CASE

  WHEN POSITION('/' IN SUBSTRING(x FROM POSITION('/' IN x) + 1)) <> 0

  THEN

    SUBSTRING(SUBSTRING(x FROM POSITION('/' IN x) + 1)

              FROM 1

              FOR POSITION('/' IN SUBSTRING(x FROM POSITION('/' IN x) + 1))-1)

END

Dieter

Re: Conversion of SQL Server Query to Teradata Query

thanks for everything.

I'll try on Monday.

Best Regards

Ismail Arslan @ Turkey

Re: Conversion of SQL Server Query to Teradata Query

By the way, I wanted this query, the following reasons;

- I want to parsing at TERADATA.

To give an example;

Column Name: Description

Data 1 : AAA

Data 2 : AAA / BBB

Data 3 : AAA / BBB / CCC

=>>> I want to create three new columns.

ColumnName1:Desc1

Data1:AAA

Data2:AAA

Data3:AAA

ColumnName2:Desc2

Data1:NULL

Data2:BBB

Data3:BBB

ColumnName3:Desc3

Data1:NULL

Data2:NULL

Data3:CCC

Best Regards;

Ismail Arslan

N/A

Re: Conversion of SQL Server Query to Teradata Query

Hi Ismail,

nested POSITION/SUBTRING get quite ugly, but if it's limited to three columns it's probably still ok.

You should check if UDFs like InStr or StrTok are installed on your system, which will simplify the parsing.

SELECT * FROM dbc.FunctionsV will return all UDFs, FunctionsVX only those you have access rights to use.

Dieter

Re: Conversion of SQL Server Query to Teradata Query

Hello again Dieter,

Thanks for update.

But I could not this query integrate own queries.

This situation very urgent for us. 

Can you help me in conversion of query to teradata ?

All SQL SERVER queries are as follows;

CASE WHEN CHARINDEX ( '/' , COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME) +1 ) <>0

THEN SUBSTRING ( COLUMNNAME , CHARINDEX ( '/' , RTRIM ( COLUMNNAME) )+1,

CHARINDEX ( '/' , COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME) +1) - (CHARINDEX ('/' , COLUMNNAME ) +1 ))

WHEN CHARINDEX ( '/' , COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME) +1)=0

AND CHARINDEX ( '/' , COLUMNNAME) <> 0

THEN SUBSTRING  ( COLUMNNAME, CHARINDEX ( '/'  ,RTRIM (COLUMNNAME)) +1 ,

LEN (COLUMNNAME) - CHARINDEX ( '/' , RTRIM (COLUMNNAME)) +1)

ELSE NULL END AS DESCRIPTION2

CASE WHEN CHARINDEX ( '/' , COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME) +1 ) <>0

THEN SUBSTRING ( COLUMNNAME , CHARINDEX ( '/' , COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME) +1) +1,

LEN (COLUMNNAME) - CHARINDEX ( '/' , (COLUMNNAME, CHARINDEX ( '/' , COLUMNNAME)+1) +1)

ELSE NULL END AS DESCRIPTION3

Thank you for everything.

Thanks in advance.

Best Regards;

Ismail Arslan

N/A

Re: Conversion of SQL Server Query to Teradata Query

Hi Ismail,

this will split into three columns (corrected version):

SUBSTRING(x FROM 1 FOR POSITION('/' IN x || '/') -1),
CASE
WHEN x LIKE '%/%'
THEN
SUBSTRING(SUBSTRING(x FROM POSITION('/' IN x) + 1) || '/'
FROM 1
FOR POSITION('/' IN SUBSTRING(x FROM POSITION('/' IN x) + 1) || '/') -1)
END,
CASE
WHEN x LIKE '%/%/%'
THEN
SUBSTRING(SUBSTRING(x FROM POSITION('/' IN x) + 1)
FROM POSITION('/' IN SUBSTRING(x FROM POSITION('/' IN x) + 1))+1)
END


Dieter

Re: Conversion of SQL Server Query to Teradata Query

Thank you for the update.

You are a great man.

But c2 returns null.  What do you think about this situation?

CASE

  WHEN x LIKE '%/%'

  THEN

    SUBSTRING(SUBSTRING(x FROM POSITION('/' IN x) + 1) || '/'

              FROM 1

              FOR POSITION('/' IN SUBSTRING(x FROM POSITION('/' IN x || '/'))))

END AS c2,

Thank you again.

Best Regards;

Ismail Arslan

N/A

Re: Conversion of SQL Server Query to Teradata Query

Hi Arslan,

i modified my query after a few minutes and repostet it, you probably used the first (=wrong) one :-)

Just try it again

Dieter

Re: Conversion of SQL Server Query to Teradata Query

Thank you Dieter.

See you later.

Best Regards.