Replace one column in a table with a column in a different table and select *

Question

Apologies for the somewhat confusing Title, I've been struggling to find an answer to my question, partly because it's hard to concisely describe it in the title line or come up with a good search string for it. Anyhoooo, here's the problem I'm facing:

Short version of the question is: How can I write the following (invalid but understandable SQL) in valid SQL understood by Oracle:

select B.REPLACER as COL, A.* except A.COL from A join B on a.COL = B.COL;

Here's the long version (if you already know what I want from reading the short version, you don't need to read this :P ):

My (simplified) task is to come up with service that massages a table's data and provide it as a sub-query. The table has a lot of columns (a few dozens or more), and I am stuck with using "select *" rather than explicitly listing out all columns one by one, because new columns may be added to or removed from the table without me knowing, although my downstream systems will know and adjust accordingly.

Say, this table (let's call it Table A from now on) has a column called "COL", and we need to replace the values in that COL with the value in the REPLACER column of table B where the two COL value matches.

How do I do this? I cannot rename the column because the downstream systems expect "COL"; I cannot do without the "expect A.COL" part because that would cause the sql to be ambiguous.

Appreciate your help, almighty StackOverflow

Ray

Replacing columns is discussed http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea unfortunately I suspect your answer will be no. — pd40, Jun 28 '12 at 08:40
@pd40 nice link there, I think the temp table approach is the best one, not elegant, but effective. — Jcis, Jun 28 '12 at 08:59

score 2 · Answer 1 · answered Jun 28 '12 at 08:52

You can either use table.* or table.fieldName.

There is no syntax available for table.* (except field X).

This means that you can only get what you want by explicitly listing all of the fields...

select
  A.field1,
  A.field2,
  B.field3,
  A.field4,
  etc
from
  A join B on a.COL = B.COL;

This means that you may need to re-model your data so as to ensure you don't keep getting new fields. OR write dynamic sql. Interrogate the database to find out the column names, use code to write a query as above, and then run that dynamically generated query.

Thought about that dynamically generation idea as well. Sounds like there's no better solution. — RAY, Jun 28 '12 at 09:01

Thinhbk · Answer 2 · 2012-06-28T08:59:29.320

0

Try this: (not tested)

select Case B.COL
       when null then A.COL
       else B.REPLACER
       end as COLAB, A.*
from A left join B on A.COL = B.COL;

This should get the B.REPLACER when exists B.COL = A.COL, you can add more column in the select (like sample col1, col2) or use A.* (change COL into COLAB to make it distinguish with A.COL in A.*) .

edited Jun 28 '12 at 08:59

answered Jun 28 '12 at 08:43

Thinhbk

2,194
1
23
34

score 0 · Answer 3 · answered Jun 28 '12 at 09:20

Like said before, you cannot specify in regular sql which column not to select. you could write a procedure for that, but it would be quite complex, because you would need to return a variable table type. Probably something with refcursor magic stuff.

The closest I could come up with is joining with using. This will give you the column col in the first field once and for the rest all columns in a and b. So not what you want basically. :)

select *
from   a
join   b using (col)

score 0 · Answer 4 · answered Jun 28 '12 at 09:30

Let's start from first principles. select * from .... is a bug waiting to happend and has no place in production code. Of course everybody uses it because it entails less typing but that doesn't make it a good practice.

Beyond that, the ANSI SQL standard doesn't support select * except col1 from .... syntax. I know a lot of people wish it would but it doesn't.

There are a couple of ways to avoid excessive typing. One is to generate the query by selecting from data dictionary, using one of the views like USER_TAB_COLUMNS. It is worth writing the PL?SQL block to do this if you need lots of queries like this.

A simpler hack is to use the SQL*Plus describe to list out the structure of table A; cut'n'paste it into an IDE which supports regular expressions and edit the columns to give you the query's projection.

Both these options might strike you as labourious but frankly either workaround (and especially the second) would have taken less effort than asking StackOverflow. You'll know better next time.

Replace one column in a table with a column in a different table and select *

4 Answers4

Linked