We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Query From ResultSet Of Another Query

guvencum
guvencum asked
on
Medium Priority
261 Views
Last Modified: 2010-04-04
How can make a query from the returned resultset of another query
for example
      Query1.SQL.Text:='select CourseName from Course
      Query1.Open;
      Query2.SQL.Text:='select CourseName from QUERY1
      query2.Open;
that is exactly what I want to do
Is there a way to reference a query after "from" reserved word or another thing that does same
Comment
Watch Question

Hi, guvencum!

You can use TDataSource for Query2 as a data provider.

> aDataSource.DataSet = Query1;
> Query2.DataSource = aDataSource;

Andrey.

Commented:
guvencum

here is a function from the delphi tips and tricks that I used

Here is a function that will perform a sequential search of the result set

from a TQuery:



  function SeqSearch(AQuery: TQuery; AField, AValue: String): Boolean;

  begin

    with AQuery do begin

      First;

      while (not Eof) and (not (FieldByName(AField).AsString = AValue)) do

        Next;

      SeqSearch := not Eof;

    end;

  end;

This function takes three parameters:



  1. AQuery: type TQuery; the TQuery component in which the search is to

             be executed.

  2. AField: type String; the name of the field against which the search

             value will be compared.

  3. AValue: type String; the value being searched for. If the field is of

             a data type other than String, this search value should be

             changed to the same data type.

             

The Boolean return value of this function indicates the success (True) or

failure (False) of the search.

Later
BoRiS

Commented:
I think you cannot.

But...
If you use some client-server database you can create VIEW from first query
and use view in the second query.

Also, you can write such query

select a.CourseName
from
  (select CourseName from Course) a

i.e select from subquery

Author

Commented:
I will use so complicated SQL statements that are 10 lines long querying from 4 or 5 table
It will include filters, equation of sums, groupings and such other things that make the statement complicated. Delphi's SQL engine can't handle so complicated queries.
That's why I wolud like to chop the statement into pieces and get the result.
Thanks in advance.

Commented:
Which database do you use?

Commented:
Like Vladika said before - views are a way to do it. But I couldn't create views with BDE. Either it doesn't allow this at all, or I have to use some specific db, or maybe it works only with client-server db, don't know, but shame.

BUT, you can still do it this way:

the procedure runs a query and puts the rusults to a file:

procedure QueryToFile(hTmpDb: hDBIDB; TblName, SQL: String);
var
  hStmt: hDBIStmt;
  hQryCur, hNewCur: hDBICur;
begin
  hQryCur := nil;
  hNewCur := nil;
  hStmt := nil;

  try
    Check(DbiQAlloc(hTmpDb, qrylangSQL, hStmt));
    Check(DbiQPrepare(hStmt, PChar(SQL)));
    Check(DbiQExec(hStmt, @hQryCur));
    Check(DbiQInstantiateAnswer(hStmt, hQryCur, PChar(TblName), szPARADOX,
                                      true, @hNewCur));

  finally
    if hStmt <> nil then
      Check(DbiQFree(hStmt));
    if hNewCur <> nil then
      Check(DbiCloseCursor(hNewCur));
  end;
end;

This way you can run queries on queries:

QueryToFile(Database1.Handle, 'QUERY.DB', 'SELECT * FROM Customer WHERE state=''HI'';');
QueryToFile(Database1.Handle, 'QUERY1.DB', 'SELECT * FROM query.db WHERE city=''Hoover'';');
......................................

See the idea?
You should also delete the temporary result tables after that.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Grhm... Marcius, were you in a big hurry so you didn't read any previous commants?!!!

Commented:
I guess,
my query (with subquery in the FROM clause) do not work for DBase, Paradox databases
Sorry...  But in the Oracle it works :-)

Commented:
See my answer on the question number Q.10067618
"Branching a TQuery after executing SQL"

Maybe it is what you want

Commented:
Hey Matvey, my answer was accepted. So guess what... I did read all the previous comments but thought that you guys were not on the right track. I was correct.

Commented:
Your answere was autograded. Guvencum doesn't need the question - he left it. Ex-Ex grades answeres automatically if there is no responce.

Commented:
I still think that my answer is valid. The easiest way to do stuff like that is to use SQL. SQL was developed for exactly that sort of query, so use it I say.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.