Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    5

    Unanswered: Finding Data in Oracle table

    Hello Colleagues,

    I have some questions:
    1)How can I find data in an Oracle table and select it, but only the cell with this data to select not the entire row? And how can I select the next neighbour cell of this cell?
    2)Is it possible to delete all columns from the table which don't have any data?

    Thank You for supporting me
    Vasil Nikolov

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is the Oracle Documentation page. Select SQL Reference guide and read it, especially SELECT statement chapter.

    You'd select one column by specifying it:
    Code:
    SELECT ename FROM emp;
    What do you call "the next neighbour cell"?

    Columns that don't contain data are empty - have NULL value. I guess you'd like to drop them (not delete). To do so, use
    Code:
    ALTER TABLE emp DROP ename;

  3. #3
    Join Date
    Jul 2006
    Posts
    49
    1. To see a cell you must specify both a column and row identifier (x,y coordinates). Relational databases are great matricies using the principles of linear algebra. Here's a typical 4GL SQL statement:
    SELECT COLNAME FROM TABNAME WHERE CONDITION;
    You replace COLNAME with a comma delimited list of column names in order to define your X axis points. You replace CONDITION with something to identify the rows you want, or Y axis points.
    For example,
    SELECT NAME FROM DEPARTMENTS WHERE CODE = 'SALES';
    or,
    SELECT NAME FROM DEPARTMENTS WHERE DEPT_ID IS NOT 17;

    2. A row can have many columns, so to delete a row with "no data" you must test each column for data and only delete rows where every column is null. Recall CONDITION from above: in SQL we use the WHERE clause to set conditions. For example,
    DELETE FROM DEPARTMENTS WHERE DEPT_ID IS NULL AND CODE IS NULL AND NAME IS NULL AND LAST_UPDATED_TIME IS NULL AND .....;
    In general never delete without a WHERE clause, because without a WHERE clause Oracle deletes all rows in the table. If you do this by accident, then immediately issue the command ROLLBACK and Oracle will undelete the data. Once you do a COMMIT it is really deleted.
    The TRUNCATE TABLE X command also deletes all rows, whether empty or not, and it does an auto-commit at the end so there is no going back.

    I hope that helps,

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by remidata
    SELECT NAME FROM DEPARTMENTS WHERE DEPT_ID IS NOT 17;
    Which Oracle version supports this syntax? I'm familiar with "DEPT_ID IS NOT NULL", but I've never heard of "IS NOT 17".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •