Setting up a window to find the "first value"

[gsdcrazy]

Gurus,

We are converting existing Oracle applications to Teradata. We have some code that in Oracle SQL is using a window with the "FIRST_VALUE" function. This takes the result set of the window and returns the first value of the result set (always used in conjunction with an ORDER BY).

Is there anyway to do this in Teradata? I thought about trying to us the "RANK" function somehow, but can't put it together. Any ideas?

Thanks,
gsdcrazy

 

[SiMCard]

Assuming that you only want the first value in your answer set, RANK can be used as follows:

SELECT
 Col1
,Col2
,Col3
FROM
 Table1
QUALIFY RANK (Col1 ,Col2) = 1
;

The RANK statement (that replaces the 'ORDER BY' statement) can use as many qualifying columns as you like (within normal parameters) and can be specified as ASC or DESC as required.

The issue with this is that should you have top RANKed values that match, you will get all of them in the answer set. If this is the case, you only want one but you don't care which one, just slap SAMPLE 1 on the end.

Si M...

 

[gsdcrazy]

Si M...

Thanks for the direction. Probably a little late, but this is a sample of the code I was trying to convert from Oracle to Teradata:

SELECT /*+ PARALLEL(t) USE_HASH(t, a) */
t.tran_code
, a.agrmt_id
, (FIRST_VALUE(a.agrmt_id) OVER(PARTITION BY SUBSTR(a.agrmt_src_id, 1, 10) ORDER BY a.agrmt_type_id)) base_agrmt_id
FROM tl_transactions t
, agreements a
WHERE t.agrmt_ext_id = SUBSTR(a.agrmt_src_id, 1, 10)
AND a.src_system_id = '10'
AND t.load_date = '19-FEB-2004'
AND NVL(t.dollar_amount, 0) != 0

Because of the specifics of my need, I was getting a "Ordered Analytical Functions can not be nested". I needed to use a "SUBSTR" in the "PARTIIONED BY" clause and Teradata did not like both the "SUBSTR" and the "QUALIFY RANK". I was trying the code below:

SELECT t.tran_code
, a.agrmt_id
, aa.agrmt_id AS base_agrmt_id
, RANK() OVER (PARTITION BY SUBSTR(aa.agrmt_src_id, 1, 10) ORDER BY aa.agrmt_type_id) as first_agrmt_id
FROM staging.tl_transactions t
, salesmi.agreements a
, salesmi.agreements aa
WHERE t.agrmt_ext_id = SUBSTR(a.agrmt_src_id, 1, 10)
AND t.agrmt_ext_id = SUBSTR(aa.agrmt_src_id, 1, 10)
AND a.src_system_id = 10
AND t.load_date = '2004-02-19'
AND ZEROIFNULL (t.dollar_amount) <> 0
QUALIFY RANK (first_agrmt_id) = 1

I kept working with it and came up with the following. Not sure about efficiency yet, but it provides the right results.

SELECT t.tran_code
, a.agrmt_id
, aa.base_agrmt_id
FROM staging.tl_transactions t
, salesmi.agreements a
, (SELECT a1.agrmt_id AS base_agrmt_id
,a1.agrmt_src_id
,RANK() OVER (PARTITION BY SUBSTR(a1.agrmt_src_id, 1, 10) ORDER BY a1.agrmt_type_id) as first_agreement
FROM salesmi.agreements a1
WHERE a1.src_system_id = 10) as aa
WHERE t.agrmt_ext_id = SUBSTR(a.agrmt_src_id, 1, 10)
AND t.agrmt_ext_id = SUBSTR(aa.agrmt_src_id, 1, 10)
AND aa.first_agreement = 1
AND a.src_system_id = 10
AND t.load_date = '2004-02-19'
AND ZEROIFNULL (t.dollar_amount) <> 0

If there is a better way, please let me know.

Again, Thanks for the help.
gsdcrazy