Stored procedure--Bit tricky one

Database
Enthusiast

Stored procedure--Bit tricky one

Hi All,

How are you guys?

Hope, all are doing good.

I am new to SP concepts. Please anyone help me to write SP in teradata.

Example : Assume target is empty and source we have some data like below
LA Global COA Acct Dt
abc 1234 1-Jan
abc 898 2-Feb
abc 4567 3-Mar
abc 1234 4-Apr
abc 1234 5-May
abc 1234 6-Jun
abc 898 7-Jul
abc 898 10-Aug
abc 1234 23-Sep
xyz 1234 23-Sep
But I have to load data into target when LA,Global COA different than target by lookup on target table.
I mean ouput would be
LA Global COA Start Efctv(Acct Dt)
abc 1234 1-Jan
abc 898 2-Feb
abc 4567 3-Mar
abc 1234 4-Apr
abc 898 7-Jul
abc 1234 23-Sep
xyz 1234 23-Sep

we have to ignore
abc 1234 5-May
abc 1234 6-Jun
abc 898 7-Jul because LA and Global COA are same as above.

Objective is need to comapare target latest record with source records. Need to ignore if LA and Global COA same as existing record with target.

I hope you will be able to understand my query. Please feel free to reply if you have any queries.

Thanks in advance,
Suni
4 REPLIES
Junior Contributor

Re: Stored procedure--Bit tricky one

Hi Suni,
you probably don't need an SP for that, it's seems to be a query using OLAP-functions.

But i can't understand your requirements, could you please post DDL + some INSERTs and a description why which row needs to be returned.

Dieter
Enthusiast

Re: Stored procedure--Bit tricky one

Hi Dieter ,

Good Morning.

Actually, source will be having records like
LA GS1 GS2 GS3 GS4 GS5 GS6 GS7 GS8 GS9 START_EFCTV_DT end effective date
abc 1 2 3 6 8 9 6 45 1234 1-Jan 31-Dec-99
abc 1 2 4 6 8 9 6 45 1898 2-Feb 31-Dec-99
abc 1 2 5 6 8 9 6 45 14567 3-Mar 31-Dec-99
abc 1 2 3 6 8 9 6 45 1234 4-Apr 31-Dec-99
abc 1 2 3 6 8 9 6 45 1234 5-May 31-Dec-99
abc 1 2 3 6 8 9 6 45 1234 6-Jun 31-Dec-99
abc 1 2 4 6 8 9 6 45 898 7-Jul 31-Dec-99
abc 1 2 4 6 8 9 6 45 898 10-Aug 31-Dec-99
abc 1 2 3 6 8 9 6 45 1234 23-Sep 31-Dec-99
And doing select on all columns with Min function on date columns and group by LA and GS1 to GS9. so data would like below
LA GS1 GS2 GS3 GS4 GS5 GS6 GS7 GS8 GS9 START_EFCTV_DT end effective date
abc 1 2 3 6 8 9 6 45 1234 1-Jan 31-Dec-99
abc 1 2 4 6 8 9 6 45 1898 2-Feb 31-Dec-99
abc 1 2 5 6 8 9 6 45 14567 3-Mar 31-Dec-99

As per requirement we will update end effective date as start effective date where LA ans GS1 is same.
So data will look like below
LA GS1 GS2 GS3 GS4 GS5 GS6 GS7 GS8 GS9 START_EFCTV_DT end effective date
abc 1 2 3 6 8 9 6 45 1234 1-Jan 2-Feb
abc 1 2 4 6 8 9 6 45 1898 2-Feb 3-Mar
abc 1 2 5 6 8 9 6 45 14567 3-Mar 31-Dec-99
But our requirement is data should be loaded as given below
LA GS1 GS2 GS3 GS4 GS5 GS6 GS7 GS8 GS9 START_EFCTV_DT end effective date
abc 1 2 3 6 8 9 6 45 1234 1-Jan 2-Feb
abc 1 2 4 6 8 9 6 45 1898 2-Feb 3-Mar
abc 1 2 5 6 8 9 6 45 14567 3-Mar 4-Apr
abc 1 2 3 6 8 9 6 45 1234 4-Apr 6-Jun
abc 1 2 3 6 8 9 6 45 1234 6-Jun 7-Jul
abc 1 2 4 6 8 9 6 45 898 7-Jul 23-Sep
abc 1 2 3 6 8 9 6 45 1234 23-Sep 31-Dec-99
Because we have same LA and GS1 to GS9 for jan and april record.But we have to compare april record with march record and need to load the data into target because LA and GS1 to GS9 data is different.

We created GT tables to store distinct records using group by on LA and GS1 to GS9 columns and one more GT table to compare distinct records with whole data.

/* Create VT tabel to load distinct records */

CREATE MULTISET GLOBAL TEMPORARY TABLE GL_JDE_COA_XREF_temp ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(

GLOBL_SEGMNT1_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT2_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT3_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT4_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT5_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT6_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT7_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT8_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT9_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,

LAcnt VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
START_EFCTV_DT DATE FORMAT 'YYYY-MM-DD',
END_EFCTV_DT DATE FORMAT 'YYYY-MM-DD'
)
ON COMMIT PRESERVE ROWS;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;
/* to load source records to VT */
INSERT INTO GL_JDE_COA_XREF_temp
SELECT
--SUB1.RN,
SUB1.GLOBL_SEGMNT1_KEY GS1,
SUB1.GLOBL_SEGMNT2_KEY GS2,
SUB1.GLOBL_SEGMNT3_KEY GS3,
SUB1.GLOBL_SEGMNT4_KEY GS4,
SUB1.GLOBL_SEGMNT5_KEY GS5,
SUB1.GLOBL_SEGMNT6_KEY GS6,
SUB1.GLOBL_SEGMNT7_KEY GS7,
SUB1.GLOBL_SEGMNT8_KEY GS8,
SUB1.GLOBL_SEGMNT9_KEY GS9,
SUB1.LEGCY_ACCT LA,
SUB1.START_EFCTV_DT ST1,
SUB1.END_EFCTV_DT ST2
FROM GL_JDE_COA_XREF_S33_GT sub1;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

/* to load distinct records from source */

CREATE MULTISET GLOBAL TEMPORARY TABLE GL_JDE_COA_XREF_temp1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(

GLOBL_SEGMNT1_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT2_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT3_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT4_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT5_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT6_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT7_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT8_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
GLOBL_SEGMNT9_KEY VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,

LAcnt VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
START_EFCTV_DT DATE FORMAT 'YYYY-MM-DD',
END_EFCTV_DT DATE FORMAT 'YYYY-MM-DD'
)
ON COMMIT PRESERVE ROWS;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;
/* load into temp1 distinct records */

INSERT INTO GL_JDE_COA_XREF_temp1
SEL *
FROM GL_JDE_COA_XREF_temp
WHERE (GLOBL_SEGMNT1_KEY,
GLOBL_SEGMNT2_KEY,
GLOBL_SEGMNT3_KEY,
GLOBL_SEGMNT4_KEY,
GLOBL_SEGMNT5_KEY,
GLOBL_SEGMNT6_KEY,
GLOBL_SEGMNT7_KEY,
GLOBL_SEGMNT8_KEY,
GLOBL_SEGMNT9_KEY,
LACNT,START_EFCTV_DT)
in ( sel GLOBL_SEGMNT1_KEY,
GLOBL_SEGMNT2_KEY,
GLOBL_SEGMNT3_KEY,
GLOBL_SEGMNT4_KEY,
GLOBL_SEGMNT5_KEY,
GLOBL_SEGMNT6_KEY,
GLOBL_SEGMNT7_KEY,
GLOBL_SEGMNT8_KEY,
GLOBL_SEGMNT9_KEY,
LACNT,
MIN(START_EFCTV_DT)
from GL_JDE_COA_XREF_temp

GROUP BY GLOBL_SEGMNT1_KEY,
GLOBL_SEGMNT2_KEY,
GLOBL_SEGMNT3_KEY,
GLOBL_SEGMNT4_KEY,
GLOBL_SEGMNT5_KEY,
GLOBL_SEGMNT6_KEY,
GLOBL_SEGMNT7_KEY,
GLOBL_SEGMNT8_KEY,
GLOBL_SEGMNT9_KEY,
LACNT);

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;
/* load latest records */
INSERT INTO GL_JDE_COA_XREF_temp1
SEL A.GLOBL_SEGMNT1_KEY,
A.GLOBL_SEGMNT2_KEY,
A.GLOBL_SEGMNT3_KEY,
A.GLOBL_SEGMNT4_KEY,
A.GLOBL_SEGMNT5_KEY,
A.GLOBL_SEGMNT6_KEY,
A.GLOBL_SEGMNT7_KEY,
A.GLOBL_SEGMNT8_KEY,
A.GLOBL_SEGMNT9_KEY,
A.LACNT,
Min(A.START_EFCTV_DT),
A.END_EFCTV_DT
FROM GL_JDE_COA_XREF_temp A,GL_JDE_COA_XREF_temp1 B
WHERE A.GLOBL_SEGMNT1_KEY=B.GLOBL_SEGMNT1_KEY
AND A.GLOBL_SEGMNT2_KEY=B.GLOBL_SEGMNT2_KEY
AND A.GLOBL_SEGMNT3_KEY =B.GLOBL_SEGMNT3_KEY
AND A.GLOBL_SEGMNT4_KEY=B.GLOBL_SEGMNT4_KEY
AND A.GLOBL_SEGMNT5_KEY=B.GLOBL_SEGMNT5_KEY
AND A.GLOBL_SEGMNT6_KEY=B.GLOBL_SEGMNT6_KEY
AND A.GLOBL_SEGMNT7_KEY=B.GLOBL_SEGMNT7_KEY
AND A.GLOBL_SEGMNT8_KEY=B.GLOBL_SEGMNT8_KEY
AND A.GLOBL_SEGMNT9_KEY=B.GLOBL_SEGMNT9_KEY
AND A.START_EFCTV_DT<> B.START_EFCTV_DT
GROUP BY
A.GLOBL_SEGMNT1_KEY,
A.GLOBL_SEGMNT2_KEY,
A.GLOBL_SEGMNT3_KEY,
A.GLOBL_SEGMNT4_KEY,
A.GLOBL_SEGMNT5_KEY,
A.GLOBL_SEGMNT6_KEY,
A.GLOBL_SEGMNT7_KEY,
A.GLOBL_SEGMNT8_KEY,
A.GLOBL_SEGMNT9_KEY,
A.LACNT,
A.END_EFCTV_DT;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

Above scripts are which we tried but we did not get the required result. Also, I tried using dynamic lookup in informatica but not possible to implement for our requirement.

so thinking to implement using stored procedure.

Please let me know still you have queries.

Thanks in advance:)

Junior Contributor

Re: Stored procedure--Bit tricky one

Hi Suni,
i still don't understand the rules:
Why do you expect two rows

abc 1 2 3 6 8 9 6 45 1234 4-Apr 6-Jun
abc 1 2 3 6 8 9 6 45 1234 6-Jun 7-Jul

instead of

abc 1 2 3 6 8 9 6 45 1234 4-Apr 7-Jul

Hopefully this is only a mistake, because it looked as if you want to merge consecutive rows with the same values in GS2 to GS9 into a single row.

This is easy to achieve usign OLAP-functions:
SELECT
dt.*
-- find the next row's START_EFCTV_DT (or the current END_EFCTV_DT)
,COALESCE(MIN(START_EFCTV_DT)
OVER (PARTITION BY LA, GS1
ORDER BY START_EFCTV_DT
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
, END_EFCTV_DT) AS next_START_EFCTV_DT
FROM
(
SELECT *
FROM your_table
QUALIFY
-- remove all rows where GS2 to GS9 don't change
ROW_NUMBER()
OVER (PARTITION BY LA, GS1 ORDER BY START_EFCTV_DT) = 1
OR
GS2 <> MIN(GS2)
OVER (PARTITION BY LA, GS1
ORDER BY START_EFCTV_DT
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
OR
GS3 <> MIN(GS3)
OVER (PARTITION BY LA, GS1
ORDER BY START_EFCTV_DT
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
OR
GS4 ...
OR
GS9 <> MIN(GS9)
OVER (PARTITION BY LA, GS1
ORDER BY START_EFCTV_DT
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
) AS dt

Dieter
Enthusiast

Re: Stored procedure--Bit tricky one

Hi,

good Morning:)

Sorry for the wrong type...actually out put should be given below
LA GS1 GS2 GS3 GS4 GS5 GS6 GS7 GS8 GS9 START_EFCTV_DT end effective date
abc 1 2 3 6 8 9 6 45 1234 1-Jan 2-Feb
abc 1 2 4 6 8 9 6 45 1898 2-Feb 3-Mar
abc 1 2 5 6 8 9 6 45 14567 3-Mar 4-Apr
abc 1 2 3 6 8 9 6 45 1234 4-Apr 7-Jul
abc 1 2 4 6 8 9 6 45 898 7-Jul 23-Sep
abc 1 2 3 6 8 9 6 45 1234 23-Sep 31-Dec-99
xyz 1 2 3 6 8 9 6 45 1234 24-Sep 31-Dec-99
I am new to BTEQ.

Actually, initially target will be empty.
Case:1 we need to load data row by row and then need to compare 1st row target with 2nd row of source whether LA, GS1 -GS9 same or not. We need to load the data when LA is same, GS1 -GS9 different than 1st row target and both LA, GS1 -GS9 are different than 1st row target.

how to achieve the required out put?and how to load 1st record of source to target and comaprasion?

Can you pls Explain how we can achieve with OLAP step by step?

Thanks a lot for your response:)