Select * as ?

[jeepxo]

In the database I have a number of tables with the same column name. for example Employer.FirstName, Staff.FirstName, Person.firstName etc.
I'm doing a full outer join and want to rename these without specifying the columns.

So it's

Select employer.*, staff.*, person.* from employer full outer join staff on something full outer join person on something

What I think I want to do for simplicity is rename everything to tableName_columnName without having to explicitly name each column.

I'm trying to avoid

Select employer.address as Employer_Address,employer.FirstName as Employer_FirstName ...

And before you say it, I know that generally it is a waste of resources to simply say select * since you are returning a lot more than you actually need. The reason for using "select *" is there are a number of user defined columns where the user can name the column whatever they want. Once the application is in place, I don't have anyway of knowing exactly what the users have added for column names.

Is there a way to effectively say "Select employers.* as employers_*"?


To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[SQLSister]

Ouch. You never want to do that! Never never let users add columns with names you don't know. This is trouble waiting to happen. Typically when user defined columns are allowed they are named usercolumn1, usercolumn2 etc and there are a limited number of them. If you don't know the column names how can you effectively use them?

How on earth will you ever write code against a database when you don't know the column names?

I suppose your only choice is to look in the system tables for the column names, but I would never allow this to be done by the user interface. Talk about making your data completely unsafe!

And to have to select * for every single query, this thing is going to be slower than dirt.

Frankly I think your only valid choice is a complete redesign.



"NOTHING is more important in a database than integrity." ESquared

 

[jeepxo]

Thanks for your editorial comment. It makes for good reading when sitting on the toilet, but that's about it.

It would have been far more useful for you just to have said
"Sorry, but I don't know of a way to do what you are asking"

I'll correct your thought process since it was wrong.
I am not using Select * for every single query, just for a query under certain conditions.

To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[r937]

jeep, don't get mad

the design dictates the solution, and in this case there ain't no way to get what you want

SQL Sister is right, you should be thinking about a redesign

and a three-way full outer join? whoa!!!

r937.com | rudy.ca

 

[jeepxo]

Think "Report Generation Tool."
Ever done something with Crystal Reports or Report Smith?
Dog slow. Can't get around it. If you want an idiot proof interface, the trade off is performance because you have to assume that an idiot is using it.

The application is fine and does a bang up job for what it is intended to do. The user base is looking for a cheap way to do some on demand reports. Both Crystal Enterprise and Report Smith Enterprise are over kill for what is needed here. At $20,000 and $14,000 respectively per server installation, it's just not cost effective.

So I'm building a scaled down simplified way for the users to pick and choose what they need when generating a report, hence the full outer joins and the select *. Yes it is slow. I know it's slow. It would be slow if we used Crystal Enterprise as well. The difference is with 2 days of my programming time I can rattle off a quick and dirty tool that lets users generate their own on demand reports without them having to have any knowledge of the database.

The solution that I was hoping to avoid is renaming the columns in all of the data tables to ensure they are unique, then adding to functions to the application to handle the conversion of the column names. I don't really want to do that because it means testing every single page in the application to accommodate this one functionality upgrade. What really should be just an add on means a whole new release of the product.


To build may have to be the slow laborious task of years. To destroy can simply be the thoughtless act of a single day.

 

[AdaHacker]

Why not dynamically generate the field list for the query? You could grab a list of the column names from the database, apply whatever transformations you need to turn them into a pretty "table.column_name AS Table_Column_Name" format, and dump that into the SELECT query that you pass to the database. Maybe not be the most elegant solution in the world, but it would give you the effect of auto-prefixing all the column names without having to manually do it in the SQL or alter the database.

 

[r937]

also do a search for EAV entity-attribute-value

i don't much like it but it does seem to fit the circumstances

r937.com | rudy.ca

 

[mikrom]

As AdaHacker said, it's very simply to do programatically:

Here is a Python exapmle

[COLOR=#804040][b]def[/b][/color] [COLOR=#008080]generate_select[/color](schema, table, field_list):
  myselect = "[COLOR=#ff00ff]select [/color][COLOR=#6a5acd]\n[/color]"
  [COLOR=#804040][b]for[/b][/color] field [COLOR=#804040][b]in[/b][/color] field_list:
    myselect += "[COLOR=#ff00ff]  %10s as %s_%s[/color][COLOR=#6a5acd]\n[/color]" % (field.ljust(10), table, field)
  [COLOR=#0000ff]# FROM  [/color]
  myselect +=  "[COLOR=#ff00ff]from %s.%s[/color]" % (schema, table)
  [COLOR=#804040][b]return[/b][/color] myselect

[COLOR=#804040][b]if[/b][/color] __name__=="[COLOR=#ff00ff]__main__[/color]":
  [COLOR=#0000ff]# obtain these from your database using driver (ADO, ODBC, ...)[/color]
  field_list=['[COLOR=#ff00ff]LASTNAME[/color]', '[COLOR=#ff00ff]FIRSTNAME[/color]', '[COLOR=#ff00ff]Field001[/color]', '[COLOR=#ff00ff]FIELD002[/color]', '[COLOR=#ff00ff]FIELD003[/color]']
  schema = '[COLOR=#ff00ff]BIZDATA[/color]'
  table = '[COLOR=#ff00ff]CUSTOMERS[/color]'
  generated_select = generate_select(schema, table, field_list)
  [COLOR=#804040][b]print[/b][/color] generated_select

Output is your desired select

select
  LASTNAME   as CUSTOMERS_LASTNAME
  FIRSTNAME  as CUSTOMERS_FIRSTNAME
  Field001   as CUSTOMERS_Field001
  FIELD002   as CUSTOMERS_FIELD002
  FIELD003   as CUSTOMERS_FIELD003
from BIZDATA.CUSTOMERS