Re: SQL question from an absolute SQL newbie

On Tue, 30 Aug 2011 18:34:00 -0500, 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.

It really doesn't take much work to create a calendar file consisting of
a real date and the CCYYMMDD equivalent (and whatever other date variants
you need.) A program to load the calendar data is pretty simple. I can
provide an example if you're interested. Calendar files are very useful
for joining dissimilar date representation types. You might also use an
SQL function, or a host variable to provide the CCYYMMDD date, since the
date is in the where clause, and not the result table.

-- Get the rows from TCKTIHST with POST_DATE = Yesterday
WITH A (CUST, TKT) AS (
SELECT AA.CUSTOMER#, AA.TICKET#
FROM TCKTHIST AA
-- CAL.IDATE is a real date data type
WHERE AA.POSTDATE = (SELECT CAL.CCYYMMDD FROM CALENDAR.CAL
WHERE CAL.IDATE = CURRENT_DATE - 1 DAY ))
-- Return the TCKTHIST rows that are not in NEWTCKTS
SELECT A.*
FROM A
EXCEPTION JOIN NEWTCKTS N
ON N.TICKET# = A.TKT
AND N.CUSTOMER# = A.CUST;

You'd at least need an index on TCKTHST.POSTDATE. Run the query in
VisualExplain to identify anything else that's needed.