Reading a SET of records 'twice' from the same table in one Query

[phoenix_me]

I have a table which look like below

DEFINE TEMP-TABLE Changes
   FIELD iKey1 AS INTEGER
   FIELD dDate AS DATE
   FIELD cChanges AS CHARACTER FORMAT "X(1000)"
.

Is it possible to build ONE query which will include all rows from last 60 days (based on dDate field) and all rows where iKey1 = iKey1 from results from last 60 days?

 

[MaximMonin]

?
for each changes no-lock where
changes.dDate >= today - 60 and
changes.iKey1 = iKey1:
end.

?
define buffer changes2 for changes.
for each changes no-lock where
changes.dDate >= today - 60,
each changes2 no-lock where
changes2.iKey = changes.ikey:
end.

 

[phoenix_me]

?
for each changes no-lock where
changes.dDate >= today - 60 and
changes.iKey1 = iKey1:
end.

You will receive message about ambiguous iKey1 or illegal nested block. Even for me it's not clear ...changes.iKey1 = iKey1? What is the source of second iKey1?

If I will be able to do such thing the query will be look like following

FOR EACH Changes WHERE Changes.dDate GE (TODAY - 60),
   EACH Changes WHERE Changes.iKey = Changes.iKey /* (from first FOR EACH line) */
   END.
END.

But such statement is illegal.

?
define buffer changes2 for changes.
for each changes no-lock where
changes.dDate >= today - 60,
each changes2 no-lock where
changes2.iKey = changes.ikey:
end.

In this case you have results in two tables, Changes and Chages2. I would like to have all results in one table. Is it possible?
But of course this resolves my problem. I will store results in another TEMP-TABLE, so I will have it all in one place.

 

[Cringer]

You could define a buffer for Changes and do this:

DEFINE BUFFER Changes2 for Changes.
FOR EACH Changes WHERE Changes.dDate GE (TODAY - 60),
   EACH Changes2 WHERE Changes2.iKey = Changes.iKey /* (from first FOR EACH line) */
   END.
END.

Or have I missed something?

 

[phoenix_me]

You could define a buffer for Changes and do this:

DEFINE BUFFER Changes2 for Changes.
FOR EACH Changes WHERE Changes.dDate GE (TODAY - 60),
   EACH Changes2 WHERE Changes2.iKey = Changes.iKey /* (from first FOR EACH line) */
   END.
END.

Or have I missed something?

Yes, you're right. And this is also the second part of previous answer Posted by MaximMonin. I did this way but I have one major issue with this resolution. I can't use it with my query. This is because this Query requires TWO BUFFERS and I am already using only ONE BUFFER

DEFINE QUERY Changes FOR ChgLog.

IF Log.ipLogObject = 'KC_ALL1' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.
IF Log.ipLogObject = 'KC_ALL2' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.
IF Log.ipLogObject = 'KC_ALL3' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.

GET FIRST Changes.
DO WHILE NOT QUERY-OFF-END('Changes'):
     .....
     .....
     .....
     GET NEXT Changes.
END.
CLOSE QUERY Changes.

So if I will change it to:

DEFINE QUERY Changes FOR ChgLog.
[FONT=monospace]
[/FONT]DEFINE BUFFER Changes2 for Changes

IF Log.ipLogObject = 'KC_ALL0' THEN
 DO:
    OPEN QUERY Changes FOR EACH Changes WHERE Changes.dDate GE (TODAY - 60),[FONT=monospace]
    [/FONT]EACH Changes2 WHERE Changes2.iKey = Changes.iKey /* (from first FOR EACH line) */
END.

IF Log.ipLogObject = 'KC_ALL1' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.
IF Log.ipLogObject = 'KC_ALL2' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.
IF Log.ipLogObject = 'KC_ALL3' THEN
DO:
    OPEN QUERY Changes FOR EACH ChgLog WHERE.....etc
END.

GET FIRST Changes.
DO WHILE NOT QUERY-OFF-END('Changes'):
     .....
     .....
     .....
     GET NEXT Changes.
END.
CLOSE QUERY Changes.

I have a message that QUERY requires TWO BUFFERS and it's already DEFINED for ONE. Is it any way to resolve it?

 

[MaximMonin]

define buffer chglog2 for chglog.
define query Changes for chglog, chglog2.

IF Log.ipLogObject = 'KC_ALL0' THEN
DO:
OPEN QUERY Changes FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60),
EACH Chglog2 WHERE Chglog2.iKey = Chglog.iKey /* (from first FOR EACH line) */
END.
....

Or use dynamic queries.

define variable hQueryData as handle no-undo.
create query hQueryData.
hQueryData:FORWARD-ONLY = true.
hQueryData:SET-BUFFERS (BUFFER chglog:HANDLE,
BUFFER chglog2:HANDLE).
IF Log.ipLogObject = 'KC_ALL0' THEN
DO:
sQuery = "FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60), EACH Chglog2 WHERE Chglog2.iKey = Chglog.iKey".
end.
....
hQueryData:QUERY-PREPARE(sQuery).
hQueryData:QUERY-OPEN.
hQueryData:GET-FIRST ().
repeat:
if hQueryData:QUERY-OFF-END then leave.
......
hQueryData:GET-NEXT ().
END.

 

[phoenix_me]

define buffer chglog2 for chglog.
define query Changes for chglog, chglog2.

IF Log.ipLogObject = 'KC_ALL0' THEN
DO:
OPEN QUERY Changes FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60),
EACH Chglog2 WHERE Chglog2.iKey = Chglog.iKey /* (from first FOR EACH line) */
END.
....

Or use dynamic queries.

define variable hQueryData as handle no-undo.
create query hQueryData.
hQueryData:FORWARD-ONLY = true.
hQueryData:SET-BUFFERS (BUFFER chglog:HANDLE,
BUFFER chglog2:HANDLE).
IF Log.ipLogObject = 'KC_ALL0' THEN
DO:
sQuery = "FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60), EACH Chglog2 WHERE Chglog2.iKey = Chglog.iKey".
end.
....
hQueryData:QUERY-PREPARE(sQuery).
hQueryData:QUERY-OPEN.
hQueryData:GET-FIRST ().
repeat:
if hQueryData:QUERY-OFF-END then leave.
......
hQueryData:GET-NEXT ().
END.

I will need to use DYNAMIC QUERY. This is because with the same query I would like to use in some conditions ONE BUFFER, and in other conditions TWO BUFFERS.
hQueryData:SET-BUFFERS (BUFFER chglog:HANDLE, BUFFER chglog2:HANDLE) is the answer. I will try it tommorow.
Thanks for answer

I still have problem with Query.
I have a message "
Conflict in number of buffers for QUERY hChangeLog from previous use. (3325)"

define variable hQueryData as handle no-undo.
create query hQueryData.
hQueryData:FORWARD-ONLY = true.

IF Log.ipLogObject = 'KC_ALL0' THEN
 DO:
    hQueryData:SET-BUFFERS (BUFFER chglog:HANDLE).
       sQuery = "FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60).
 end.

IF Log.ipLogObject = 'KC_ALL1' THEN
DO:
   hQueryData:SET-BUFFERS (BUFFER chglog:HANDLE,
                             BUFFER chglog2:HANDLE).
   sQuery = "FOR EACH Chhlog WHERE Chglog.dDate GE (TODAY - 60), EACH  Chglog2      WHERE Chglog2.iKey = Chglog.iKey".
end.
....
hQueryData:QUERY-PREPARE(sQuery).
hQueryData:QUERY-OPEN. 
hQueryData:GET-FIRST ().
repeat:
  if hQueryData:QUERY-OFF-END then leave.
......
  hQueryData:GET-NEXT ().
END.

[/QUOTE]