Generate a automated Nested CASE STATEMENT using entries from a table

Analytics
Enthusiast

Generate a automated Nested CASE STATEMENT using entries from a table

I have a situation where i am trying to automate a BTEQ script where it has 100ds of conditional statements like 

CASE WHEN

WHEN

CASE WHEN

 WHEN

CASE WHEN

 WHEN

END

WHEN 

END

WHEN

WHEN

END AS COLUMN1. 

like this i have 100 different columns. Now i need help where have to design a table such a way that i will insert Ex. COLUMN1, "WHEN condition statement" and hierarchy kind of design.

Finally I need to write a SQL query (Ex. recursive query) that will generate above CASE Statement. The nested CASE Statements varies depends on the need 1,2,3,4,5 the level of conditions. Which i will export to a unix file and call that file into actual BTEQ script. I am not sure if i putting it write way of my question. 

Thanks for your help

10 REPLIES
Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

I think you are looking for an opportunity to automate the process. I see people get faster response by showing sample , this is what I have and this is the output required. The sentence hierarchy  kind of design is vague for me.

I think you are looking to export the conditions to a file(s) and then import them correspondingly. This link can provide you direction. Please check the syntax.

http://forums.teradata.com/forum/tools/help-needed-on-bteq-exportimport-option .

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Thanks Raja for your quick response. So ignore all the export and import that’s the last step. The actual challenge is generating a INLINE CASE STATEMENT using the data from a table where I need to design a table say like lookup table.

I have a BTEQ script process with lots of CREATE SELECT queries which has CASE Statements like below. We need to change (add or remove) frequently the conditional values say like LIKE '%(ipad%' to '%ipadmini%'. which is becoming difficult to maintain and planning to move to production where we don't have control on the script. Except a Teradata table which I need to design such a way that the below CASE STMT is generated by using the values from a table. Once if I can generate this case statement i will then export to a file and import back using UNIX variables in the BTEQ script.

SELECT
CASE
WHEN (COLUMN_X LIKE ANY ('%Macintosh%', '%Mac OS%', '%ipad%', '%iphone%', '%ipod%', '%Apple %', '%powerpc%')
AND COLUMN_Y NOT LIKE ALL ( '%windows%', '%winnt%', '%linux%', '%Android%'))
THEN
CASE
--ios devices, first order sorting
WHEN COLUMN_X LIKE '%(ipad%' THEN 'ipad'
WHEN COLUMN_X LIKE '%(iphone%' THEN 'iphone'
WHEN COLUMN_X LIKE '%(ipod%' THEN 'ipod'
WHEN COLUMN_XX LIKE '%(macintosh%' THEN 'Macintosh'
--ios devices, higher order sorting
WHEN COLUMN_X LIKE ANY ( '% ipod %', '% ipod;%') THEN 'ipod'
WHEN COLUMN_X LIKE ANY ('% ipad %', '% ipad;%') THEN 'ipad'
WHEN COLUMN_Z LIKE ANY ( '% iphone %', '% iphone;%') THEN 'iphone'
WHEN COLUMN_Z LIKE ANY ('% macintos''h %', '% macintosh;%', '%powerpc%') THEN 'Macintosh'
WHEN COLUMN_X LIKE '%mobile%' THEN 'iphone'
ELSE 'Macintosh'
END
WHEN COLUMN_X LIKE ANY ('%nintendo%','%xbox%', '%play%station%') THEN 'Game'
WHEN COLUMN_X LIKE ANY ('%windows%', '% pc %', '%winnt%') THEN
CASE
WHEN COLUMN_X LIKE '%tablet %' THEN 'win_tablet'
WHEN COLUMN_X LIKE ANY ('%windows phone%', '%windows ce%') THEN 'win_phone'
ELSE 'win_pc'
END
WHEN COLUMN_X LIKE '%Android%' THEN
CASE
WHEN COLUMN_X LIKE '%mobile%' THEN 'android_mobile'
WHEN COLUMN_X LIKE '%tablet%' THEN 'android_Tablet'
ELSE 'android_Tablet'
END
WHEN COLUMN_X LIKE '%BB10%' THEN 'Blackberry_Mobile'
WHEN COLUMN_X LIKE '%Black%Berry%' THEN 'Blackberry_Mobile'
WHEN COLUMN_X LIKE '%PlayBook%' THEN 'Blackberry_Tablet'
WHEN COLUMN_Y LIKE '%linux%' THEN 'linux'
WHEN COLUMN_Z LIKE '%cros%' THEN 'chrome'
ELSE COLUMN_X
END AS COLUMN_ABC
FROM TABLE1

Here are my thougths to design a lookup table

TABLE_NAME , COLUMN_NAME, WHERE_CONDITION_VALUES

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

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE '%(ipad%' THEN 'ipad'

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE '%(iphone%' THEN 'iphone'

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE '%(ipod%' THEN 'ipod'

TABLE1, COLUMN_ABC, WHEN COLUMN_XX LIKE '%(macintosh%' THEN 'Macintosh'

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE ANY ( '% ipod %', '% ipod;%') THEN 'ipod'

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE ANY ('% ipad %', '% ipad;%') THEN 'ipad'                                             

TABLE1, COLUMN_ABC, WHEN COLUMN_Z LIKE ANY ( '% iphone %', '% iphone;%') THEN 'iphone'                                           

TABLE1, COLUMN_ABC, WHEN COLUMN_Z LIKE ANY ('% macintos''h %', '% macintosh;%', '%powerpc%') THEN 'Macintosh'                                          

TABLE1, COLUMN_ABC, WHEN COLUMN_X LIKE '%mobile%' THEN 'iphone'                                       

TABLE1, COLUMN_ABC, ELSE 'Macintosh'

Then write an SQL statement such way that it generates the CASE STEMENT. Challange is we have Nested CASE statemetents and how to capture them.

Any help would be greatly appreciated. 

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Any help would be greatly appreciated.

Teradata Employee

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Perhaps a different way to think about it...

It is possible to have LIKE refer to a column for the pattern. This allows you to put the patterns in a table and join to them. You can put your substitution text in another column of the pattern table, pulling it in with the join.

You can of course build any SQL text you want prodcedurally in a stored procedure and execute it. Again you could store the patterns in a table and have the SP read it and build the SQL. Or you could write a program in your favorite language or scripting tool to do the same.

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

I wrote a script in python that solves your problem.  I had the same one.  its on my github. you will not be able to use it unless you have semantic programming knowledge.  I am going to turn it into a web application soon.  So that anyone can use it.  I have just been busy.

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Thats great. I have some good knowledge of programming. If you can pass me on your logic i will work from their this i something i need it to move further. Appreciate your help.

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Hey Justin, I have created my account in github. can you guide me where to look for your solution. Appreciate your help.

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

you actually do not need an account for github, but you will need to do some tweaking to the code once you have it .  My computer where the code was located died 5 days ago I just got it fixed yesterday.  Ill push the code and post the link by the end of the weekend.  You will need some way to run Python programs (ie. download python for your machine.)  The code is written for version 2.7.2 of python so I would recommend downloading that.

Cheers,

Justin

Enthusiast

Re: Generate a automated Nested CASE STATEMENT using entries from a table

Great, Let me know when you push the code and the link. I know i will have questions though.

-R