Capturing MAX marks for Each Student when the MArks are in Columns

Database
Enthusiast

Capturing MAX marks for Each Student when the MArks are in Columns

Consider the Below Scenario  -

ID|MATHS|PHYSICS|CHEMISTRY

1|50|60|70

2|70|60|40

3|50|80|70

4|50|100|70

5|90|60|70

I want to get the Result where it will display the maximum marks which the Student has got!

Result Would be-

ID|Mark

1|70

2|70

3|80

4|100

5|90

13 REPLIES
Teradata Employee

Re: Capturing MAX marks for Each Student when the MArks are in Columns

select greatest(9,35,2)

Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

Thanks a lot! Its a great solution

Fan

Re: Capturing MAX marks for Each Student when the MArks are in Columns

Hi,this solution worked but its giving wrong value for one id 4 alone

Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

It should work..Can you post your data.

Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

WHAT does not work.

Given the example data for id 4 the statement returns 100:


					
				
			
			
				
			
			
				
Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

SELECT GREATEST(50,100,70)

Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

I am using TD14.10 release and SELECT GREATEST (50,100,70) throws an error in TERA Mode. The error I got was: "SELECT Failed. 9881: Function 'greatest' called with an invalid number or type of parameters". However, when I tried SELECT GREATEST(GREATEST(50,100),70), I did get the desired result. I was checking its syntax at info.teradata.com and found that it accepts two arguments.

Am I wrong here? Does it really accept more than two arguments.

Junior Contributor

Re: Capturing MAX marks for Each Student when the MArks are in Columns

td_sysfnlib.GREATEST accepts up to 10 parameters.

Check if there's another UDF with the same name in the search path: 

default database -> syslib -> td_sysfnlib

Enthusiast

Re: Capturing MAX marks for Each Student when the MArks are in Columns

I checked for any other UDF with the same name and I found just one function with this name.

I issued the folloing query to check if there are any other functions with the same name and I got one row in result:

DataBaseName TableName TableKind

TD_SYSFNLIB Greatest F

SELECT DATABASENAME, TABLENAME, TABLEKIND FROM DBC.TABLESV
WHERE TABLENAME = 'GREATEST'
;

However, when I checked manually using Teradata Administrator, I found that inside SYSLIB database, there were 9 functions as shown below:

greatestcc Function N F 1 DBC

greatestcd Function N F 1 DBC

greatestci Function N F 1 DBC

greatestdc Function N F 1 DBC

greatestdd Function N F 1 DBC

greatestdi Function N F 1 DBC

greatestic Function N F 1 DBC

greatestid Function N F 1 DBC

greatestii Function 0 2015-02-13 13:01:33 N F 1 DBC

And definitions of all the above mentioned functions starts like this with only change being present in the paramter part and the "SPECIFIC" part:

Definition of "greatestcc" function:

REPLACE FUNCTION SYSLIB.GREATEST 
(str1 VARCHAR(512) CHARACTER SET LATIN,
str2 VARCHAR(512) CHARACTER SET LATIN)
RETURNS VARCHAR(512) CHARACTER SET LATIN
SPECIFIC greatestcc
LANGUAGE C
NO SQL
NO EXTERNAL DATA
PARAMETER STYLE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!greatestcc!M:\DBAScripts\UDFs\oraudfs_v_4\greatest\greatestcc.c'

All functions have only two params, with multiple variations of (int, decimal), or, (decimal, int), or, (int, str), or, (str, int) and so on...

Could this be the reason that in my case, this function is accepting only two params?