/***
  Creates and loads a table containing all keywords for different versions of
  Standard SQL and Teradata SQL.

  The reserved status of a Teradata keyword has been tested on each version of
  Teradata using "select 1 as KEYOWRD;", that's why some differ from the manuals.
  E.g. in V2R5 'WITHOUT' and 'PROPORTIONAL' are Reserved according to
  "SQL Reference Vol 1, Appendix B: Restricted Words for V2R5.0",
  but are not causing an 3707 error.
  "Non-Reserved Keywords" might be wrong (it's according to the manuals),
  because there's no way to determine if a keyword is non-reserved using SQL.

  Column KEYWORD:
    Teradata or SQL keyword

  Column VERSION:
    Standard SQL (ISO 9075)
      '1992'
      '1999'
      '2003'
    Teradata V2
      'R4.0'
      'R4.1'
      'R5.0'
      'R5.1'
      'R6.0'
      'R6.1'
      'R6.2'

  Column RESERVED, hopefully correct ;-)
    'R' -> Reserved keyword
    'N' -> Non-Reserved keyword
    'F' -> Keyword reserved for future use



  2003-08-25 dn Initial version
  2005-01-12 dn Added V2R6
  2006-01-18 dn Added V2R6.1 and some missing V2R6.0 keywords
  2006-09-19 dn Added V2R6.2
***/

/***
-- exported using BTEQ

.os del sql_keywords.txt
.export report file = sql_keywords.txt

select
  trim(Version) || ' ' ||
  trim(Reserved) || ' ' ||
  trim(Keyword) (title '')
from sql_keywords
order by keyword, version;

.export reset;

-- importing using BTEQ:
-- adjust logon username/password and run:
-- bteq < SQL_keywords.sql
***/

.SET SESSIONS 1;

.LOGON tntlocal/teradata_education,educ;

SELECT 1
FROM dbc.tables
WHERE TableName = 'SQL_keywords'
AND DatabaseName = DATABASE;

.IF activitycount = 1 THEN DROP TABLE SQL_keywords;

CREATE SET TABLE SQL_keywords
 (
  keyword VARCHAR(32) UPPERCASE NOT NULL

  ,version CHAR(4) NOT NULL
   COMPRESS ('1992','1999','2003','R4.0','R4.1','R5.0','R5.1','R6.0','R6.1','R6.2')

  ,reserved CHAR(1) UPPERCASE NOT NULL
   COMPRESS ('F','N','R')
 );

.SET ERRORLEVEL UNKNOWN SEVERITY 99;

.SET MAXERROR = 1;

.IMPORT VARTEXT ' ' FILE = sql_keywords.txt;

.QUIET ON

-- if the REPEAT causes an error, then your version is pre-V2R6.0
-- -> remove PACK (and use more than 1 session for faster load)
.REPEAT * PACK 1000

USING
  (version VARCHAR(4),
  reserved VARCHAR(1),
  keyword VARCHAR(32))
INSERT INTO SQL_keywords VALUES (:keyword, :version, :reserved);

.QUIET OFF

SELECT
  keyword
  ,MAX(CASE WHEN version = 'R4.0'
       THEN reserved ELSE '' END) AS "R4.0"
  ,MAX(CASE WHEN version = 'R4.1'
       THEN reserved ELSE '' END) AS "R4.1"
  ,MAX(CASE WHEN version = 'R5.0'
       THEN reserved ELSE '' END) AS "R5.0"
  ,MAX(CASE WHEN version = 'R5.1'
       THEN reserved ELSE '' END) AS "R5.1"
  ,MAX(CASE WHEN version = 'R6.0'
       THEN reserved ELSE '' END) AS "R6.0"
  ,MAX(CASE WHEN version = 'R6.1'
       THEN reserved ELSE '' END) AS "R6.1"
  ,MAX(CASE WHEN version = 'R6.2'
       THEN reserved ELSE '' END) AS "R6.2"
  ,MAX(CASE WHEN version = '1992'
       THEN reserved ELSE '' END) AS "1992"
  ,MAX(CASE WHEN version = '1999'
       THEN reserved ELSE '' END) AS "1999"
  ,MAX(CASE WHEN version = '2003'
       THEN reserved ELSE '' END) AS "2003"
FROM sql_keywords
GROUP BY 1
ORDER BY 1
;

.QUIT;