Solved

Query From ResultSet Of Another Query

Posted on 1998-07-23
13
211 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
0
Comment
Question by:guvencum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358627
Hi, guvencum!

You can use TDataSource for Query2 as a data provider.

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

Andrey.
0
 
LVL 4

Expert Comment

by:BoRiS
ID: 1358628
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
0
 
LVL 3

Expert Comment

by:vladika
ID: 1358629
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:guvencum
ID: 1358630
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.
0
 
LVL 3

Expert Comment

by:vladika
ID: 1358631
Which database do you use?
0
 
LVL 3

Expert Comment

by:Matvey
ID: 1358632
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.
0
 
LVL 1

Accepted Solution

by:
Marcius earned 30 total points
ID: 1358633
What about:

select coursename from course
where coursename in (select coursename from course);
0
 
LVL 3

Expert Comment

by:Matvey
ID: 1358634
Grhm... Marcius, were you in a big hurry so you didn't read any previous commants?!!!
0
 
LVL 3

Expert Comment

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

0
 
LVL 3

Expert Comment

by:vladika
ID: 1358636
See my answer on the question number Q.10067618
"Branching a TQuery after executing SQL"

Maybe it is what you want

0
 
LVL 1

Expert Comment

by:Marcius
ID: 1358637
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.

0
 
LVL 3

Expert Comment

by:Matvey
ID: 1358638
Your answere was autograded. Guvencum doesn't need the question - he left it. Ex-Ex grades answeres automatically if there is no responce.
0
 
LVL 1

Expert Comment

by:Marcius
ID: 1358639
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.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question