As far as I remember the COLAESCE function causes problems in the joining condition in MERGE statement. Did you try without using the COALESCE statement?
Yes Qaisar. It works fine if i am not using coalesce. But my requirement is such that i have to default a null value for comparison purpose. Its then, while using coalesce, merge statement fails. Not sure, if this is a limitation.
I don't think there is a limitation on RANK about last 2 digits of s(6). It works fine in my project. Please share the SQL if you can and will try to help.
Table structure is
Create Set Table Database.Table
Account_Number decimal(16,0) not null,
Date_Added Date Format 'YYYY-MM-DD' not null,
Time_Added Time(6) format 'hh:mi:ss.s(6)' not null,
) Primary Index(Account_Number, Date_Added);
Insert into Database.Table values ( 100,'2013-01-01','10:14:27.196005', 'BCMD');
Insert into Database.Table values ( 100,'2013-01-01','10:14:27.196008', 'AMEX');
Select T1.*, rank(Account_Number, Date_Added,Time_Added)
where Account_Number = 100
Date_Added = '2013-01-01'
group by Account_Number, Date_Added,Time_Added
order by Account_Number, Date_Added,Time_Added;
Since Time_Added has a different value, rank '1' should be assigned to both the records. However, the above query is returning the rank as 1 and 2. Seems the value of Time_Added is getting truncated to a common value internally during the execution of query.
No. Use the ANSI windowed format if this is what you intend:
RANK() OVER (PARTITION BY Account_Number, Date_Added, Time_Added)
In the (deprecated) Teradata RANK syntax, the columns only specify sort order; i.e. your query is effectively computing
RANK() OVER (ORDER BY Account_Number, Date_Added, Time_Added)
And regarding your question on MERGE, all columns in the PI/PPI must be directly specified with an equality predicate in the ON clause. So it is not possible to "match" a NULL value in one of those columns.