Query

gasomma

Member
Hi to All,

question I think simple for you.
I have a temp-table that contains same records for customer table.
I have a query (QC) that shows customer records.
In the QC query I need to show only customer records inside the temp-table.

For example:

for each temp-table where .....
open query QC where customer.field = temp-table.field.
end.

There is a way for to find a solution?
many thanks.
G.
 

TomBascom

Curmudgeon
Maybe you should step back and explain this in more depth with an example that uses an actual TT definition and some real field names?

At this moment it seems like a ridiculous thing to be trying to do.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Are you trying to find the subset of the records from the customer table that are also in this temp-table? If so we would also need to know the key that joins these tables.
 

gasomma

Member
Hi,

thanks for your reply. Let me explain better.
I have defined an OPEN QUERY (QC) for customer table with different sort-by in frame-a.

My problem is to show same records in the table considering the result of this research.
For example shows each records that contains not in one field but in any character fields the string "urpon frisbee".

Code:
create buffer bh for table tbl.
create query qh.
qh:set-buffers( bh ).
qh:query-prepare( "for each " + tbl ).
qh:query-open.

qh:get-first( no-lock ).
do while qh:query-off-end = no:
fh = bh:buffer-field( fld ).
if fh:buffer-value = xyz then /* needs special handing if there are array fields in the db ... */
do:
display tbl fld bh:recid fh:buffer-value.
pause.
end.
qh:get-next( no-lock ).
end.

delete object bh.
delete object qh.

return.

end.

for each _file no-lock where _file-name = "customer":

for each _field no-lock of _file:

if _data-type <> "character" then next. /* skip non-char fields */

run x ( _file-name, _field-name, "urpon frisbee" ).

end.

end.
Thanks in advance.
G.
 
Last edited by a moderator:

TomBascom

Curmudgeon
You want to create a procedure that can find records in any table where the provided string appears in any character field of any table?
 

TomBascom

Curmudgeon
mpro sports2000 -p fe.p

When prompted type "lift" or whatever.

Code:
/* fe.p
 *
 */

define variable searchFor    as character no-undo format "x(40)".
define variable recordsFound as integer   no-undo.

update searchFor.

for each _file no-lock
   where _file._file-num > 0
     and _file._file-num < 10000
      by _file._file-name:

  display _file-name with frame a down.

  for each _field no-lock
     where _field._file-recid = recid( _file )
       and _field._data-type = "character" 
       and _field._extent = 0:

    run findstuff.p ( _file._file-name, _field._field-name, searchFor, output recordsFound ).

    display _field-name recordsFound with frame a down.
    down with frame a.

  end.

  down with frame a.

end.
Code:
/* findstuff.p
 *
 */

define input  parameter tableName   as character no-undo.
define input  parameter fieldName   as character no-undo.
define input  parameter lookFor     as character no-undo.
define output parameter results     as integer   no-undo.

define variable b   as handle    no-undo.
define variable q   as handle    no-undo.
define variable qs  as character no-undo.
define variable i   as integer   no-undo.


qs = substitute( 'for each &1 no-lock where &2 matches "*&3*" ', tableName, fieldName, lookFor ).

create query  q.
create buffer b for table tableName.

q:set-buffers( b ).

q:query-prepare( qs ).
q:query-open.

results = 0.
do while q:get-next( no-lock ) <> false:
  results = results + 1.
end.

delete object q.
delete object b.

return.
 

TomBascom

Curmudgeon
The code above is, of course, absolutely dreadful from a performance point of view. It will read the entire database. Multiple times. That is no big deal for sports2000 but it is unlikely to be a good idea for any real production database.
 

gasomma

Member
Thanks for your reply.
I need only one table for time (customer in this example).
My problem also is how show the records extracted in a QUERY already defined in frame.

OPEN QUERY QC FOR EACH customer WHERE (result of "QS") NO-LOCK BY cust-num .

Is it possible?
 

TomBascom

Curmudgeon
I'm sorry but I have no idea what you are asking.

Your question seems to have something to do with displaying data. Apparently there is a two-step process involved where you want to modify the first result set somehow based on some attribute of the initial query. But I see no example code that displays any data and your examples don't really explain what you expect to happen. The examples that you have shared so far are all about querying the data in a rather convoluted and arcane fashion.

There is also some mention of a temp-table. How that fits in is a mystery to me.

If the query has already been opened and displayed somehow (maybe with a browse?) then I guess you could close it, re-open it, and filter it based on your new criteria. Whatever that new criteria is. But I really don't think that I understand what you are asking.
 
Last edited:

TomBascom

Curmudgeon
It might help to mock up what you would like your screens and user experience to look like so that we can visualize the steps of the process and the desired outcomes.
 

gasomma

Member
Hi,

My question is for to display data in existing query already opened.
I would like to have a difference sort by in the same browse.

Ex.
If I press Botton1 I have a query sort- by name. OPEN QUERY QC FOR EACH customer NO-LOCK BY name..
If I press Botton2 I have a query sort- by cust num. OPEN QUERY QC FOR EACH customer NO-LOCK BY cust-num .


DEFINE QUERY QC FOR customer SCROLLING.

DEFINE BROWSE QC
QUERY QC DISPLAY
cust-num
name
WITH NO-ASSIGN SEPARATORS MULTIPLE SIZE 136 BY 21.1
FONT 2.

After that I have a field string where insert data. I need to looking it in a string in the any fields of the customer table. The records selected would be displayed in the query before defined.


thanks a lot.
 

TomBascom

Curmudgeon
Queries do not display anything.

A browse displays the records in the result set of its associated query.

You cannot change what has been selected by the query or what order the records are in after the query has been opened. Once you open it all of that is fixed - it is not changeable.

If you wish to change the records in the result set and therefore available to the browse you must close the query and re-open it. Just as you do when you switch from sorting by name to sorting by number. The difference that you seem to be struggling with is that you also need to change the selection criteria (the WHERE clause). You have shown code that apparently selects what you want so it seems like you need to close the existing query and re-open it with your modified criteria.
 

gasomma

Member
Thanks a lot Tom.
I will try,
 
Top