Re: SQL question from an absolute SQL newbie

Something like this, (maybe... not tested, of course, and it is late):

select h.*
from tckthist h
where post_date = current date - 1 days
and not exists (
select *
from newtckts n
where n.customer# = h.customer#
and n.ticket# = h.ticket#
and n.tran_date = h.tran_date
)

Of course, post_date is not a date data tyoe field, so you need to change it to a date data type by substituting the appropriate code. There are lots of examples of how to convert to a date on the mailing list. Many consist of code like this, but there are other ways:
date( substr(ccyymmdd,1,4)
|| '-' || substr(ccyymmdd,5,2)
|| '-' || substr(ccyymndd,7,2)
)
You may need to adjust this if the incoming field is numeric.

Sam

On 8/30/2011 7:34 PM, Tom Hightower wrote:

Once again I come to the masters of all things SQL...

I have 2 files:

File TCKTHIST:
TICKET#
CUSTOMER#
TRAN_DATE (CCYYMMDD, likely not the same value as POST_DATE)
POST_DATE (CCYYMMDD)


File NEWTCKTS:
TICKET#
CUSTOMER#
TRAN_DATE (CCYYMMDD)

I need to work up an SQL statement that will find all entries in TCKTHIST
with a post_date = yesterday and which are not in file NEWTCKTS. Fields
TICKET#, CUSTOMER# and TRAN_DATE are common fields to both files; POST_DATE
is only in TCKTHIST.