Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: need help writing sql query...

Re: need help writing sql query...

From: Bomb Diggy <techguy_chicago_at_yahoo.com>
Date: 19 Jul 2003 21:56:09 -0700
Message-ID: <94599bb3.0307192056.24c8cf65@posting.google.com>


That was it! Well, that was the right idea. One of the columns I'm selecting is a BLOB type, which I guess can't be compared, so I had to select only key columns from the query, then rejoin it with the full table.

Also, you can't select just '*' b/c the results would never match -> the column values 'VALIDATION' and 'COMPLETED' would never match 'RUNTIME_PARAM_2' and 'something other than COMPLETED OR ERROR'.

But thanks! I also added the 'timestamp = ( SELECT MAX(timestamp)' stuff to get only the latest records from the STATUS table.

"Anna C. Dent" <anacedent_at_hotmail.com> wrote in message news:<sG0Sa.5787$Ne.1888_at_fed1read03>...
> 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 Sat Jul 19 2003 - 23:56:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US