Equivalent of a SQL subquery in 4GL ?

[Hugh Middity]

Suppose that you have a table of support calls that contains an AssignedTo column (linking to another table of users). If you also have a history table that stores details of who worked on a support call and when, how could you query a Progress 9.1D database to get a listing of all the support calls that are assigned to a Fred or have been worked on by Fred?

In sql, the pseudo query would be something like:
select * from SupportCall where AssignedTo = 'fred'
or SupportCall.Id in (select SupportCallHistory.SupportCallID from SupportCallHistory where SupportCallHistory.User = 'fred')

Is there a way to get the equivalent using 4GL?

Thanks for any suggestions.

 

[sphipp]

for each SupportCall where SupportCall.AssignedTo = 'fred' no-lock:
/* Do something */
end.

To amend the records use exclusive-lock

 

[rusguy]

I think it would be best to use 2 queries at the same time, something like this

for each SupportCall where AssignedTo = 'fred' no-lock:
  run yourLogic(buffer SupportCall).
end.
for each SupportCallHistory where SupportCallHistory.User = 'fred' no-lock,
  each SupportCall where SupportCall.Id = SupportCallHistory.SupportCallID no-lock:
  run yourLogic(buffer SupportCall).
end.
 
procedure yourLogic:
  define parameter buffer SupportCall for SupportCall.
  /*
  put your logic here
  */
end procedure.

 

[Hugh Middity]

Thanks rusguy

How could one modify that logic to only process each SupportCall record once?

If it was assigned to Fred, and Fred had worked on the call a couple of times (so there were 2 support call history records), I would only want to include the call once, not 3 times.

 

[rusguy]

something like this:

for each SupportCall where AssignedTo = 'fred' no-lock:
  run yourLogic(buffer SupportCall).
end.
for each SupportCallHistory where SupportCallHistory.User = 'fred' no-lock
    break by SupportCall.Id:
  if first-of(SupportCall.Id) then
    for each SupportCall where SupportCall.Id = SupportCallHistory.SupportCallID 
                  and SupportCall.assignedto <> 'fred' no-lock:
      run yourLogic(buffer SupportCall).
    end.
end.
 
procedure yourLogic:
  define parameter buffer SupportCall for SupportCall.
  /*
  put your logic here
  */
end procedure.

 

[Hugh Middity]

Thanks

That looks ideal.