Reverse SQL Select

[rmahawaii]

Hello, I need to select around 300 fields from and 303 fields table. Now, I don't want to type down all the 300 fields in my select statement. How can I omit the 3 fields I don't want with the SELECT statement? thanks in advance.

 

[r937]

create a view

yes, you will have to type the 300 column names one time

after that, though, you can happily write SELECT *


rudy

 

[Kavius]

[tt]
Name
^^^^^^^^
Bob <---------- Omit
Jo <---------- Omit
Josephine
Bill
Sue
[tt]

select *
from   MyTable
where  Name not in ('Bob','Jo')

---------------------------------------
Where would one begin to study Myology?

 

[rmahawaii]

kavius,
What I don't want is the entire field not just the records. Thanks anyway.

Ronald

 

[Kavius]

Sorry, misread your problem. ---------------------------------------
Where would one begin to study Myology?

 

[emblem]

It should be possible to write a subquery against a system catalog table to build your list of 300 fields automatically.

Here is something that should work in Informix:

SELECT '(
SELECT FIELDNAME||',' FROM PSRECFIELD WHERE RECNAME = 'JOB' AND FIELDNAME NOT in <list of unwanted fields & last field>
)',last_field
FROM PS_JOB WHERE EMPLID ='KU0010'

The inner select works just fine. Unfortunately, Informix cannot recognize the outer quotes, which should turn multiple rows into one long string.

I've also tried to do this in SQLServer (using + for concat operator) but it has the same problem

I don't have an Oracle platform handy, but I think it will work on 8i. Replace the outer ' with ''' or maybe even with ''''

As you can tell, I'm working with a PeopleSoft DB. You can replace PSRECFIELD with SYSCOLUMNS or dba_tab_columns or what-have-you as appropriate

Happy SQL coding!

 

[Kavius]

This is the view I use for grabbing column and table names. I know this not the exact thing you are looking for, but it does show the view you can grab column names from.

create view columns as
select TOP 100 PERCENT
  left(Table_Name,20) as Tab,
  left(Column_Name,20) as Col,
  left(Data_Type + CASE
                     WHEN CHARACTER_MAXIMUM_LENGTH IS NULL
                     THEN ''
                     ELSE '(' + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ')'

                   END
  ,20) as Type,
  IS_Nullable as Nullable
from information_schema.columns
order by Tab
go

________________________________________
Nature and Nature's laws lay hid in night
God said &quot;Let Newton be&quot;, and all was light
~Alexander Pope~