All Forums General
jyoti106 3 posts Joined 09/12
11 Sep 2012
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?

WAQ 158 posts Joined 02/10
13 Sep 2012

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.

Qaisar Kiani 337 posts Joined 11/05
13 Sep 2012

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	;
jyoti106 3 posts Joined 09/12
13 Sep 2012

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

WAQ 158 posts Joined 02/10
14 Sep 2012

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.

Qaisar Kiani 337 posts Joined 11/05
14 Sep 2012

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

jyoti106 3 posts Joined 09/12
14 Sep 2012

Thanx Guys...

You must sign in to leave a comment.