Query a Query?

crabjoe · March 10th, 2008, 01:32 PM

I know I can query an open query, but is it possible to query a query?

As an example, in an open query, I can do the below and it gives me a count.

select count(*) from openquery(D2SQL01,'

SELECT
      FNAME,
      LNAME,
      ADDRESS,
      CITY,
      STATE,
      ZIP
FROM TBL_ADDRESS
')

Thanks!

Jeff Mason · March 10th, 2008, 03:57 PM

Sure.

A query is a derived table, so it can be used anywhere where a table can be used. This is usually in a FROM clause or subquery. The only 'trick' is that you must give a name to the derived table (the query). This is so its columns can be unambiguously referred to if needed:

Code:

SELECT COUNT (*)
  FROM (SELECT
          FNAME,
          LNAME,
          ADDRESS,
          CITY,
          STATE,
          ZIP
        FROM TBL_ADDRESS) as DerivedTable

Jeff Mason
[email protected]

crabjoe · March 11th, 2008, 09:58 AM

Thanks Jeff, you gave me the exact answer I needed!! I didn't realize the query needed to be named. That's why I couldn't get it to work!!!