On 2014/05/26 22:17, Staffan Tylen wrote:
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.
Nope, understood perfectly. It's a classic case of trying to fix bad table design with worse SQL statements and it is not uncommon
because a lot of people are in your shoes viz. inheriting badly designed databases to work from and try to make sense of the layouts.
As others have already pointed out, this isn't SQL's intention or job and you can't do the thing you want to do like that. May I
suggest something that could possibly work for you?
Interestingly, the original badly designed DB with all those non-sensible column names is a much better design than your newer table
which has the column name as a field entry IF they keep only the same kinds of data in the same columns (which I believe they do if
understood you correct). What really you should do is simply extract it all to a translation table with really proper names.
You can even do this with a View by simply adding proper column names so that if you have a DB like this: (I'm going to steal
Simon's example for laziness):
ID COL1 COL2 COL3 COL4
1 876 67 68 54
2 54 875 46 45
3 57 445 67 4
You can declare a view like this:
CREATE VIEW TView AS SELECT ID, COL1 AS DeviceName, COL2 AS Position, COL3 AS
DeviceStatus, COL4 AS UserName FROM BadTable;
(or whatever else you want those column names to be)
And then run your queries like this:
SELECT DeviceName, DeviceStatus, etc. FROM TView WHERE DeviceStatus=xxx;
Which is more sensible and will cause you a LOT less work in future.
If you have already put in a great deal of effort to use your new slimmer table, then the above will require a lot of reworking and
options are bleak.
A last option (which is silly but will work if this is a post-traumatic fix rather than a design decision) would be a query with a
very large CASE statement which checks the column (in the new Table) for the name of the column and then compares or extracts that
specific column from the Old table. Keeping with the examples above and imagining your new table has a column called "PropertyName"
which specifies what column the data is from:
SELECT ID,
CASE N.PropertyName WHEN 'DeviceName' THEN O.COL1 WHEN 'Position' THEN O.COL2 WHEN 'DeviceStatus' THEN O.COL3 ELSE 'Invalid
Column Name' END AS PropertyValue,
(other columns...)
FROM TheNewTable AS N
LEFT JOIN TheOldTable AS O ON O.ID=N.ID
WHERE xxx;
But this will be ugly SQL, it will be convoluted (especially for 100 columns, although you may not need all 100) and I am unsure as
to how fast it will execute - but worth a shot if it's just a quick fix needed.
Ref:
http://www.sqlite.org/lang_expr.html#case
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
