Is there a limitation on the Merge statement and Rank Function?

General
Enthusiast

Is there a limitation on the Merge statement and Rank Function?

Hello,

While using the merge statement, i am using coalesce on the primary index columns. I am getting an error - 'search condition must fully specify the Target table primary index and partition columns(s)'. Sample query is given below:-

MERGE INTO database.Table1 as TGT

USING (sel name, age,dept from database.Table2) as DLT

ON

coalesce(TGT.name,'')= coalesce(DLT.name,'')

and

TGT.age = DLT.age

WHEN MATCHED THEN UPDATE

SET dept= DLT.dept

WHEN NOT MATCHED THEN

INSERT

values

(DLT.name, DLT.age, DLT.dept );


Also, while ranking on a timestamp field of the format hh:mi:ss.s(6), two of the records have a difference in the last digit i.e. last digit of s(6), still the same rank is assigned to both the records. It seems the values are getting truncated while doing the ranking.

is there any limitation on Merge Statement and rank functions?

5 REPLIES
Enthusiast

Re: Is there a limitation on the Merge statement and Rank Function?

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?

Enthusiast

Re: Is there a limitation on the Merge statement and Rank Function?

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. 

Enthusiast

Re: Is there a limitation on the Merge statement and Rank Function?

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.

Enthusiast

Re: Is there a limitation on the Merge statement and Rank Function?

Hi KS42982,

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,

Dept Varchar(10)

) Primary Index(Account_Number, Date_Added);

Queries

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

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)

from

Database.Table

where Account_Number = 100

and

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.

Teradata Employee

Re: Is there a limitation on the Merge statement and Rank Function?

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.