Sunday, July 24, 2011

What are the ways by which we can use zero to replace a null value for a given column ?

Answer -

1. By using Teradata SQL supported command as follows
Select Col1, ZEROIFNULL(Col2)  from Table_name;

2. By using ANSI SQL command as follows
a. Coalesce
Select Col1,COALESCE(Col2,0)    from Table_name;

b.Case operator
select
Case When Col2 IS NOT NULL
Then Col2
Else 0
End
from Table_name;


It is always suggested to use ANSI standard while coding in Teradata , since any changes in Teradata version due to upgrade/patches installation will lead to
- Time for regression testing
- rework of code.

Friday, July 8, 2011

What is a join index ? What are benefits of using Join index?

Answer:

It is a index that is maintained in a system .It maintains rows  joined on two or more tables. Join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover all or part of the query

Benefits if using join index is
- to eliminate base table access
- Aggregate processing is eliminated by creating aggregate join index
- It reduces redistribution of data since data is materialized by JI.
- Reduces complex join conditions by using covering queries

Can a macro be called inside a macro?

Answer:
The main purpose of run a set of repeated sql queries.   Macro supports only DML queries .
Hence We cant call any-other macro or not even a procedure in a macro.

One trick to have closest functionality of this is to copy all the sql queries from macro2 to macro1 and add parameters if it is necessary as shown below.


replace macro1( val int)
as
(  sel * from employee_table where empid= :val );

replace macro2( dept_no int)
as
(  sel * from employee_table where deptno= :dept_no );

so, to call a macro2 inside a macro1..it is not possible. Hence follow this approach

Replace macro1 (val int, dept_no int)
as
(
sel * from employee_table where empid= :val;
sel * from employee_table where deptno= :dept_no ;
);