All Forums UDA
vels_info 1 post Joined 03/16
25 Mar 2016
Split delimited column into separate rows dynamically

I have code as below,
sel * from testdb.maintable WHERE STATUS not in (10,11,13,16,17,20,21,23,26,27);
I need to remove the above hard coded status values and parametrize this piece of code. 
To parametrize, I'm using another parm table below where I have the above status values put it. 
Here, I have varchar(255) column in parm table where I inserted the above statuses with comma delimited value. Below the insert I used. 
INSERT INTO testdb.parmtable VALUES ('STATUS', '10,11,13,16,17,20,21,23,26,27');
1. How do I delimit it, assuming in future I might add some more status there. Example like 28,29,30.. etc
2. At the same time, I need to avoid sub-select. This needs to be done in 2 steps. In First step unstring the above values and store it in temporary fields and pass these temporary fields to main query in second step.
Please advise logic to do this. You effort will be very much appreciated.

You must sign in to leave a comment.