CONVERT PLSQL TO TSQL

Question

Post reply

CONVERT PLSQL TO TSQL

STYLES

SSC Journeyman

Points: 83
More actions
July 1, 2014 at 10:11 am

#287414

HOW TO CONVERT THE BELOW FROM PLSQL TO TSQL
YTD_Deprn2=DECODE(SUBSTR(Period_Name,-2),SUBSTR(:Date,-2),DECODE(NVL(Prior Fy Expense,0),0,NVL(Ytd_ Deprn,0),Ytd Less Pfye),0)
YTD Deprn=DECODE(TRUNC(PYE Deprn Reserve+YTD Deprn2,2),0,0,YTD Deprn2)--

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Use

Oracle DECODE = T-SQL:

CASE WHEN [condition 1] THEN [expression 1]

WHEN [condition 2] THEN [expression 2]

....

ELSE [expression]

END

SUBSTR = T-SQL: SUBSTRING

NVL = T-SQL: ISNULL

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

STYLES (7/1/2014)
HOW TO CONVERT THE BELOW FROM PLSQL TO TSQL
YTD_Deprn2=DECODE(SUBSTR(Period_Name,-2),SUBSTR(:Date,-2),DECODE(NVL(Prior Fy Expense,0),0,NVL(Ytd_ Deprn,0),Ytd Less Pfye),0)
YTD Deprn=DECODE(TRUNC(PYE Deprn Reserve+YTD Deprn2,2),0,0,YTD Deprn2)--

DECODE can be replaced by a case statement as it is a "if then else" function.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

First, are you using SQL Server 2012 or SQL Server 2005? You have double posted in two different forums.

Second, please don't double post, it fragments answers you may get.

Third, you want to convert the DECODE to a CASE clause.

STYLES SSC Journeyman Points: 83 More actions · Answer 4

OK

this is where i am at and stuck

declare @period_name as date = 'DEC-10'

case when substring(period_name,5,2) = '@periodname' THEN SUBSTRING('@periodname' ,5,2)

the period names are in format DEC-01, JAN-01, FEB-01 etc

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

STYLES (7/1/2014)
OK
this is where i am at and stuck
declare @period_name as date = 'DEC-10'
case when substring(period_name,5,2) = '@periodname' THEN SUBSTRING('@periodname' ,5,2)
the period names are in format DEC-01, JAN-01, FEB-01 etc

Have you read BoL about SUBSTRING function?

substring(period_name,5,2) will return string of 2 characters length starting from 5th character of period_name...

Placing @periodname variable in quotes makes it literal string constant.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Remove the quotes from '@periodname'

declare @period_name as date = 'DEC-10'

SELECT CASE WHEN substring(period_name,5,2) = @periodname THEN SUBSTRING(@periodname ,5,2)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 7

I think you'll need to change the way you deal with this slightly.

I suspect that your period_name column is a char or varchar, so declaring @periodname as a date is a wee bit pointless and won't work in a SUBSTRING without a explicit conversion.

Taking your first statement to convert you would get something like

-- YTD_Deprn2=DECODE(SUBSTR(Period_Name,-2),SUBSTR(:Date,-2),DECODE(NVL(Prior Fy Expense,0),0,NVL(Ytd_ Deprn,0),Ytd Less Pfye),0)

-- assuming column for comparison is char or varchar

-- if it is a date datatype use YEAR(...)

DECLARE @periodName varchar(7) = 'DEC-10'

SELECT

CASE

-- If date use WHEN YEAR(Period_Name) = YEAR(@periodName) THEN

WHEN SUBSTRING(Period_Name,5,2) = SUBSTRING(@periodName,5,2) THEN

CASE

WHEN ISNULL([Prior Fy Expense],0) = 0 THEN ISNULL([Ytd_ Deprn],0)

ELSE [Ytd Less Pfye]

END

ELSE 0

END