Teradata Functions

Database
Fan

Teradata Functions

Hi,

I am new to Teradata. Can some one help me to convert Sqlserver functions in to Teradata Function. I really appreciate for your help.

ALTER FUNCTION [dbo].[udf_getMerchantPackagePrice] 

(

@ServiceID int

, @PrivateID int

, @PromoCode varchar(20)

, @MerchantId int

, @PriceLevel tinyint

)

RETURNS money

AS

BEGIN

DECLARE @ReturnPrice money, @RegularPrice money, @PriceAdjustmentTypeId int, @AdjustmentAmount money, @MerchantBillingPriceAdjustmentsId int, @PromoId int;

-- Get the regular package price

SELECT @RegularPrice = Price FROM [MC].[dbo].[vw_AppServiceMasterOptions] WHERE serviceID = @ServiceID AND PrivateID = @PrivateID AND PriceLevel = @PriceLevel;

-- Get the promoId based on promo code, serviceId and privateId

SELECT @PromoId = dbo.udf_getPromoIdByPromoCode(@PromoCode, @ServiceId, @PrivateId)

-- Get the MerchantBillingPriceAdjustments Id based on the merchantId and serviceId (ensure that billingcylesused is less than billingcycles)

SELECT @MerchantBillingPriceAdjustmentsId = id FROM MerchantBillingPriceAdjustments WHERE MerchantId = @MerchantId AND ServiceId = @ServiceId AND (BillingCyclesUsed < BillingCycles OR BillingCycles IS NULL) AND IsValid = 1

-- No promo code was passed so check to see if the merchant has a valid MerchantBillingPriceAdjustment record

If (@PromoCode = '' AND @MerchantBillingPriceAdjustmentsId Is Null)

SELECT @ReturnPrice = @RegularPrice

ELSE

BEGIN

IF NOT (@PromoId Is Null)

SELECT @PriceAdjustmentTypeId = PriceAdjustmentTypeId, @AdjustmentAmount = Amount FROM Promotions WITH(NOLOCK) WHERE Id = @PromoId

ELSE

BEGIN

If NOT (@MerchantBillingPriceAdjustmentsId Is Null)

SELECT @PriceAdjustmentTypeId = PriceAdjustmentTypeId, @AdjustmentAmount = Amount FROM MerchantBillingPriceAdjustments WITH(NOLOCK) WHERE Id = @MerchantBillingPriceAdjustmentsId

ELSE

SELECT @ReturnPrice = @RegularPrice

END

-- NOTE: at this time we are only supporting absolute promo pricing.

--IF (@PriceAdjustmentTypeId = 1) -- %

-- SELECT @PromoPrice = ROUND(@RegularPrice * (@AdjustmentAmount / 100), 2)

--ELSE

--BEGIN 

IF (@PriceAdjustmentTypeId = 2) -- absolute

SELECT @ReturnPrice = @AdjustmentAmount

--ELSE

--BEGIN

--IF (@PriceAdjustmentTypeId = 3) -- offset

--SELECT @PromoPrice = (@RegularPrice - @AdjustmentAmount)

--END

--END

END

RETURN @ReturnPrice

END;

Thanks,

Sasha

2 REPLIES
Enthusiast

Re: Teradata Functions

Hi Sasha,

Whaw!!!! All I can think of now is providing the links for you to see and code. You can download the docs.

http://www.info.teradata.com/edownload.cfm?itemid=140900023 

http://www.info.teradata.com/edownload.cfm?itemid=140900022

You know the functionalities better than anyone else. I always suggest people that if we migrate from one DB to another DB, it is better not to think much of code by code unless you are an expert in both. The features in Oracle, SQL server, Teradata etc are a bit different and their syntaxes are different but structures are similar. Say for oracle, it is declare...begin(nesting begin end)....exception ...end;

Fan

Re: Teradata Functions

Thanks Raja! I coundn't find any syntax examples for functions. 

Can someone help me with this?

Replace  FUNCTION onedb.udf_getPromoIdByPromoCode

(

p_PromoCode varchar(20),

p_ServiceId int,

p_PrivateId int

)

RETURNS int

LANGUAGE SQL

CONTAINS SQL

 BEGIN

COLLATION INVOKER

INLINE TYPE 1

--AS

--BEGIN

--DECLARE v_PromoId int;

SELECT 

p.Id INTO v_PromoId 

FROM 

onedb.Promotions p

INNER JOIN onedb.AppServiceMasterOptionsPromotions asmop  ON p.Id = asmop.PromotionId 

INNER JOIN onedb.AppServiceMasterOptions options ON options.id = asmop.AppServiceMasterOptionId 

WHERE 

options.serviceID = p_ServiceId  

AND options.PrivateID = p_PrivateId 

AND Code = p_PromoCode 

AND IsActive = 1

RETURN v_PromoId

END;

Thanks,

Sasha