Teradata To Oracle MOD Function [message #671207] |
Thu, 16 August 2018 14:03 |
|
kawi6rr
Messages: 4 Registered: March 2013 Location: Honolulu
|
Junior Member |
|
|
Hello All
We're moving over from Teradata to Oracle and I'm having a hard time getting this code to not throw an error. The code in bold is my attempt to convert to oracle and the code below is the original Teradata code. Any help is appreciated, thanks in advance.
Error
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
Code:
SELECT
proc_type
,surg_year
,surg_month
,CASE median_los_hrs
WHEN COUNT(*) OVER (PARTITION BY proc_type, surg_year, surg_month) MOD(1,0) THEN los_hours
WHEN COUNT(*) OVER (PARTITION BY proc_type, surg_year, surg_month) MOD 2 = 1 THEN los_hours
ELSE AVG(los_hours) OVER (PARTITION BY proc_type, surg_year, surg_month ORDER BY los_hours ROWS 1 PRECEDING)
END
FROM surg_base
|
|
|
Re: Teradata To Oracle MOD Function [message #671208 is a reply to message #671207] |
Thu, 16 August 2018 15:09 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You have to tell us what is the meaning of your Teradata expression.
And better as told you in your previous topics:
Michel Cadot wrote on Thu, 14 March 2013 16:45With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Regards
Michel
And if you want help better feedback and thank people who spend time to help you.
Remind:
Michel Cadot wrote on Wed, 13 March 2013 21:43And before your next question, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
Regards
Michel
|
|
|
Re: Teradata To Oracle MOD Function [message #671215 is a reply to message #671208] |
Fri, 17 August 2018 03:30 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well mod(1,0) is a call to mod that will always return 1.
I imagine you want one of those values to be variable, probably the result of count:
,CASE WHEN mod(COUNT(*) OVER (PARTITION BY proc_type, surg_year, surg_month), <whatever the 2nd parameter should be>) = <whatever you're checking for>
OR mod(COUNT(*) OVER (PARTITION BY proc_type, surg_year, surg_month), <whatever the 2nd parameter should be>) = <whatever you're checking for> THEN los_hours
ELSE AVG(los_hours) OVER (PARTITION BY proc_type, surg_year, surg_month ORDER BY los_hours ROWS 1 PRECEDING)
END AS median_los_hrs
|
|
|