Archives of the TeradataForum

Message Posted: Sun, 22 Jan 2006 @ 20:12:33 GMT

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

I have used CROSS JOIN for generating large test data sets as others have mentioned, but that's not the most common use I have. I use CROSS JOIN extensively to apply 1-row tables of constants & limits to every row of a data table so I can do tests. You can achieve the same effect by putting that tables in the from clause with no join conditions, but using CROSS JOIN is self-documenting and makes your intent clear.

For example, if you want to find out what userids are close to password expiration, you can use something like:

     select
      ddb.databasename,
      cast('20'||substring(ddb.passwordstring from 12 for 8) as date) as
     PWChangeDate,
      PWChangeDate + ssd.ExpirePassword as PWExpireDate,
      (case when PWExpireDate >= Date then 'Active' else 'Expired' end) as
     UserStatus
     from
      dbc.dbase as ddb
       CROSS JOIN
      dbc.syssecdefaults as ssd
     where
      ddb.rowtype = 'U' and
      UserStatus = 'Expired'
     order by
      ddb.databasename;

Note that in production code you would want to copy the password change date extraction code from the DBC.USERS view, but this will do for an example. You can achieve the same effect with:

     from
      dbc.dbase as ddb,
      dbc.syssecdefaults as ssd

But you loose the documentation of intent.

/dave hough

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