I agree to Idea View column position - easy identification
Voting Disabled

52 votes

I disagree to Idea View column position - easy identification

Rank12

Idea#381

This idea is active.
Editor »

View column position - easy identification

Easy way to match the column position in a view definition to the selected column. For large views, it is often difficult/time consuming to find out which column in the view definition corresponds to which column in the select statement. This usually involves counting its position in the definition and then counting down the same number in the select. Not a problem on small views but on some of the larger ones (particuarly in apps - mtl_system_items_vl selects 341 columns!) this can be an annoying and can often lead to picking the wrong column when the selected columns aliases don't correspond to the definition column names, or there are several subqueries.

I think a useful addition would be that if you place your cursor on a column alias in a view definition then by pressing some key (ctrl etc), the corresponding column/inline select is highlighted in the select statement.

eg:

CREATE VIEW v (c1,c2,c3,c4,c5) AS

SELECT x.y,x.z, a.b, a.c, a.d FROM t;

To, if I placed my cursor next to c3 above then something would indicate to me that a.c was the corresponding column. Perhaps colouring the background, highlighting etc...

Submitted by jkeymer 3 years ago

Attachments Show

Comments (11)

  1. Although, I think that this has potential of adding overhead to the editor to do it for every column you might put your cursor on. It might be easier to have a keystroke invoke that.

    This would be good for INSERT/VALUE statements, too.

    3 years ago
    0 Agreed
    0 Disagreed
  2. 3 years ago
    0 Agreed
    0 Disagreed
  3. jkeymer Idea Submitter

    Yes, I agree I certainly wouldn't want it to do it just by the act of clicking a column however some shortcut key/modifier such as an F button, ctrl, alt etc might work? That would be up to the HCI guys! :)

    3 years ago
    0 Agreed
    0 Disagreed
  4. Even we have same kind of need but we do it manually using some Text editor and excel to format sequencing as below.

    insert into xyz (

    col_1, /* 1 */

    col_2, /* 2 */

    col_2_new, /* 2.1 */

    ...

    col_300, /* 300 */

    as select

    col_1, /* 1 */

    col_2, /* 2 */

    col_2_new, /* 2.1 Number in decimal if added at later stage*/

    ...

    col_300, /* 300 */

    from abc

    2 years ago
    0 Agreed
    0 Disagreed
  5. I'm voting for this request; I can see it's utility as an ideal solution.

    However, I will point out that column aliasing would help a great deal and at least eliminate the need to count to match the column definition and SELECT columm:

    CREATE VIEW v (

    c1

    ,c2

    ,c3

    ,c4

    ,c5) AS

    SELECT x.y AS c1

    ,x.z AS c2

    , a.b AS c3

    , a.c AS c4

    , a.d AS c5

    FROM t;

    If you want to match up "c3", just search on it.

    In case it looks like extra typing, and for those who may not be aware, you don't need to create the column name definition list when you create the view. If you alias the columns in the SELECT statement, then Oracle will create the definition list for you in the source code when it creates and stores the source code in the database for the view.

    2 years ago
    0 Agreed
    0 Disagreed
    1. This is good practise in general, but the problem with this suggestion is that it tells you what columns you intended to match up rather than which columns actually are matched.

      2 years ago
      1 Agreed
      0 Disagreed
  6. This is a good idea. You can create views many ways like Community Member said.

    If idea is implemented in future, I hope that it works also with select...union...select views.

    2 years ago
    1 Agreed
    0 Disagreed
  7. jkeymer Idea Submitter

    CM - yes, that would work if you'd created the objects in house however it's not possible using 3rd party code.

    2 years ago
    0 Agreed
    0 Disagreed
  8. It could also check for columns mismatches (for unions or insert into).

    What about a vertical division, like:

    create view .... | as select

    col1 | col1

    col2 | col2

    ... | ...

    (or insert into instead of create view)

    Agreed with the shortcut activation not to add overhead.

    Of course difficult cases may arise with

    select *

    select cast()

    and the like

    1 year ago
    0 Agreed
    0 Disagreed
    1. oops, vertical division is not displayed as intended.

      I hope you get the idea.

      I suggest a division, insert/view on the left, query on the right

      right side may be toggled between the various unions clauses

      difficult cases may arise also with

      functions

      procedures/packages

      special types (XML and the like)

      1 year ago
      0 Agreed
      0 Disagreed
  9. maybe code formatter could take care of that

    bonus is that columns are ordered and parsing is easier

    1 year ago
    0 Agreed
    0 Disagreed