Forum Stats

  • 3,838,461 Users
  • 2,262,366 Discussions
  • 7,900,654 Comments

Discussions

SQL Query Help

User_BU3NG
User_BU3NG Member Posts: 116 Blue Ribbon
edited Dec 15, 2015 1:33AM in SQLとPL/SQL

@Need a help in writing oracle query

I have a table Test with following information

DIM1DIM2DIM3DIM4DIM5DIM6SM1SM2SM3SM4SM5SM6
ABCDEF123456
ABCDEF222222

I would need data with sum row wise, I wrote this query it works.

select DIM1, DIM2,DIM3,DIM4,DIM5,DIM6,(SM1+SM2+SM3+SM4+SM5+SM6) from  Test


But my requirement in table columns might increase, hence I would need this query to be dynamic, it should work though any additions or deletions in columns with out changing query.We are using Oracle 11g.


Thanks in advance.





Answers

  • Jagadekara
    Jagadekara Member Posts: 1,860 Gold Trophy
    edited Dec 15, 2015 1:33AM

    Hi,

    You don't know how many columns, so you must use all_tab_cols table to bring cols dynamically.

    you may need like this...

    select column_name from all_tab_cols where table_name='YOUR_TABLE_NAME' and data_type='NUMBER', so it will display all columns with number data type.

    Note that, it may not be the correct answer, but it may help you to give at least an idea.

This discussion has been closed.