All Forums Database
Nikhil_Teradata 24 posts Joined 10/13
12 Aug 2015
Issue in Substr and Index with CASE

Hi I am having an issue with below syntax:
When I run below query 

SELECT URL_Val, SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10) FROM DB.A Click
WHERE Click.URL_Val LIKE '%artist%'

I get below result
URL_Val                                                                             SUBSTR

http://ABC.com/artist/123456/HANSON:-EMLN_690635    123456/HA

 

However, when I try to do it using a Case statement,

 

SELECT 

Click.a

,Click.b

, Click.c

, Click.d

, Click.URL_Val

, CASE 

WHEN Click.URL_Val LIKE '%event%'  

THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)

WHEN Click.URL_Val LIKE '%artist/%'

THEN  SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10  ) 

END

,Click.e

,P.f

,p.g

,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))

FROM DB.ABC Click

INNER JOIN DBA.PAT p ON click.e = patron.e;

 
I get below result for artist row. 

URL_Val                                                                             SUBSTR

http://ABC.com/artist/123456/HANSON:-EMLN_690635    //ABC.com/arti

 

I need to get result as mentioned in the first result set. Am I missing something here? How come the extraction is different in second result set? Please help.

 

Thanks,

Nik

 

 

--Nik

"Learn, Learn, Learn and your life would never be boring"

Nikhil_Teradata 24 posts Joined 10/13
12 Aug 2015

Sorry for the missing code window for second piece. Here it is

SELECT 
Click.a
,Click.b
, Click.c
, Click.d
, Click.URL_Val
, CASE 
WHEN Click.URL_Val LIKE '%event%'  
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)
WHEN Click.URL_Val LIKE '%artist/%'
THEN  SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10  ) 
END
,Click.e
,P.f
,p.g
,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))
FROM DB.ABC Click
INNER JOIN DBA.PAT p ON click.e = patron.e;

 

--Nik

"Learn, Learn, Learn and your life would never be boring"

dnoeth 4628 posts Joined 11/04
12 Aug 2015

Hi Nik,
//ABC.com/arti is really strange, seems like it didn't find 'artist' and then started at 0 + 6, but returned only 14 instead of 16 characters.
 

SELECT
'http://ABC.com/artist/123456/HANSON:-EMLN_690635' AS URL_Val,
CASE
   WHEN URL_Val LIKE '%event%' 
   THEN SUBSTR (URL_Val, INDEX(URL_Val,'event/')+6, 16)
   WHEN URL_Val LIKE '%artist/%'
   THEN  SUBSTR (URL_Val, INDEX(URL_Val,'artist/')+7, 10  ) 
END

returns 123456/HAN as expected.
What if you change WHEN URL_Val LIKE '%artist/%' to WHEN INDEX(URL_Val,'artist/') > 0?
 
You might also have a look a REGEXP_SUBSTR, might be easier depending on what part of the string you try to extract. 

Dieter

Nikhil_Teradata 24 posts Joined 10/13
12 Aug 2015

Hi Dieter,
Here is a bit modification on the previous issue.
I am trying to query a table that will have event and artist information in URLs. This step removes the artist and event info from the URL. Each URL will have either artist or event info but never both. So I am using case.
If run separately like you mentioned, the results are fine. But if put in a case statement as specified by the big select(which is part of an insert statement), it does not work and does not find the 'artist' part.
Here is the weird pard. If we change the order of 'event' and 'artist' as below,

SELECT
Click.a
,Click.b
, Click.c
, Click.d
, Click.URL_Val
, CASE
WHEN Click.URL_Val LIKE '%artist/%'
THEN  SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10  ) 
WHEN Click.URL_Val LIKE '%event%' 
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)
END
,Click.e
,P.f
,p.g
,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))
FROM DB.ABC Click
INNER JOIN DBA.PAT p ON click.e = patron.e;

I get the same issue with event URL's but artist URLs are extracted correctly. 
I am trying your suggestion of WHEN INDEX(URL_Val,'artist/') > 0. Will update shortly. Meanwhile, any help on this issue is really appreciated.
 
Thanks,
Nik

--Nik

"Learn, Learn, Learn and your life would never be boring"

dnoeth 4628 posts Joined 11/04
12 Aug 2015

Hi Nik,
you should open an incident with Teradata support for this issue.

Dieter

You must sign in to leave a comment.