Archives of the TeradataForum

Message Posted: Fri, 04 Dec 2009 @ 10:26:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Your simplification, Dieter, can only be met with a slightly frustrated .... doh ! Brilliantly simple and just what I was struggling to get to, but getting lost in a sea of SQL.

That doesn't detract, of course, from the equally innovative solution from ulrich.

Just for interest, where it leads us to is to provide a Financial calendar - but upon TNT's financial needs - that we can use to do this sort of query :

     SELECT * FROM DEVSCD_P.FINENTRY_V01 FE
     INNER JOIN (SELECT MAX(DTE_DT) AS DTE_DT_MAX,
                                     MIN(DTE_DT) AS DTE_DT_MIN
                                     FROM FINCALDAYSDIFF_V01
                                     WHERE WKS_DIFF = 52) FC ON FE.CON_COLL_DT BETWEEN
     FC.DTE_DT_MIN AND FC.DTE_DT_MAX;

This WKS_DIFF is effectively allowing us to say, get so many weeks worth of data, based upon today's date but using our Financial calendar, not the Gregorian calendar (which would be simple, if life was fair).

(FINENTRY - name changed to protect the guilty - is the Financial Fact table, by the way)

     16) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
         an all-rows scan into Spool 56 (all_amps) (compressed columns
         allowed), which is duplicated on all AMPs.  The size of Spool 56
         is estimated with high confidence to be 1 row.  The estimated
         time for this step is 0.00 seconds.

     17) We do an all-AMPs JOIN step from Spool 56 (Last Use) by way of an
         all-rows scan, which is joined to DEVCOE_T.CONENTRY by way of an
         all-rows scan with no residual conditions.  Spool 56 and
         DEVCOE_T.FINENTRY are joined using a product join, with a join
         condition of ("(DEVCOE_T.FINENTRY.CON_COLL_DT >= DTE_DT_MIN) AND
         (DEVCOE_T.FINENTRY.CON_COLL_DT <= DTE_DT_MAX)").  The input table
         DEVCOE_T.FINENTRY will not be cached in memory, but it is eligible
         for synchronized scanning.  The result goes into Spool 55
         (group_amps), which is built locally on the AMPs.  The result
         spool file will not be cached in memory.  The size of Spool 55 is
         estimated with no confidence to be 1,188,718 rows.  The estimated
         time for this step is 13.30 seconds.

What's clever about this (in my opinion) is that Teradata is clever enough to recognize that, given that I've selected a MIN and MAX, only one row can ever be returned. From this, even though there's a lot of SQL going on within FINCALDAYSDIFF (based upon yours and Ulrich's solution), the confidence level is always set to 'high'. This then results in a Product Join which, I would argue, is an example of where you do want.

And, sorry, yes I only offer that delicious warm English virtual beer ! As we used to say, drink your beer before it gets cold.

Regards

David Clough
Database Developer
Database Design Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>