All Forums General
abhi_insignia 13 posts Joined 06/11
30 Jan 2013
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?

Qaisar Kiani 337 posts Joined 11/05
30 Jan 2013

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?

abhi_insignia 13 posts Joined 06/11
31 Jan 2013

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. 

KS42982 137 posts Joined 12/12
31 Jan 2013

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.

abhi_insignia 13 posts Joined 06/11
02 Feb 2013

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.

Fred 1096 posts Joined 08/04
02 Feb 2013

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.

You must sign in to leave a comment.