Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need Help in Querying

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nivastech

New User


Joined: 17 Aug 2007
Posts: 6
Location: Chennai

PostPosted: Wed Aug 11, 2010 1:22 pm    Post subject: Need Help in Querying
Reply with quote

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
View user's profile Send private message

Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Aug 11, 2010 2:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nivastech

New User


Joined: 17 Aug 2007
Posts: 6
Location: Chennai

PostPosted: Wed Aug 11, 2010 2:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Aug 11, 2010 3:05 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nivastech

New User


Joined: 17 Aug 2007
Posts: 6
Location: Chennai

PostPosted: Wed Aug 11, 2010 3:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Aug 11, 2010 4:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nivastech

New User


Joined: 17 Aug 2007
Posts: 6
Location: Chennai

PostPosted: Thu Aug 12, 2010 12:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Aug 12, 2010 1:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Aug 12, 2010 1:44 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Aug 12, 2010 2:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -514 while querying through EZT esther_ebe CA Products 1 Tue Oct 26, 2010 12:49 pm
No new posts DB2 abend while querying - SQLCODE=-9... krishnapriyavr DB2 4 Wed Jul 14, 2010 1:14 pm
No new posts Querying two flat files on common key... ganeshptrk Mainframe Interview Questions 3 Sun Jun 24, 2007 2:32 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us