Pattern matching in case statement

Teradata Applications
Enthusiast

Pattern matching in case statement

Hi

I'm trying to match pattern in CASE Statement using a subquery but my query output is never resulting in that Case. I suspect that the pattern matching in Case is not working, because the relevant dataset is, in fact, present.

 

SyntaxEditor Code Snippet

WHEN (
            m2.agnt_cd IS NOT NULL 
            and  (m2.agnt_cd||'~'||m2.chnl_cd1)=(select    lkup_txt     from    SLSOPSDB.LKUP_REF_skj where    lkup_1_txt    ='OMNI Report code bypass valve')
            AND (m2.chnl_cd1 = 'DR'  OR m2.chnl_cd2 = 'DR' OR m2.chnl_cd3 = 'DR')
            )
        THEN 'BYPASSED CHANNEL'        

Output of my CASE subquery:

select    lkup_txt     from    SLSOPSDB.LKUP_REF_skj where    lkup_1_txt    ='OMNI Report code bypass valve') 
SyntaxEditor Code Snippet
XBNW4~DR

Please help!

8 REPLIES
Junior Apprentice

Re: Pattern matching in case statement

Hi,

 

Is there a preceding WHEN clause which your rows are meeting? If so then those rows will not be tested against this WHEN clause.

 

If the 'dataset is present' that means that you have rows on table 'm2' where both of the following conditions are true:

m2.agnt_cd = XBNW4
m2.chnl_cd1 = DR

 

Also just to check. Is this a case-sensitive issue?

 

Also, please bear in mind that this query will fail if your sub-query returns more than one row. In this context the sub-query has to be a scalar sub-query.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Pattern matching in case statement

Hi Dave, Thanks for spending time over this!

1. Yes,

m2.agnt_cd = XBNW4
m2.chnl_cd1 = DR

are present

 

2. No case sensitive issues. Both sides are exact same cases, even so, I tried with UPPER(). Still no luck.

 

3. My subquery is a lookup table, and it is returning exactly one row. It is designed that way :)

 

Actually, I have heard that pattern matching on huge tables, more so as a part of CASE statement, is not too robust in Teradata. Do you think is might be such an issue ? Or just bad coding on my part?

I'm asking this because when I run a select with these exact conditions on my table, I get a clear result. Its just not working in CASE.

 

Highlighted
Teradata Employee

Re: Pattern matching in case statement

Perhaps m2.agnt_cd has trailing spaces, e.g. fixed CHAR field, and you need to TRIM before concatenating?

 

 

Enthusiast

Re: Pattern matching in case statement

Hey Fred

No, I compared the lengths and its matching both the sides. No issues with either small/capital case or trailing spaces.

Junior Apprentice

Re: Pattern matching in case statement

Hi,

 

Thanks for checking those items.

 

Assuming that you're talking about functionality and not performance...

I have never come across issues with the CASE expression - and I use it a lot in my code against all sizes of tables.

Yes, you might have hit a bug but I doubt if there is anything fundamentally wrong in this area (bear in mind that Teradata customers have always had huge tables).

 

Could you please show all of your code? All we've seen here is part of the CASE expression.

 

I'm particularly interested in the WHERE clause and the preceding WHEN clauses.

 

One other question (and this may just be a different use of words). You refer to this as 'pattern matching' - which is not how I would have described this processing. To me, 'pattern matching' would require the use of LIKE or a regex function. What you're doing here is (to me) a simple comparison of data values. Probably not important but I just thought I'd ask.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Pattern matching in case statement

Hey Will

 

Entire code is too big, with more CASE columns, might not make any sense. So here is my case statement(search with 'LKUP_REF_skj' to get to my point of concern):

SyntaxEditor Code Snippet

/*  ( sls_chnl_type,chnl_cd )  Order of preference :
     *  1. Ecommerce (DE) 2. CALL CENTER (DC/DT/IT)3. Agent (IA/IP) 4. EPremier (DP) 5. Partner (ID) 
     *  6. Direct Account Exec (DA)  7.  Global Accounts (DG)  8. Major Account Manager (DM)
     *  9. BYPASSED CHANNEL(DR) 10. Other
     */
        ,CASE 
           WHEN ( m2.agnt_cd IS NOT NULL 
                   AND (m2.chnl_cd1 = 'DE'  OR m2.chnl_cd2 = 'DE' OR m2.chnl_cd3 = 'DE'))
             THEN  'e-Ecomm'
           WHEN ( m2.agnt_cd IS NOT NULL 
                  AND (m2.chnl_cd1 IN ( 'DC', 'DT')  OR m2.chnl_cd2 IN ( 'DC', 'DT') OR m2.chnl_cd3 IN  ('DC', 'DT')))
             THEN 'CALL CENTER'
                 
                  WHEN ( m2.agnt_cd IS NOT NULL 
                  AND (m2.chnl_cd1 IN ('IT')  OR m2.chnl_cd2 IN ( 'IT')  OR m2.chnl_cd3 IN  ('IT')))
             THEN 'IT- Affiliate'
           WHEN  ( m2.agnt_cd IS NOT NULL 
                   AND (m2.chnl_cd1 IN ('IA', 'IP')  OR m2.chnl_cd2 IN ('IA', 'IP') OR m2.chnl_cd3 IN ('IA', 'IP') ))
             THEN  'AGENT'
           WHEN ( m2.agnt_cd IS NOT NULL 
                   AND (m2.chnl_cd1 = 'DP'  OR m2.chnl_cd2 = 'DP' OR m2.chnl_cd3 = 'DP'))
         THEN 'e-Premier'

           WHEN m2.agnt_cd IS NOT NULL 
                 AND (m2.chnl_cd1 = 'ID'  OR m2.chnl_cd2 = 'ID' OR m2.chnl_cd3 = 'ID')
             THEN  'Partner'

            WHEN ( 
                m2.agnt_cd IS NOT NULL 
                AND (m2.chnl_cd1 = 'DA'  OR m2.chnl_cd2 = 'DA' OR m2.chnl_cd3 = 'DA')
                )
            THEN 'DIRECT ACCOUNT EXEC'
            
            WHEN ( 
                m2.agnt_cd IS NOT NULL 
                AND (m2.chnl_cd1 = 'DG'  OR m2.chnl_cd2 = 'DG' OR m2.chnl_cd3 = 'DG')
                )
            THEN 'GLOBAL ACCOUNTS'
            
            WHEN ( 
                m2.agnt_cd IS NOT NULL 
                AND (m2.chnl_cd1 = 'DM'  OR m2.chnl_cd2 = 'DM' OR m2.chnl_cd3 = 'DM')
                )
            THEN 'MAJOR ACCOUNT MANAGER'
            
      WHEN (
            m2.agnt_cd IS NOT NULL 
            and  (m2.agnt_cd||'~'||m2.chnl_cd1)=(select    lkup_txt     from    SLSOPSDB.LKUP_REF_skj where    lkup_1_txt    ='OMNI Report code bypass valve')
            AND (m2.chnl_cd1 = 'DR'  OR m2.chnl_cd2 = 'DR' OR m2.chnl_cd3 = 'DR')
            )
        THEN 'BYPASSED CHANNEL'        

           WHEN  b1.sls_chnl_grp_nm IS NOT NULL 
             THEN b1.sls_chnl_grp_nm

           ELSE 'Other'
       END AS sls_chnl_type

 

I'm calling it as pattern matching as I'm matching Agent 'XBNW4' with Channel 'DR' with a column having 'XBNW4~DR' in my Lookup. Yes, it may be a appear a different choice of words. Basically , we use this lookup table as a work-around to avoid any hard-coding of Agents in main table Bteqs.

Junior Apprentice

Re: Pattern matching in case statement

Hi,

 

Thanks for that. I can't see anything wrong with your logic.

 

Do you have a WHERE clause and/or join condition(s) which might eliminate the rows that you're expecting to be returned?

 

If not then I'd raise an incident on TaYS and see if you've hit a bug in TD. What release of TD are you using?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Pattern matching in case statement

Hi Dave

 

Yes, I'm re-checking the joins to find out if my records are basically getting filtered. Please dont open a ticket as of yet.

Using Rel 15.00

 

Thanks!