Teradata Procedure Doubt

General

Teradata Procedure Doubt

REPLACE PROCEDURE ABC (IN in_Period_start CHAR(10),IN in_Period_end CHAR(10))

In the input parameter i am giving (Jan2012,Dec2012).

I have to find out different data between the range jan 2012 to dec 2012 in month wise.But in my table structure the data is like below...

Year      Month

-----     --------

2012        1

2012        2

2012        3

......           ...

and so on....

Then how i will make the comparision to get the data between that specified range?

6 REPLIES
WAQ
Enthusiast

Re: Teradata Procedure Doubt

You need to convert your input parameters in DATE format in your procedure and then probably you can use EXTRACT function to get the month and year out of it and compare if with your table.

Enthusiast

Re: Teradata Procedure Doubt

Pass start and end date in the stored procedure parameters and you can construct the date from the integer values in your table. When both the parameters are in same format then the comparison is just a piece of cake...

This piece of code might help you

REPLACE PROCEDURE SP_Comparison (IN in_Period_start_dt DATE,IN in_Period_end_dt DATE)
BEGIN
CREATE TABLE T2 AS
(
SELECT *
FROM TBL
WHERE cast((_year - 1900) * 10000 + (_month * 100) + _day as date) BETWEEN in_Period_start_dt and in_Period_end_dt;
) WITH DATA;
END ;

Re: Teradata Procedure Doubt

But i have to pass the i/p parameter in the below format only...
in_Period_start= 'Jan2012'
and
in_Period_end= 'Dec2012'
WAQ
Enthusiast

Re: Teradata Procedure Doubt

Or use the following query to convert your input parameters to year and month

select extract(year from cast('Jan2012' as date format 'mmmyyyy')) as ur_year
,extract(month from cast('Jan2012' as date format 'mmmyyyy')) as ur_month;

and then compare it with the data present in your table.

Enthusiast

Re: Teradata Procedure Doubt

In the stored procedure cast the input parameters as Date e.g.

CAST(in_Period_Start as DATE format 'MMMYYYY')

This will convert Jan2012 into date format 1/1/2012

Re: Teradata Procedure Doubt

Thanx Guys...