Help with SQL

[AnalystDBA]

Hi,

I am trying to get the first delivery date for each order in a recordset that will have many orders for each customer and many deliveries for each order. I need to get the Customer details, the order details and show the first delivery date for each order.
The Tables I am working with are
orderhead - Order header info
orderdetail - Order detail info
delivhead - Delivery header info
delivdetail - delivery detail info
customer - Customer info

ordererhead has a 1 to many on oderdetail
delivhead has a 1 to many on delivdetail
There can be many deliveries for each order.
All 4 tables are linked by order_num and the customer table is linked to orderhead by cust_code

Some orders can be cancelled so I don't want these. (this is easy for me to remove)
The deliveries are numbered for each order using the field deliv_num. The easy thing to do would be to get the deliveries with deliv_num = 1 but if the first delivery is cancelled then this does not work so I have to go on the earliest delivery date (that hasn't been cancelled) for each order. Alternativly use the lowest deliv_num that isn't cancelled.

Any help that I can get would be much appreciated.

I am ok with SQL and SPL. Just need to be pointed in the right direction.

Cheers
AnalystDBA

 

[appi]

Hi,

your thoughts about the first deliv_num is okay. Do you have a field which shows cancelled or not ?
If yes you can search for first deliv_no and cancelled <> yes

appi

 

[AnalystDBA]

hi appi,
thanks for the reply.
I have a cancelled field and that can eliminate the cancelled deliveries. If the first delivery is cancelled eg. del_num = 1 and status = cancelled then I have to go to del_num = 2 for the second delivery. Is there a FIRST operator in Informix that will get the first sequencial value in a recordset. If there is then I can select FIRST del_num from delivhead where status <> Cancelled.

I am using Informix version 7.3

Thanks

AnalystDBA

 

[StevePB]

Hi,

Could you select everything that was not cancelled into a temp table first, then SELECT MIN(del_num) from temp table, to get the minimum number from the selected records ?

SteveB.