Bomb Diggy wrote:
> Hi,
>
> Two tables - one to many relationship. Master is 'transaction (TXN)',
> other is 'status (STATUS)'. For each record in TXN, there may be zero
> or more records in STATUS. The tables are joined by TXN_ID, which is
> the PK of TXN and a FK in STATUS. Several different applications work
> on any given transaction, and they all update the STATUS table. The
> STATUS table also keeps a log of all status changes, so a logical
> update to the status of a transaction for a given application is
> actually an INSERT on the STATUS table, as opposed to an UPDATE. Each
> record in STATUS says "application 'x' updated status of transaction
> 'y' to 'this' on 'somedate'".
>
> I've been able to grab the latest status update by using a 'timestamp'
> column we have in the STATUS table using a subselect like:
>
> AND timestamp = ( SELECT MAX(timestamp)
> FROM STATUS
> WHERE bla bla... )
>
>
> Now, in words, the query I can't quite figure out:
>
> I want to return all the records in TXN that have been marked in the
> STATUS table as having been validated by the 'Validation' program.
> That to me says do a regular join -> no problemo.
>
> SELECT *
> FROM TXN, STATUS
> WHERE TXN.PK = STATUS.FK_PK
> AND STATUS.SYSTEM='VALIDATION' -- a.k.a. a 'process' or
> 'application'
> AND STATUS.STATUS='COMPLETED'
> AND TXN.TXN_TYPE='RUNTIME_PARAM_1'
>
> The difficult part for me is that I only want to return this row (from
> the regular join) if the particular transaction does *not* have
> another record in the STATUS table with certain values - those values
> that would indicate that we are already done processing this
> transaction ('COMPLETED'), or that the transaction has been put into
> 'ERROR' status.
>
> So, I need to add to the query above, something like:
> (but don't include this transaction if it is COMPLETED or DONE for
> this process)
>
> AND STATUS.SYSTEM='RUNTIME_PARAM_2'
> AND STATUS.STATUS NOT IN('COMPLETED','ERROR')
>
> So, we need the regular join, and if there are no other records at all
> for this transaction, return the transaction. If, however, there are
> other status records for this transaction, then return the transaction
> only if those status records don't indicate 'COMPLETED' or 'ERROR' as
> the last status record for the particular application being queried.
>
> So, there will be two runtime parameters - the application/process
> which is doing work on the transaction being the more interesting one
> b/c it resides in the STATUS table. The other is the type of
> transaction we're want to filter on - an attribute of the TXN table.
>
> Thanks for any hints.
SELECT *
FROM TXN, STATUS
WHERE TXN.PK = STATUS.FK_PK
AND STATUS.SYSTEM='VALIDATION' -- a.k.a. a 'process' or
AND STATUS.STATUS='COMPLETED'
AND TXN.TXN_TYPE='RUNTIME_PARAM_1'
MINUS
SELECT *
FROM TXN, STATUS
WHERE TXN.PK = STATUS.FK_PK
AND STATUS.SYSTEM='RUNTIME_PARAM_2'
AND STATUS.STATUS NOT IN('COMPLETED','ERROR')
/
Received on Fri Jul 18 2003 - 19:55:54 CDT
