Solved

Query From ResultSet Of Another Query

Posted on 1998-07-23
13
203 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
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 1

Expert Comment

by:andrey070798
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:guvencum
Comment Utility
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
Comment Utility
Which database do you use?
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Accepted Solution

by:
Marcius earned 30 total points
Comment Utility
What about:

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

Expert Comment

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

Expert Comment

by:vladika
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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 this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now