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.
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.
WHEN (COLUMN_X LIKE ANY ('%Macintosh%', '%Mac OS%', '%ipad%', '%iphone%', '%ipod%', '%Apple %', '%powerpc%')
AND COLUMN_Y NOT LIKE ALL ( '%windows%', '%winnt%', '%linux%', '%Android%'))
--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'
WHEN COLUMN_X LIKE ANY ('%nintendo%','%xbox%', '%play%station%') THEN 'Game'
WHEN COLUMN_X LIKE ANY ('%windows%', '% pc %', '%winnt%') THEN
WHEN COLUMN_X LIKE '%tablet %' THEN 'win_tablet'
WHEN COLUMN_X LIKE ANY ('%windows phone%', '%windows ce%') THEN 'win_phone'
WHEN COLUMN_X LIKE '%Android%' THEN
WHEN COLUMN_X LIKE '%mobile%' THEN 'android_mobile'
WHEN COLUMN_X LIKE '%tablet%' THEN 'android_Tablet'
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'
END AS COLUMN_ABC
Here are my thougths to design a lookup table
TABLE_NAME , COLUMN_NAME, WHERE_CONDITION_VALUES
---------------- ------------------- -------------------------------------
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.
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.
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.
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.
Hey Justin, I have created my account in github. can you guide me where to look for your solution. Appreciate your help.
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.
Great, Let me know when you push the code and the link. I know i will have questions though.