February 20, 2015 at 6:58 am
Sangeeth878787 (2/20/2015)
Hi Jason,Your script nearly satisfies the two cases, but I need it as function or store procedure to satisfy all the 4 cases. I am glad for your script.
Ok I think to help further could you post sample data for the Currency table and also the primary table along with the DDL for the creates, and expected results.
One line with each case you mention should be enough along with the rates for them.
This way we can help guide you better.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 20, 2015 at 7:53 am
Hi Sir,
Your code helped to create full stored procedure.
This is the script I have wrote.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
)
AS
BEGIN
IF (@I_TARGET_CURR = @I_DOCUMENT_CURR)
BEGIN
SELECT @I_NET_AMOUNT AS O_TARGET_CURR
END
ELSE
IF (@I_DOCUMENT_CURR = 'USD' AND @I_TARGET_CURR != 'USD')
BEGIN
SELECT ((@I_NET_AMOUNT) / (RATE)) AS O_TARGET_CURR
FROM CURRENCY
WHERE CURRENCY = @I_TARGET_CURR AND RTYPE = 'M'
END
ELSE
IF(@I_DOCUMENT_CURR <> 'USD' AND @I_TARGET_CURR = 'USD')
BEGIN
SELECT (@I_NET_AMOUNT * RATE) AS O_TARGET_CURR
FROM CURRENCY
WHERE CURRENCY = @I_DOCUMENT_CURR AND RTYPE = 'M'
END
ELSE
BEGIN
SELECT ((SELECT (@I_NET_AMOUNT * RATE) AS O_TARGET_CURR
FROM CURRENCY WHERE CURRENCY = @I_DOCUMENT_CURR AND RTYPE = 'M')/(RATE)) FROM CURRENCY
WHERE CURRENCY = @I_TARGET_CURR AND RTYPE = 'M'
END
END
EXEC SP_STAT_CURR 25,'GBP','GBP'
Once Again I am Thank You Sir!!
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply