query of a recordset

Results 1 to 3 of 3

Thread: query of a recordset

  1. #1
    Paul DO Guest

    Default query of a recordset

    how do i run an SQL query on the result of a query? <BR><BR>more specifically... i have a recordset which is the result of an SQL query. when i run queries on that recordset, what object do i reference for my "execute" method? do i have to create a new connection to the recordset, or can i simply run an execute method of the recordset?<BR><BR>example:<BR><BR>set objRS1 = objConnection.Execute("select * from Table where state = &#039CA&#039")<BR><BR>set varValue = ?????????.Execute("select sum(workers) from objRS1 where city = &#039LA&#039")<BR><BR>set objRec2 = ?????????.Execute("select * from objRS1 where city = &#039Santa Barbara&#039")<BR><BR>ect...<BR><BR>i asked this question before and was told several different ways to work around using ASP, but i just want to know how do i query a recordset that is the result of a query. i know how to skin this cat using asp, i want to know if i can skin it with SQL as well.<BR><BR>thanks<BR><BR>Paul D. Osborne

  2. #2
    Join Date
    Dec 1969

    Default RE: query of a recordset

    But a RecordSet *does not exist* in SQL! <BR><BR>Certainly not in the same set as a RecordSet exists in ODBC (or OLE DB) and thence in ADO. <BR><BR>So how *could* you do<BR><BR>set objRS1 = objConnection.Execute("select * from Table where state = &#039CA&#039")<BR>set varValue = ?????????.Execute("select sum(workers) from objRS1 where city = &#039LA&#039")<BR><BR>When "objRS1" does not even *EXIST* in the SQL world?<BR><BR>I think you are trying to mix apples and oranges.<BR><BR>As I said yesterday, the *equivalent* thing to do with SQL is to create a named VIEW of the data (which is a subset of the records in the entire table...and can even be created via a JOIN I think, though I certainly have never done that). Then you can make queries against that named view.<BR><BR>But SQL, per se, is always executed in the database engine. And record sets, per se, always exist only in the layers outside that engine. And I don&#039t *believe* that there is any way to tell an ODBC driver to stuff a recordset it just got back into the database so the DB engine can see it and then execute SQL against it. (Okay, pedantically, I am sure it could be done, but it would involve sending the data *back* to the DB engine to be saved in yet another table. And I am sure that doing a VIEW would be a heluva lot more efficient than that!) <BR><BR>Here&#039s a quote from http://w3.one.net/~jhoffman/sqltut.htm :<BR><BR>*** QUOTE ***<BR>Views <BR><BR>In SQL, you might (check your DBA) have access to create views for yourself. What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view: <BR><BR>CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS; <BR><BR>Now, write a query using this view as a table, where the table is just a listing of all Items Desired from the Orders table: <BR><BR>SELECT SELLERID <BR>FROM ANTIQUES, ANTVIEW <BR>WHERE ITEMDESIRED = ITEM; <BR><BR>This query shows all SellerID&#039s from the Antiques table where the Item in that table happens to appear in the Antview view, which is just all of the Items Desired in the Orders table. The listing is generated by going through the Antique Items one-by-one until there&#039s a match with the Antview view. Views can be used to restrict database access, as well as, in this case, simplify a complex query.<BR>**** END QUOTE ****<BR><BR>How does this *not* satisfy your requirements?<BR><BR>You first create the VIEW. Then you simply do<BR><BR>SELECT * FROM theView<BR><BR>to get all the records therein.<BR><BR>Then you do your other queries against that view. Voila??!!??<BR><BR>Just incidentally, this is also likely to be rather efficient, as the DB will have cached the contents of the view when you do the "SELECT *" from it and so can probably move quite quickly to do the other queries.<BR><BR>But I would *still* do a performance test of this vs. using better index management and multiple simple queries.<BR><BR>

  3. #3
    Paul DO Guest

    Default RE: query of a recordset---THANKS

    i think this will work very well. i would still like to be able to query a query, but i guess this is not possible, yet. i may keep searching a way. thank you very much none the less.<BR><BR>paul d. osborne

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts