Select first occurance of value
Vikas Hivarkar wanted the first of 10 integer columns equal to 90, with its column name, avoiding IF or CASE across millions of rows. Art Kagel called it poor schema design. Vikas said the schema is a long-standing OLTP one and this is an OLAP need. Art concluded CASE is the best option and noted Warehouse Accelerator would handle it well.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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