All Forums Database
fkeith 4 posts Joined 11/11
04 Feb 2013
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)));

Qaisar Kiani 337 posts Joined 11/05
06 Feb 2013

Have you tried ROW_NUMBER function?

fkeith 4 posts Joined 11/11
07 Feb 2013

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

dnoeth 4628 posts Joined 11/04
07 Feb 2013

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

Dieter

KS42982 137 posts Joined 12/12
07 Feb 2013

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*/

 

fkeith 4 posts Joined 11/11
08 Feb 2013

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?

KS42982 137 posts Joined 12/12
08 Feb 2013

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.

fkeith 4 posts Joined 11/11
08 Feb 2013

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. 

KS42982 137 posts Joined 12/12
11 Feb 2013

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 ?

wicik 35 posts Joined 06/12
19 Mar 2013

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 :/
 

wicik 35 posts Joined 06/12
19 Mar 2013

Well...
 
Sounds stupid but I have helped myself with default select --> copy paste to excel --> sorting and numeric conversion to date via left and right functions (to split needed numbers and connecting it as a date) and by makeing a PivotChart from all of it.
 
It gave me pretty the same but not that pro as it should.
Still, any help would be welcome :)
I need to learn how to do it properly.
 
Regards

KS42982 137 posts Joined 12/12
19 Mar 2013

If you do not care about the HHMISS (last 6 digits of your numeric_column) then you can do the same that you did in excel by using SUBSTR function and fetch only first 8 and assign the name to it and use that to group by.

wicik 35 posts Joined 06/12
07 May 2013

Heh... you are right :)
So obvious, yet so new!

You must sign in to leave a comment.