9 Replies Latest reply on Jun 20, 2018 1:01 PM by 2887941

    Select a Table-But exclude one Column

    Ofentse Masike Sepeng

      How to exclude one column from result?

        • 1. Re: Select a Table-But exclude one Column

          Yes there is a query provided by Oracle.

          But, see, this thing is good if your column have very large values, consider that you have a column which have very large(large in numbers not length) number of values, and suppose you want to make that column excluded from the result because suppose there is heavy traffic of users on that table, and you thought that better to make that column excluded from the list for some time and when the traffic of user becomes less, then you can delete that column from the table.

          That feature is called SET COLUMN UNUSED , this is the query->


          Alter table myTable

          SET Unused (Column_name)


          That's it.


          Now you can think that, that column is deleted from the table, and yes it acts like it is deleted and you can create a new column of same name(which is being unused), that's no problem. And, you can see the unused columns list from this query-> USER_UNUSED_COL_TABS

          But there is a problem with this query->
          You can't make any DML Statements while making that column UNUSED, so to make DML Statements, while making that column unused, you need to slightly change you query like this->


          Alter table myTable

          SET Unused (Column_name)  ONLINE // it allow DML Operations while making that column unused.


          Hope this Help

          • 2. Re: Select a Table-But exclude one Column
            Apoorva S-Oracle

            Kindly use the following space to post questions:SQL Fundamentals (2018)


            DO NOT post in the following old space: SQL Fundamentals



            -The Oracle MOOC Team

            • 3. Re: Select a Table-But exclude one Column
              Apoorva S-Oracle



              I am assuming your question is "How to exclude one column while displaying all other columns in a report?"


              Answer: The easiest option is for you to mention all the remaining columns in the SELECT clause.


              For example,


              SELECT col1, col2, col3 .....

              FROM table_name;

              1 person found this helpful
              • 4. Re: Select a Table-But exclude one Column
                Ofentse Masike Sepeng

                Thank you, i shall do that.

                • 5. Re: Select a Table-But exclude one Column
                  Ofentse Masike Sepeng

                  Besides this option is there any other way?


                  I was hoping maybe there would be something like:

                  SELECT *

                     EXCEPT COMMISSION_PCT

                  FROM EMPLOYEES.


                  i ma fairly new when it comes to SQL, so am just trying to get my head around what would be deemed logical way to do it.

                  • 6. Re: Select a Table-But exclude one Column
                    Apoorva S-Oracle

                    That would be a convenient option but there is no way to do that.


                    The other solution maybe to create a temporary table or a view without the column you want to include as :


                    CREATE TABLE temp_table

                    AS SELECT col1, col2....

                    FROM table_name;

                    • 7. Re: Select a Table-But exclude one Column
                      Danilo Piazzalunga

                      That would indeed be very convenient.


                      Ofentse Masike Sepeng, you could try submitting this as a new idea in  Database Ideas.

                      • 8. Re: Select a Table-But exclude one Column
                        William Robertson

                        Agreed, this is a valid thing to do from a relational theory perspective, but not supported by any SQL implementation as far as I know.


                        See S { ALL BUT columnname } in Tutorial D: http://kurttest.com/cjdate/cjdatenotes.html

                        • 9. Re: Select a Table-But exclude one Column

                          Cross posting from https://stackoverflow.com/questions/9133120/can-you-select-everything-but-1-or-2-fields-without-writers-cramp/33220953#3…


                          An old thread but, yes... there is a way to do it in Oracle:

                          with  employee(id, firstname, lastname, hobbies) as ( select 1, 'a', 'b', '1' from dual union select 2, 'a', 'b', '2' from dual union select 3, 'a', 'b', '3' from dual union select 4, 'c', 'd', '3' from dual union select 5, 'e', 'f', '2' from dual )  select * from employee pivot ( max(1) -- fake   for (hobbies) -- put the undesired columns here IN () -- no values here... ) where 1=1 -- and your filters here... order by id

                          To understand how the PIVOT works and why it solves the question, lets take a better example for our employee sample table:

                          select * from employee pivot ( max(id) foo, max(1)  bar for (hobbies) IN ('2' as two, '3' as three) )

                          The result here is:

                          FIRSTNAME | LASTNAME | TWO_FOO | TWO_BAR | THREE_FOO | THREE_BAR c          d         null      null        4           1 e          f           5        1         null        null a          b           2        1          3           1 

                          The exact same output can be achieved using this easier to understand query:

                          select firstname, lastname, max(case when hobbies = '2' then id end) two_foo, max(case when hobbies = '2' then 1  end) two_bar, max(case when hobbies = '3' then id end) three_foo, max(case when hobbies = '3' then 1  end) three_bar from employee group by firstname, lastname

                          So, the column hobbies is never selected, just as the column id, both specified inside the PIVOTclause. All other columns are grouped and selected.

                          Well, returning to the first query, it works for two reasons:
                          1- you will not lose any row in the grouping process because the id column is unique and no columns were specified for aggregations;
                          2- as the pivot generates N * M new columns, where N = number of values of the IN clause and M = number of aggregations specified, so having no filters and that single harmless aggregation will produce 0 * 1 = 0 new columns and will remove the ones specified in the PIVOT clause, which is just the hobbies.

                          ANSWER TO COMMENT 1

                          The first line of this question says: "... without having to specify the fields you want". In all other answers the proposed queries specifies the desired fields in the SELECT clause, except in mine, actually.

                          Also, in the question title says "... without writer's cramp". Well, what's the correct measure to identify a writer's cramp? My best effort would be to foresee a good SQL standard to this problem and compare with my answer. Actually, I think this "standard" could be something like SELECT * NOT IN ([col1], [col2], ...).

                          Now, I can see in both queries:

                          • a list of undesired columns;
                          • an IN clause;
                          • a three characters clause - FOR and NOT;

                          It means that you need to write a bit more in my approach as you need a fake aggregation and the PIVOT clause... but it's really few characters more...