Capture Changed Data (in Teradata)

Preserving history in a fact table is a common process in ETL.
If one ore more columns have changed you can add the changed one.

What if you need to know which field/column has changed?

You can write a construction that compares every field.
However, execution of such a query is performance killer.
Besides that, if the tables has, say, 50 columns it's a hell
of a job to write that.

Here you can find a small subset of an invoice fact table
(in reality there are a lot more fields). Amount, status and country
have changed.



This could be the result of the analysis script:



Here you can find the script that compares every column
per non unique index (e.g. invoicenumber).
Assumption is that the primary index is NUPI. If you designed it
otherwise, you have to change the script (hard-coding).

CREATE MULTISET TABLE DEVK425WGE.DataChangeCapture ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
databasename VARCHAR(50),
tablename VARCHAR(100),
obj_id DECIMAL(15,0),
field VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
item_old VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
item_new VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
date_original TIMESTAMP(6),
date_change TIMESTAMP(6))
PRIMARY INDEX ( databasename, tablename,field,obj_id );

--


create procedure pDataChangeCapture
(
IN OBJECTID CHAR(50)
, IN DATABASENAME VARCHAR(50)
, IN TABLENAME VARCHAR(100)
, IN FIELD VARCHAR(100)
)

BEGIN



CALL DBC.SysExecSQL('

insert into DataChangeCapture
(
databasename
, tablename
, obj_id
, field
, item_old
, item_new
, date_original
, date_change
)

sel
'||'27'xc||:DATABASENAME||'27'xc||'
, '||'27'xc||:TABLENAME||'27'xc||'
, a.'||:OBJECTID||' as obj_id
, '||'27'xc||:FIELD||'27'xc||' as field
, a.'||:FIELD||' as oldvalue
, b.'||:FIELD||' as newvalue
, b.preceding_date as date_original
, b.UPDATE_DT as date_change
from '||DATABASENAME||'.'||TABLENAME||' a,
(sel
'||:OBJECTID||' as objectid
, '||:FIELD||'
, UPDATE_DT' as UPDATE_DT
, max('UPDATE_DT') over (partition by '||:OBJECTID||' order by '||:OBJECTID||' , UPDATE_DT rows between 1
preceding and 1 preceding) as
preceding_date
from '||DATABASENAME||'.'||TABLENAME||'
) b
where
a.'||:OBJECTID||' = b.objectid
and a.UPDATE_DT = b.preceding_date
and (a.'||:FIELD||' <> b.'||:FIELD||'
or (a.'||:FIELD||' is NULL
and b.'||:FIELD||' is not null)
or (b.'||:FIELD||' is NULL
and a.'||:FIELD||' is not null) )'
)
;



END;

--

create procedure pDataChangeCaptureStart (databasename varchar(50), tablename varchar(100))

begin

declare objectid varchar(50);

sel columnname into :objectid from dbc.indices
where databasename = :databasename
and tablename = :tablename
and indextype = 'P';

create volatile table tDataChangeCapture (rownum int, FIELDNAME varchar(100)) ON COMMIT PRESERVE ROWS;

ins into tDataChangeCapture
sel csum(1,1), columnname from dbc.columns
where databasename = :databasename
and tablename = :tablename
and columnname <> :objectid
and columnname <> 'UPDATE_DT';

delete DataChangeCapture;

looper:begin

declare maxfields int;
declare x int default 0;
declare field varchar(100);

sel count(*) from tDataChangeCapture into :maxfields;

loopit: while x<=maxfields

do

set x = x+1;

sel fieldname from tDataChangeCapture into :field where rownum = :x;

call pDataChangeCapture(objectid, databasename, tablename, field);

end while loopit;

end looper;

drop table tDataChangeCapture;

end;

Kick of the procedure by command:

call pDataChangeCaptureStart ('Databasename','Tablename')

Semi-Dynamic FastExport script Teradata

While using a fast export script it's possible that the output created is not in a readable format.
In order to have a readable output you have to cast as char and delimit all the fields you are exporting.

If you have a table with a lot of fields (or you need to export several tables) it takes a lot of time to type in all the column names and column lengths.

Below script handles with these issues (including the addititon of a column header).
It's not fully dynamical (you have to copy the results - without header - of the 4 queries and paste them into the script). You can make it 100% automatic by exporting a dynamical sql with BTEQ and read it with FastExport.


--------------------------------------------------------------------------
.LOGTABLE abc.datastore_log ;
.logon ab/ab,;
.BEGIN EXPORT
SESSIONS 1
TENACITY 5
SLEEP 1;
.EXPORT OUTFILE c:\OUT.txt MODE RECORD
FORMAT TEXT;

/**********************************************************
**
** Header Record
**
**********************************************************/

sel

/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel 'C'||CAST(CSUM(1,1) AS CHAR(4)) a , '(CHAR('||
** trim(characters(
** TRIM(TRAILING' ' FROM c.columnname) ))
**
** ||'))
**
** 'b,
** case when 'C'||trim(a.maxc) <> a then ','
** ||'27'xc
** || ','||'27'xc
** || '(CHAR(1)),' else '' end c
**
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/

-- and paste it here:


FROM

(sel


/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel '27'xc||trim(c.COLUMNNAME)||'27'xc a, ' AS ' b
** , 'C'||CAST(CSUM(1,1) AS CHAR(4)) c ,
**
**
** case when 'C'||trim(a.maxc) <> c then ', '
** else '' end d
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/

-- and paste it here:


) A;

/**********************************************************
**
** Detail Record
**
**********************************************************/

sel


/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
**
** sel
**
** 'C'||CAST(CSUM(1,1) AS CHAR(4)) a
** ,
** case
**
** when columntype = 'D' then '(CHAR(18))'
** when columntype = 'DA' then '(CHAR('||
** trim(characters(columnformat))
** ||'))
** 'when columntype in ('I','I1','I2','I8','BF','BO','BV','CF','CO','CV') then '(CHAR('||
** substring(columnformat,position ('(' in columnformat)+1,position (')' in columnformat)-
** position ('(' in columnformat)-1)||'))'
** when columntype in ('CF','CV','DM','TS','DS','AT','F','DY','PS','HM','HR','HS','MS') then
** '(CHAR('||
** TRIM(CAST(COLUMNLENGTH AS integer))
** ||'))'
** else
** '(CHAR('||
** TRIM(CAST(COLUMNLENGTH AS integer))
** ||'))'
** end
** b
**
** ,
**
** case when 'C'||trim(a.maxc) <> a then ','
** ||'27'xc
** || ','||'27'xc
** || '(CHAR(1)),' else '' end c
**
** FROM dbc.columns c
**
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/

-- and paste it here:

FROM

(sel



/*************************************************************
**
** COPY OUTPUT FROM QUERY BELOW
** (without headers)
**
** sel
**
** case when columntype in ('AS','DS','TS','DM','DS','DY','HM','HR','HS','MS','TS')
** then c.COLUMNNAME
** else 'TRIM (BOTH FROM '|| trim(c.COLUMNNAME) ||')'
** END a
**
** , ' AS ', 'C'||CAST(CSUM(1,1) AS CHAR(4)) b ,
**
** case when 'C'||trim(a.maxc) <> b then ', '
** else '' end d
** FROM dbc.columns c
** ,
**
** (sel max(c) over (partition by 1) maxc, columnname
** from
** (sel columnname, CSUM(1,1) c from dbc.columns
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME')a)a
** where databasename = 'DATABASENAME'
** and tablename = 'TABLENAME'
** and c.columnname = a.columnname
**
************************************************************/

-- and paste it here:

FROM
DATABASENAME.TABLENAME ) A
;


.END EXPORT;

/**********************************************************
** Logoff the Teradata Database
**********************************************************/
.LOGOFF;

--------------------------------------------------------------------------

Search & Suggest Prompt in Cognos8.4

If you need a Google-like Select and Suggest Prompt like this:



(type in the first characters and Cognos will complete it with suggestions), just perform the following steps:

1. Create a prompt page
2. Insert a value prompt object
3. Name it something like 'dummy'
4. Skip the step 'Create a parameterized filger' (uncheck the box).
5. Create a new Query (Selecion Query for dummy)
and select the value to use and the value to display
(for example, city code and city name).
6. Set the properties of the value prompt:
- Required: No
- Visible: No
= Name: prmt
7. Insert a text box prompt (do not use the wizard, click op finish). Name: p_txt
8. Add three HTML items to the right of the text box:

1:



2:



3:



9. Complete it by adding a query with the filter (like city code = ?city?)
10. Configuration of the first Javascript:
 this.actb_lim = 4;    // Number of elements autocomplete can show (-1: no limit)
 this.actb_firstText = false; // should the auto complete be limited to the beginning of keyword?
 this.actb_startcheck = 1; // Show widget only after this number of characters is typed in.

Straight SQL Vs. Stored Procedure in Teradata

If an error occurs when you validate a stored procedure in Teradata while
the query does run successfully in Teradata SQL Assistant, the root cause is probably originated in the fact that SQL Assistant uses the Teradata ODBC Driver, which parses your SQL request and rewrites it. In contrast, the stored procedure does not parse or rewrite your SQL request. Instead, it sends your unchanged SQL request to the Teradata Database.

Example: The Teradata ODBC Driver implements the MONTH function.

The Teradata Database does not provide a MONTH function. You need to change your query to use the SQL syntax that is supported by the Teradata Database. Specifically, you must use the EXTRACT(MONTH FROM) function.

In your query, you must replace

MONTH(CURRENT_DATE)

(that results in 'E(3706):Syntax error: expected something between the '(' keyword and the 'month' keyword')

with

EXTRACT(MONTH FROM CURRENT_DATE)

(mutatis mutandis for YEAR, QUARTER, WEEK). Also if you try do run the query via JAVA, the JAVA Driver will send you query unchanged to the database).