Select first occurance of value
Hello All, I have 10 integer columns of a big table col1|col2|col3|col4|col5|col6|col7|col8|col9|col10| values 10|2|1|4|90|70|90|88|21|90| How can i select the column with first occurance having value 90, print the value 90 & the column name (col5 in above example) with out using an IF or CASE statement I have written an IF THEN statement and it works but If there is a better and efficient way of doing this as i might have to do this on millions of rows Regards,
This sounds like a very bad schema design to me. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 28, 2013 at 3:21 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hello All, > > I have 10 integer columns of a big table > > col1|col2|col3|col4|col5|col6|col7|col8|col9|col10| values > 10|2|1|4|90|70|90|88|21|90| > > How can i select the column with first occurance having value 90, print the > value 90 & the column name (col5 in above example) with out using an IF or > CASE statement > > I have written an IF THEN statement and it works but If there is a better > and > efficient way of doing this as i might have to do this on millions of rows > > Regards, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c29b44d8897104e4ffdf0c
Hi Art, I think the requirement is bad as the schema is been there for years and serving our OLTP environment nicely however the query raise by me is a part of OLAP requirements. Thanks. >This sounds like a very bad schema design to me. > >Art > >Art S. Kagel >Advanced DataTools (www.advancedatatools.com) >Blog: http://informix-myview.blogspot.com/ > >Disclaimer: Please keep in mind that my own opinions are my own opinions >and do not reflect on my employer, Advanced DataTools, the IIUG, nor any >other organization with which I am associated either explicitly, >implicitly, or by inference. Neither do those opinions reflect those of >other individuals affiliated with any entity with which I am affiliated nor >those of the entities themselves. > Hello All, > > I have 10 integer columns of a big table > > col1|col2|col3|col4|col5|col6|col7|col8|col9|col10| values > 10|2|1|4|90|70|90|88|21|90| > > How can i select the column with first occurance having value 90, print the > value 90 & the column name (col5 in above example) with out using an IF or > CASE statement > > I have written an IF THEN statement and it works but If there is a better > and > efficient way of doing this as i might have to do this on millions of rows > > Regards, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
OK, I admit I'm in an odd mood this AM. OLAP queries against an OLTP schema often get weird. I think that a CASE clause is the best you're going to do. Note that the Warehouse Accelerator would do a great job on this. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 28, 2013 at 7:41 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hi Art, > > I think the requirement is bad as the schema is been there for years and > serving our OLTP environment nicely however the query raise by me is a > part of > OLAP requirements. > > Thanks. > > >This sounds like a very bad schema design to me. > > > >Art > > > >Art S. Kagel > >Advanced DataTools (www.advancedatatools.com) > >Blog: http://informix-myview.blogspot.com/ > > > >Disclaimer: Please keep in mind that my own opinions are my own opinions > >and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > >other organization with which I am associated either explicitly, > >implicitly, or by inference. Neither do those opinions reflect those of > >other individuals affiliated with any entity with which I am affiliated > nor > >those of the entities themselves. > > > Hello All, > > > > I have 10 integer columns of a big table > > > > col1|col2|col3|col4|col5|col6|col7|col8|col9|col10| values > > 10|2|1|4|90|70|90|88|21|90| > > > > How can i select the column with first occurance having value 90, print > the > > value 90 & the column name (col5 in above example) with out using an IF > or > > CASE statement > > > > I have written an IF THEN statement and it works but If there is a better > > and > > efficient way of doing this as i might have to do this on millions of > rows > > > > Regards, > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9473c2be5439404e501b3f3