April 7, 2018 at 6:00 am
Hi guys
I am struggling to convert this into SQL
SELECT sum( case when table1.sale_Id Is Null And
table2.status In ('D1','E1','P1') And
table3.Code In ('A','B','C') Then
(Case When (Greatest(table2.date1),table4.CALENDAR_DATE) <=
Least(table3.date1),(table4.CALENDAR_DATE+(Nvl(table1.YEAR_PERCENTAGE,100)/100*365)-1)) ) Then
Round((table1.AMT/Round((nvl(table1.YEAR_PERCENTAGE,100)/100*365),2))*
((Least(table3.date1),(table4.CALENDAR_DATE+(nvl(table1.YEAR_PERCENTAGE,100)/100*365)-1))+1)-
(Greatest(table2.date1),table4.CALENDAR_DATE) )),2) End)End)
FROM
table1,
table2,
table3,
table4
WHERE
( table1.b_key=table2.c_key )
AND ( table1.d_key=table3.f_key )
AND ( table1.g_key=table4.h_key )
April 7, 2018 at 9:12 am
I think there isn't a GREATEST and LEAST function equivalent in sql server. So you would need to make use of CASE statements to mimick the functions. Eg
-- In oracle
select greatest(col1,col2) from dual
-- in sql server
select case when col1 > col2 then
col1
else col2 end
Also it might be worth using ANSI join instead of Oracle syntax
FROM table1,
table2,
table3,
table4
WHERE ( table1.b_key=table2.c_key )
AND ( table1.d_key=table3.f_key )
AND ( table1.g_key=table4.h_key )
Can be converted to
FROM table1
JOIN table2
ON ( table1.b_key=table2.c_key )
JOIN table3
ON ( table1.d_key=table3.f_key )
JOIN table4
ON ( table1.g_key=table4.h_key )
April 9, 2018 at 6:10 am
nutty - Saturday, April 7, 2018 6:00 AMHi guys
I am struggling to convert this into SQLSELECT sum( case when table1.sale_Id Is Null And
table2.status In ('D1','E1','P1') And
table3.Code In ('A','B','C') Then
(Case When (Greatest(table2.date1),table4.CALENDAR_DATE) <=
Least(table3.date1),(table4.CALENDAR_DATE+(Nvl(table1.YEAR_PERCENTAGE,100)/100*365)-1)) ) Then
Round((table1.AMT/Round((nvl(table1.YEAR_PERCENTAGE,100)/100*365),2))*
((Least(table3.date1),(table4.CALENDAR_DATE+(nvl(table1.YEAR_PERCENTAGE,100)/100*365)-1))+1)-
(Greatest(table2.date1),table4.CALENDAR_DATE) )),2) End)End)FROM
table1,
table2,
table3,
table4
WHERE
( table1.b_key=table2.c_key )
AND ( table1.d_key=table3.f_key )
AND ( table1.g_key=table4.h_key )
You also appear to have an Oracle syntax problem, as your Greatest function right out of the gate, closes the parenthesis before a 2nd value appears. In theory, if that is even allowed in Oracle, it would render the function of no value whatsoever. After realizing that was also similarly problematic with a number of other Greatest and Least functions, I gave up trying to interpret the meaning. Was that a copied and pasted query? If so, can you remove those Greatest and Least functions that have just one argument and replace them with just that one argument and get the same result in Oracle? If so, they were never needed in the first place. Otherwise, you're going to have to demonstrate exactly what logic your query needs to handle...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 9, 2018 at 6:58 am
There are many problems with that query, but I tried to get it correct. This might give you an idea on what could work.
I'm guessing that this is a snippet and the actual query is different, but as it is, the CASE expressions should be converted to WHERE conditions.
SELECT
SUM( CASE when t1.sale_Id Is Null
AND t2.status In ('D1','E1','P1')
AND t3.Code In ('A','B','C')
THEN CASE WHEN Greatest.dateg <= Least.datel )
THEN Round((t1.AMT/Round(ISNULL(t1.YEAR_PERCENTAGE,100)/100*365,2))
* DATEDIFF( DD, Greatest.dateg, Least.datel) + 1,2)
END
END)
FROM table1
JOIN table2 ON t1.b_key=t2.c_key
JOIN table3 ON t1.d_key=t3.f_key
JOIN table4 ON t1.g_key=t4.h_key
CROSS APPLY (SELECT MAX(datex) AS Dateg
FROM VALUES((t2.date1),(t4.CALENDAR_DATE))x(datex)) Greatest
CROSS APPLY (SELECT MIN(datex) AS Datel
FROM VALUES((t3.date1),(t4.CALENDAR_DATE+(ISNULL(t1.YEAR_PERCENTAGE,100)/100*365)-1))x(datex)) Least;
April 9, 2018 at 11:19 pm
While the above solutions work, they give you a different answer than Oracle if any of the values is NULL. Oracle will always return NULL for the Greatest and Least functions if any value is NULL.
MIN and MAX functions will only return NULL if all values are NULL
A small modifiction to Luis C. query would be to change:
SELECT MAX(datex) AS Dateg
FROM VALUES((t2.date1),(t4.CALENDAR_DATE))x(datex)
to:SELECT CASE WHEN COUNT(datex) = COUNT(*) THEN MAX(datex) ELSE NULL END AS Dateg
FROM VALUES((t2.date1),(t4.CALENDAR_DATE))x(datex)
COUNT(*) includes all rows, while the COUNT(datex) only includes non NULL values, so if there is a difference then at least one of the values is NULL and therefore the result should be NULL.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply