|
View previous topic :: View next topic
|
| Author |
Message |
nivastech
New User
Joined: 17 Aug 2007
Posts: 6
Location: Chennai
|
|
|
|
Hi Al,
I have 4 tables,
metatable with columns - tableid(PK) and name
row table with columns - rowid (PK) and tableid (FK)
column table with columns - column id (PK) and tableid(FK)
data table with columns - rowid (FK) ,column id (FK),tableid(FK) and data
I'll be provided with name or tableid. And I need to get the columnid and data out of the query.
Provide me with possible Querying options and Queries. Can this be brought out of joins, correlated subqueries. Or separate queries needed?
Thanks
Nivas |
|
| Back to top |
|
 |
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
With a physical data model like that, you are supposedly not the first at your site to issue such a query.
Have a look into existing similar queries to this database, you may have luck to find one where you only have to change the key values.
And by the way: Does the fact that you are not provided with a rowid mean that you are supposed to retrieve data for all rows for a column in a specific table? |
|
| Back to top |
|
|
nivastech
New User
Joined: 17 Aug 2007
Posts: 6
Location: Chennai
|
|
|
|
Hi,
The PDM is not ready yet. Also this is a new database, and there was no need for any querying earliler. Also i'm a new practitioner to DB2.
Yes, i need to retreive all rows in a specific table.
I repeat... I need to retreive the data from data table and columnid from column table, provided name or table id in metatable.
Thanks |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Something like this:
| Code: |
SELECT
D.ROWID
,D.COLUMNID
,D.DATA
FROM META_TABLE A
,DATA_TABLE D
WHERE
A.NAME = 'yourname' AND
A.TABLEID = D.TABLEID
|
If you are provided with a table id you can skip using the meta table:
| Code: |
SELECT
D.ROWID
,D.COLUMNID
,D.DATA
FROM DATA_TABLE D
WHERE
D.TABLEID = yourid
|
|
|
| Back to top |
|
|
nivastech
New User
Joined: 17 Aug 2007
Posts: 6
Location: Chennai
|
|
|
|
Thanks Hansen.
I need to get one more column, say column X, from column table, (actually which i should've mentioned earlier) , Is that possible to get it done in a single query ? |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Since you didn't qualify your query on column id in the previous queries, you will get all columns for a table.
If you add
| Code: |
AND D.COLUMNID = colid
or
AND D.COLUMNID IN (colid1, colid2)
|
to your where statement you can qualify your selection on specific columns. |
|
| Back to top |
|
|
nivastech
New User
Joined: 17 Aug 2007
Posts: 6
Location: Chennai
|
|
|
|
Apologies for my previous unclear attempts. Let me be more clear.
I've to load just the data from the data table into the memory as a sequence of records. Each record is made of multiple entries from data table. Each data in data table is identified using a rowid, columnid and tableid.
Now, i need columnname from column table, row id from row table (this is needed to track end of each row/record) and data from data table. Using the columnname i need to query one more table columnfrmt to get frmtofcol.
For this i intended to use 2 cursors (1 for row table and 1 for column table) and 2 direct query to get data and frmtofcol respectively.. Now i need to make it in a single cursor using subqueries or anything else.
My input is either tableid or tablename. Can I retreive all the data in a single query using sub queries or more than one query is required ? |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
From your information, it should be possible to retrieve the data in a single query.
Your intent to clarify things didn't succeed, as you suddenly threw in 2 additional requirements (your original post did not mention anything about a columnname column, plus you added a table). Nevertheless, information from these tables can be incorporated in the query I have suggested above, with additional correllations through the column table and the columnfrmt table.
I suggest that you work with a solution yourself, and then if you have specific questions, post again.
If you are new to DB2 and SQL, do go through some SQL primer course and make use of the DB2 manuals, which is available at this website, and seek the advice of colleagues and support staff at your site.
One final remark: You mentioned that columnname is the key in columnfrmt table. I wonder if this should key should have been columnid instead? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select ...
from Tables tb
join TabRows rw on rw.tbid = tb.tbid
join TabColumns co on co.tbid = tb.tbid
join colDef Def on def.colname = co.colname
left join data dt on dt.tbid = tb.tbid and dt.rowid = rw.rowid and dt.colid = co.colid
where tb.tbname = :hv
order by rw.rowid, co.colid |
I hope you have decent indexes.
The left join on data is for when you add "columns" to a "table", but haven't filled in data for some "rows". It is not absolutely necessary but makes your program easier and guides DB2 to a reasonably good accesspath.
would give you all needed info but not in tabular format.
although possible to transpose in SQL, it is a lot easier in COBOL
PS. I know of at least one company in Belgium that uses this kind of structure. you don't work for that one ? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Just a thought: What is exactly the requirement that makes you develop such a system?
Maybe you want to have a look at the XML possibilities in DB2. My guess is that you could meet the same requirements using XML with a lot less programming and the benefit of having IBM taking care about improving performance and future releases. |
|
| Back to top |
|
|
|
|
