0

I have a table with a massive number of columns. So many, that when I do SELECT * I can't even see any values because all the columns fill up the screen. I'd like to do something like this:

SELECT * FROM my_table WHERE NAME LIKE '%unique name%' AND <THIS COLUMN> IS NOT NULL

Is this possible? Note: VALUE is not a column.

There are so many questions on SO that ask this same question, but they have some bizarre twist, and the actual question is not answered.

I've tried:

SELECT * FROM my_table WHERE NAME LIKE '%unique name%' AND VALUE NOT NULL
                                                                     *
Invalid relational operator

SELECT * FROM my_table WHERE NAME LIKE '%unique name%' AND VALUE <> ''
                                                           *
'VALUE': invalid identifier

SELECT * FROM my_table WHERE NAME LIKE '%unique name%' AND COLUMN NOT NULL
                                                           *
Missing Expression

Bonus Questions:

  1. Is there any way to force Oracle to only show one output screen at a time?
  2. Is there a variable to use in the WHERE clause that relates to the current column? Such as: WHERE this.column = '1', where it would check each column to match that expression?
  3. Is there any way to get back your last command in Oracle? (I have to remote into a Linux box running Oracle - it's all command line - can't even copy/paste, so I have to type every command by hand, with a wonky connection, so it's taking an extremely long time to debug this stuff)
Improve this question
5
  • 1
    You could create some views for the more frequent set of columns .. Commented Nov 22, 2016 at 21:11
  • I'm not quite sure that I understand what you are looking for. Is my_table in this example a table with a column name and another column value? Or are you trying to do something where name is the name of each column in the table and value is the value in that column? So if I have a table with hundreds of columns, I am dynamically picking the columns I want to read and only displaying rows where at least one of those columns has a non-NULL value? Commented Nov 22, 2016 at 21:30
  • VALUE is not a column. NAME is a column. The name in the LIKE expression is unique enough to only return one row. However, it returns too many columns, they fill up the entire screen without showing any values. Commented Nov 22, 2016 at 21:36
  • Why not just select the column(s) you are interested in instead of all columns: SELECT name, col1 FROM my_table WHERE NAME LIKE '%unique name%' AND col1 IS NOT NULL Commented Nov 22, 2016 at 23:45
  • are you allowed to create procedures on that DB? you could create one that takes the table name and the where condition as input parameters, iterates over the table schema's columns, executes a select per column, and dbms_output-prints the column name and value if it is non-null. bonus question 2: no, there is no notion of a "current column" in the where clause. Commented Nov 23, 2016 at 0:14

2 Answers 2

Reset to default
2

If you are trying to find all the non null column values for a particular record you could try an unpivot provided all the columns you are unpivoting have the same data type:

 SELECT *
   FROM (select * from my_table where name like '%unique value%')
UNPIVOT [include nulls] (col_value FOR col_name IN (col1, col2, ..., coln))

with the above code null values will be excluded unless you include the optional include nulls statement, also you will need to explicitly list each column you want unpivoted.

If they don't all have the same data type, you can use a variation that doesn't necessarily prune away all the null values:

select * 
  from (select * from my_table where name like '%unique value%')
  unpivot ((str_val, num_val, date_val)
  for col_name in ((cola, col1, date1)
                  ,(colb, col2, date2)
                  ,(colc, col3, date1)));

You can have a fairly large set of column groups, though here I'm showing just three, one for each major data type, with the IN list you need to have a column listed for each column in your column group, though you can reuse columns as shown by the date_val column where I've used date1 twice. As an alternative to reusing an existing column, you could use a dummy column with a null value:

select * 
  from (select t1.*, null dummy from my_table t1 where name like '%unique value%')
  unpivot ((str_val, num_val, date_val)
  for col_name in ((dummy, col1, date1)
                  ,(colb, dummy, date2)
                  ,(colc, col3, dummy)));
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, unfortunately the columns have different types.
0

Have tried this?

SELECT * FROM my_table WHERE NAME LIKE '%unique name%' AND value IS NOT NULL;

Oracle / PLSQL: IS NOT NULL Condition

For row number:

SELECT field1, field2, ROW_NUMBER() OVER (PARTITION BY unique_field) R WHERE R=1;

Usually in Linux consoles you can use arrow up&down to repeat the last sentence.

Improve this answer

5 Comments

Linux consoles use up/down, Oracle does not. It pastes (what I'm guessing to be) the keyboard representation of the up arrow - something like [[^A.
Also, value is not a column. I was simply showing that to demonstrate what I'm looking for.
I see, the question is that Oracle syntax for not null values is: IS NOT NULL,
Right, but how do I apply that to all columns?
Something like WHERE ALL IS NOT NULL? I don't know any DB with this functionality. stackoverflow.com/questions/3108262/where-all-is-not-null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.