EASY SQL QUESTION

Anon

i need a way to:

select * from table where all fields are not null;

how can i specify all fields in the select statement? i tried using a * but that didn't work.

thanks,
Josh.

Anon

You have to list each one, you can only use * when choosing which columns to select.

SELECT * FROM your_table WHERE field1=null or field2=null or field3=null;

---John Holmes...

Anon

actually, i should have been more specific. that won\'t work. i\'m going to be pulling data from any of a number of tables. so i won\'t know what fields i have to work with,...therefore i can\'t name them in my sql statement.

any ideas?
josh.

John Holmes wrote:

You have to list each one, you can only use * when choosing which columns to select.

SELECT * FROM your_table WHERE field1=null or field2=null or field3=null;

---John Holmes...

Anon

You have to eventually know the names, or have the assigned to a variable...right? Otherwise you can't do anything.

You can use variable names for the table and fieldnames.

SELECT * FROM $table WHERE $field[0]=null..etc..

Will that work for you?

---John Holmes...

Anon

well, not really in this case. let me explain what i'm doing:

i have an oracle database full of tables. i need to move the tables and associated data to a mysql instance. i'm writing a script to do all of this semi-automatically.

in order to find the column types for the oracle tables i'm querying: "select * from ora_table". i fetch a row into an array, then i can use the ora_ColumnType( ) (and ora_ColumnSize( ) and ora_ColumnName( )) function to get the info I need to construct a similar table in mysql.

the problem is that some of the fields in some of the oracle tables have null values. therefore, when i pull a row from the oracle cursor that i queried on, i don't get values (hence i don't get slots in my array for that column so i can't get ColumnType, etc). if i could just make sure to get a row with NO null values (assuming one such row exists in each of the tables in question) then i would have no problems.

hope that makes sense.
thanks,
Josh.

John Holmes wrote:

You have to eventually know the names, or have the assigned to a variable...right? Otherwise you can't do anything.

You can use variable names for the table and fieldnames.

SELECT * FROM $table WHERE $field[0]=null..etc..

Will that work for you?

---John Holmes...

Anon

I know MySQL has a "show columns from table" command that you can issue to get all of the column types. It seems like Oracle would have something similar, but I don't know anything about Oracle. Sorry.

---John Holmes...