RE: SQL question from an absolute SQL newbie

Select *
from TCKTHIST a
where a.POST_DATE = dec( replace( char( current_date - 1 day, iso ), '-' , '' ), 8 ) // stolen from chuck's answer
and not exists( Select *
from NEWTCKTS b
where a.TICKET# = b.TICKET#
and a.CUSTOMER# = b.CUSTOMER#
and a.TRAN_DATE = b.TRAN_DATE )

You could do a funky join between TCKTHIST and NEWTCKTS instead of the exists( test if performance is a problem.

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Hightower
Sent: Tuesday, August 30, 2011 7:34 PM
To: Midrange Systems Technical Discussion
Subject: SQL question from an absolute SQL newbie

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.