Hi All,

I have been wracking my brain trying to figure out a way to do this. I'm not sure if it can even be done. I am told it can be done using a SQL Sub Select, but I do not know SQL at all and am wondering if it is possible with FOCUS.

Here is my data:

Orig Ord Ord Nbr Entry Date Ship Date
123 123 20090303 20090303
123 123 20090303 20090303
123 123 20090303 20090303
123 456 20090303 20090304
123 456 20090303 20090304
123 456 20090303 20090304


I need to filter this by a particular Ship Date, but I also need all the records for the Orig Ord. The Entry Date is always the same for both orders. The Ship Dates can be different by Ord Nbr and the Orig Ord could have a later Ship Date than the second order.

Hopefully this is enough info.

Thank you so much for any help - even if it is to tell me if it can be done or not, so I can quit wracking my brain if it can't!

Krysti

This message has been edited. Last edited by: Kerry,

I don't know if you are looking for this....

Here is the sample query.

select * from TableA
where origin_ord in
(select distinct origin_ord from TableA where ship_Date = '20090304')

If above SQL doesn't solve your requirement, I guess it would be better if you tell the actual result set.

WF 7.1.7

Just using WebFOCUS without SQL, you could perform a pre extract to hold all the order numbers that have an order with a ship date equal to that required.

Then either use that in an IF field EQ (filename), or if the file is greater than 32000 bytes (the limit for EQ (filename) use it within a join, or you could use match logic like this -

Bear in mind that the first part is only to build some sample data to show you the technique

EX -LINES 6 EDAPUT MASTER,KrystiOrd,CF,MEM,FILENAME=KrystiOrd, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S0 ,$
  FIELD=Orig_Ord,     ,A5    ,A5    ,$
  FIELD=Ord_Numb,     ,A9    ,A9    ,$
  FIELD=Entry_Date,   ,YYMD  ,YYMD  ,$
  FIELD=Ship_Date,    ,YYMD  ,YYMD  ,$
-RUN
CREATE FILE KrystiOrd
MODIFY FILE KrystiOrd
FREEFORM Orig_Ord Ord_Numb Entry_Date Ship_Date
DATA
123,1231,20090303,20090303,$
123,1232,20090303,20090304,$
123,1233,20090303,20090305,$
234,2341,20090303,20090303,$
234,2342,20090303,20090304,$
234,2343,20090303,20090305,$
345,3451,20090303,20090306,$
345,3452,20090303,20090307,$
345,3453,20090303,20090308,$
END
-RUN
MATCH FILE KrystiOrd
BY Orig_Ord
WHERE Ship_Date EQ '2009/03/04'
RUN
FILE KrystiOrd
PRINT Ship_Date
   BY Orig_Ord
   BY Ord_Numb
   BY Entry_Date
AFTER MATCH HOLD OLD
END
-RUN
TABLE FILE HOLD
PRINT *
END

T

Using focus code, I would do it as follows:
TABLE your data, filtered on the specified ship_date, and hold the data, specifically the OrigOrd field. See to it that you have only one record per origord value (ie use SUM). Next join this hold file to the source table using the origord field as join field. This will give you all records that have at least one record with the requested ship_date.

T, you just beat me to it. But we think along the same lines....

This also does the trick, with one pass on the data:

 
DEFINE FILE KrystiOrd
TD/I1=IF Ship_Date EQ '2009/03/04' THEN 1 ELSE 0;;
END
TABLE FILE KrystiOrd
PRINT Ship_Date 
COMPUTE TT/I1=IF Orig_Ord NE LAST Orig_Ord THEN TD ELSE LAST TT; NOPRINT
BY Orig_Ord 
BY HIGHEST TD NOPRINT
BY Ord_Numb 
BY Entry_Date 
WHERE TOTAL TT EQ 1
END

Thank you all for your help.

I ended up joining back to the source table. I originally didn't want to do that because I thought it would not be efficient, however, there didn't end up being a problem.

Danny - thanks for showing me how to do it in one pass. That's what was driving me crazy. Unfortunately, using a define took way too long and the source data is not in the correct order to use the LAST function (if I understand correctly, the sort doesn't happen until after the compute). Please correct me if I'm wrong on that...

Thanks for the SQL sample also. I didn't use it because joining back to the source worked out and without knowing SQL, it was going to take me too long to figure out how to write it with the rest of the scenario of the data.

Thank you again for all your help. This forum is a great resource, especially when you're really under the gun. I really appreciate all you guys so much!

Krysti

Krysti,

quote:

Unfortunately, using a define took way too long and the source data is not in the correct order to use the LAST function (if I understand correctly, the sort doesn't happen until after the compute). Please correct me if I'm wrong on that...

1. Interesting what you say about the DEFINE. Did you do a MATCH? Did you do a HOLD and join to the original table? Did you create an SQL table and join to the original?

2. No, COMPUTE is done after the sort, hence the LAST function works like a charm. DEFINEing TD and sorting by it, will put all your wanted ship dates up front, then the COMPUTE "colors" all the Orig_Ord and they are screened after sort and COMPUTE by the WHERE TOTAL condition.

Danny,

Sorry it took me so long to reply back to you.

1. I ended up querying for the ship date, holding that and then joining back to the source table.

2. Thanks for correcting me on the compute. I must have gotten that confused with something else I heard about the order in which things happen.

The reason the Define took too long in my situation is because our response time here is not great.

Thanks again,

Krysti