Home » SQL & PL/SQL » SQL & PL/SQL » Teradata To Oracle MOD Function
Teradata To Oracle MOD Function [message #671207] Thu, 16 August 2018 14:03 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66688
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:45
With 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:43
And 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 Go to previous message
cookiemonster
Messages: 13721
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
Previous Topic: Put data from one field to another.
Next Topic: FIND MONTHS
Goto Forum:
  


Current Time: Mon Nov 18 16:24:44 CST 2019