Solved

Query From ResultSet Of Another Query

Posted on 1998-07-23
13
204 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

937 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

4 Experts available now in Live!

Get 1:1 Help Now