Re: SQL question from an absolute SQL newbie

On 30-Aug-2011 16:34 , 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.

create table qtemp/th (t# for ticket# int , c# for customer# int , td for tran_date dec(8) , pd for post_date dec(8) )
;
create index qtemp/th_pdix on qtemp/th (pd)
;
-- all post_date entries from yesterday ; selection by index possible
create view qtemp/th_pd_yest as
( select * from qtemp/th
where pd = dec( replace( char( current_date-1 day, iso ),'-','' ), 8 )
)
;
create table qtemp/nt (t# for ticket# int , c# for customer# int , td for tran_date dec(8) )
;
select th.*
from qtemp/th_pd_yest th exception join
qtemp/nt
on th.t#=nt.t# and th.c#=nt.c# and th.td=nt.td

Regards, Chuck