June 13, 2006 at 2:16 pm
I have 7 tables.
APP
Objecttypes
DEV_DropdownHardcoded
DEV_DropdownLookup
DEV_Textbox
DEV_Datefield
DEV_HiddenConstants
APP
paramname objecttypeid objectid
@ADTY 4 1
@FHTY 2 2
@DHOK 3 4
Objecttypes
ObjectTypeID ObjectTable
4 DEV_DropdownHardcoded
2 DEV_DropdownLookup
3 DEV_Textbox
The rest of the tables
DEV_DropdownHardcoded
DEV_DropdownLookup
DEV_Textbox
DEV_Datefield
DEV_HiddenConstants
contain the same fields:
DEV_DropdownHardcoded
ID NAME LABEL
1 PUBCODE PUBLICATION CODE
DEV_DropdownLookup
ID NAME LABEL
2 USERMENU MENU FOR USER
DEV_Textbox
ID NAME LABEL
4 INPUT TEXT ENTER TEXT
DEV_Datefield
DEV_HiddenConstants
Each objecttypeid corresponds to a objecttable column in objecttypes table
And each objecttable column has a table name.
Each tablename is the actual table that contains the objectid as ID.
I want to select APP.PARAMNAME, APP.OBJECTTYPEID, LABEL
how can i select a label column with paramname correctly from all these tables?
Thanks for all your help
June 13, 2006 at 2:31 pm
You really need DYNAMIC SQL for this.
Search for SOMMARSKOG and DYNAMIC SQL on the Internet and you will find a person who has good insight about this.
http://www.sommarskog.se/dynamic_sql.html
N 56°04'39.16"
E 12°55'05.25"
June 13, 2006 at 3:27 pm
Too many tables.
Those 5 must be in one with double key:
Labels:
ID TypeId NAME LABEL
1 4 PUBCODE PUBLICATION CODE
2 2 USERMENU MENU FOR USER
4 3 INPUT TEXT ENTER TEXT
_____________
Code for TallyGenerator
June 14, 2006 at 2:41 am
IMHO, this is what you get from letting a procedural programmer design the data model. This will work for your example data:
SET NOCOUNT ON
DECLARE @APP TABLE(paramname varchar(10), objecttypeid int, objectid int)
INSERT INTO @APP
SELECT '@ADTY',4,1
UNION SELECT '@FHTY', 2, 2
UNION SELECT '@DHOK', 3, 4
DECLARE @Objecttypes TABLE (ObjectTypeID int, ObjectTable varchar(25))
INSERT INTO @Objecttypes
SELECT 4, 'DEV_DropdownHardcoded'
UNION SELECT 2, 'DEV_DropdownLookup'
UNION SELECT 3, 'DEV_Textbox'
DECLARE @DEV_DropdownHardcoded TABLE (ID int, NAME varchar(10), LABEL varchar(20))
INSERT INTO @DEV_DropdownHardcoded
SELECT 1, 'PUBCODE', 'PUBLICATION CODE'
DECLARE @DEV_DropdownLookup TABLE (ID int, NAME varchar(10), LABEL varchar(20))
INSERT INTO @DEV_DropdownLookup
SELECT 2, 'USERMENU', 'MENU FOR USER'
DECLARE @DEV_Textbox TABLE (ID int, NAME varchar(10), LABEL varchar(20))
INSERT INTO @DEV_Textbox
SELECT 4, 'INPUT TEXT', 'ENTER TEXT'
SET NOCOUNT OFF
SELECT A.paramname, A.objectid
--, O.ObjectTable
, CASE WHEN O.ObjectTable = 'DEV_DropdownHardcoded' THEN
(SELECT MIN(LABEL) AS LABEL FROM @DEV_DropdownHardcoded)
WHEN O.ObjectTable = 'DEV_DropdownLookup' THEN
(SELECT MIN(LABEL) AS LABEL FROM @DEV_DropdownLookup)
WHEN O.ObjectTable = 'DEV_Textbox' THEN
(SELECT MIN(LABEL) AS LABEL FROM @DEV_Textbox)
ELSE 'Unknown' END AS LABEL
FROM @APP AS A
INNER JOIN @Objecttypes AS O ON A.objecttypeid = O.ObjectTypeID
/* Results:
paramname objectid LABEL
---------- ----------- --------------------
@ADTY 1 PUBLICATION CODE
@DHOK 4 ENTER TEXT
@FHTY 2 MENU FOR USER
*/
Andy
June 14, 2006 at 2:49 am
For another approach, Try:
SELECT A.paramname, A.objectid
--, O.ObjectTable
, COALESCE(H.LABEL, L.LABEL, T.LABEL, 'Unknown') AS LABEL
FROM @APP AS A
INNER JOIN @Objecttypes AS O ON A.objecttypeid = O.ObjectTypeID
LEFT JOIN (SELECT MIN(ID) AS ID, MIN(LABEL) AS LABEL FROM @DEV_DropdownHardcoded) AS H
ON A.objectid = H.ID
AND O.ObjectTable = 'DEV_DropdownHardcoded'
LEFT JOIN (SELECT MIN(ID) AS ID, MIN(LABEL) AS LABEL FROM @DEV_DropdownLookup) AS L
ON A.objectid = L.ID
AND O.ObjectTable = 'DEV_DropdownLookup'
LEFT JOIN (SELECT MIN(ID) AS ID, MIN(LABEL) AS LABEL FROM @DEV_Textbox) AS T
ON A.objectid = T.ID
AND O.ObjectTable = 'DEV_Textbox'
Andy
June 14, 2006 at 9:42 am
Thank you all for responding.
Unfortunately i don't know how many rows are in the objecttypes table.
Since they grow weekly new tables are created for each row in objecttypes table colulmn.
So best bet would be using dynamic sql i guess or create another table that contains values from both app, objecttypes and dev tables.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply