I fully agree with this. The fact is that I'm using a database from a third party.
TBL1 PFX1_COL1 PFX1_COL2 PFX3_COL3 etc TBL2 PFX2_COL1 PFX2_COL2 PFX3_COL3 etc. The first column has the name of the table. The next x number of columns contain data universal to all tables, such as date and time stamps. The rest of the columns are unique for each table. I've built a 'master' table containing the 1st and the common columns which I use for selecting data. But at occasions I need to get hold of data from the original table and the only way to know where that data can be found is to look at the first column which contains the name of the original table. And to make things even worse, the column names are all different so joining tables is not doable - to do that you need to know the name of the tables to join and the name of the columns to join on, and all this dynamically. The number of tables is close to 100. This is why I've tried to get SQL to dynamically resolve the table and column names using the example I posted. But as that doesn't work I need to look for a different solution. I hope all this is not too vague to understand. Staffan On Mon, May 26, 2014 at 7:04 PM, Simon Slavin <[email protected]> wrote: > > On 26 May 2014, at 3:50pm, Igor Tandetnik <[email protected]> wrote: > > > You can't do that. SQL allows you to manipulate values, but not names. > All table and column names are set in stone when the query is prepared and > the execution plan is determined. > > And the fact that you're even trying to do it may be a sign that your > database needs refactoring. If column names are variable then they should > be column values. So a table like > > ID COL1 COL2 COL3 COL4 > 1 876 67 68 54 > 2 54 875 46 45 > 3 57 445 67 4 > > should actually be > > ID PROP VALUE > 1 1 876 > 1 2 67 > 1 3 68 > 1 4 54 > 2 1 54 > ... > > with a primary key of (ID, PROP). > > That means you can select anything you want and you don't have to try to > treat a fixed thing as variable. > > Simon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
