How to retrieve the Relative Record Number?

Database
Fan

How to retrieve the Relative Record Number?

Hi,

I am Very New to Teradata. I was wondering how to retrieve the Relative Record Number for a row in a table.  I have used RRN before but it doesn't seem to work in Teradata. Here is my code to find bad dates. The column BLANK8 is a numeric date in YYYYMMDD format. Any help would be appreciated.

SELECT Cast(Cast(t.blank8 as format'9(6)') as char(8)), rrn(ji_core_v.keeph)  FROM

ji_core_v.keepH T

where not exists(Select *

From SYS_CALENDAR.CALENDAR C

where Cast(Cast(t.blank8 as format'9(6)') as char(8)) = C.CALENDAR_DATE (FORMAT 'YYYYMMDD' ) (CHAR(8)));

12 REPLIES
Enthusiast

Re: How to retrieve the Relative Record Number?

Have you tried ROW_NUMBER function?

Fan

Re: How to retrieve the Relative Record Number?

Doesn't ROW_NUMBER just provide the position in the return result set not the actual row position in the table?

Junior Contributor

Re: How to retrieve the Relative Record Number?

There's is no "row position" in a relational table, looks like you worked on a mainframe DB2 before, afaik it's using VSAM flat files.

The closest match is a ROWNUM on Oracle or the Standard SQL ROW_NUMBER in most RDBMSes.

Why do you think you need it?

What are you trying to achieve?

Dieter

Enthusiast

Re: How to retrieve the Relative Record Number?

If I understood you correctly, then below solution would solve your problem.

SELECT a.blank8, b.rownum
(
SELECT
-- add PK ,
CAST(CAST(t.blank8 AS FORMAT'9(6)') AS CHAR(8))
FROM ji_core_v.keepH T
WHERE NOT EXISTS(SELECT *
FROM SYS_CALENDAR.CALENDAR C
WHERE CAST(CAST(t.blank8 AS FORMAT'9(6)') AS CHAR(8)) = C.CALENDAR_DATE (FORMAT 'YYYYMMDD' ) (CHAR(8))
)
) a

INNER JOIN
(
SELECT
-- add PK,
CAST(CAST(t.blank8 AS FORMAT'9(6)') AS CHAR(8)),
ROW_NUMBER() OVER (ORDER BY /*whatever you want to order by*/) rownum
FROM ji_core_v.keepH T
) b
ON a.blank8 = b.blank8
AND /*a.PK = b.PK*/
Fan

Re: How to retrieve the Relative Record Number?

Hi, 

This table is very old and was never setup with a primary key or any other unique identifier so it's possible that some rows have duplicate data. I don't want to accidently update a row that contains a valid date. The only way I could think of identifying the row that contains an invalid date was using the relative record number.  Any ideas?

Enthusiast

Re: How to retrieve the Relative Record Number?

Sorry, I am confused now. If there are duplicate dates and you afraid of updating the both the dates thinking one could be valide ? As per your initial query, you check for the bad date from SYS_CALENDAR. Now if two dates are duplicates or say same then both could be either good or bad. So I don't get the scenario when there are duplicates and you want to identify one but not other.  

If this is a very old table and not getting new data, then you can think of altering it and add one key with a unique identifier like a surrogate key (using row_number probably) and then use that to update that.

Fan

Re: How to retrieve the Relative Record Number?

Sorry, I don't mean to be confusing. I guess I should clarify what I am trying to accomplish. I need to find the invalid dates and update them to be valid. The table is still in use and new rows added daily. This table has no keys on it and I can't alter it to create a key. I am afraid that if I tried to make up a key using one or more columns from the table that it wont be unique.   I guess I know how to find the invalid dates I just need help on how to fix them.  Old school was to use RRN on a table without any keys and use the relative record number to find the rows and update the column but I am lost on how to do it best on Teradata. 

Enthusiast

Re: How to retrieve the Relative Record Number?

I think I got what you try to do here now. Can you give an example how you want to update the bad dates ? I meant, with what you want to update the bad dates ? Where can we find the correct date that will replace the bad date ? Is there in some other table or what's the source ?

Enthusiast

Re: How to retrieve the Relative Record Number?

Hi there...

I have very similar problem like friend from the very start of the post.

I'm pretty nobbish in this type of SQL so please try not to be rough for me :)

Well.. I have to convert numeric field to date, than count some upload data and group by table.

Numeric_column like 20121121133549 which is probably like YYYYMMDDHHMMSS

Other columns are acces_point_type and data_uplink.

My goal is to convert numeric to Date (without time HHMMSS), count data_uplink and group by acces_point_type.

Simple group by would not be that problem but whole idea of convert data and connect it with casted date format and group by this,  is pretty dark magic for me.

Any help would be appreciated.

PS: Sorry for my terrible English :/