All Forums Database
sam141988 7 posts Joined 08/16
11 Aug 2016
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
 

AtardecerR0j0 71 posts Joined 09/12
11 Aug 2016

select greatest(9,35,2)

Be More!!

sam141988 7 posts Joined 08/16
11 Aug 2016

Thanks a lot! Its a great solution

Inguva 4 posts Joined 08/16
11 Aug 2016

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

yuvaevergreen 93 posts Joined 07/09
11 Aug 2016

It should work..Can you post your data.

Johannes Vink 28 posts Joined 08/14
11 Aug 2016

WHAT does not work.
Given the example data for id 4 the statement returns 100:

 

Johannes Vink 28 posts Joined 08/14
11 Aug 2016

SELECT GREATEST(50,100,70)

sk8s3i 35 posts Joined 06/13
12 Aug 2016

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.

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
12 Aug 2016

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

Dieter

sk8s3i 35 posts Joined 06/13
12 Aug 2016

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?
 

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
12 Aug 2016

Those functions found in syslib are some old pre-TD14.10 UDFs, if you don't qualify the function name they will be found/used instead of td_sysfnlib.greatest. The names are due to overloading (the SQL name is the same, but each function needs a unique name), better use select * from dbc.FunctionsV where FunctionName = 'GREATEST' .
 

Dieter

sk8s3i 35 posts Joined 06/13
15 Aug 2016

Thank you Dieter!
 
I did check using dbc.FunctionsV for the function 'GREATEST', and I got one row in result set:
 
Databasename: SYSLIB
FunctionName: GREATEST
SpecificName: greatestdc
...
..
NumParameters: 2
ParameterDataTypes: D CV
..
..
 
Would this mean that in no case, I can pass more than two params in this function? Can 'NumParamters' be customized/modified by a DBA for any function?
 
Thanks,
Shardul

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
15 Aug 2016

You should find 9 UDFs in syslib and 10 in td_sysfnlib with the name GREATEST (but different specific names).
The functions in syslib are not the built-in functions (those UDFs have been installed manually). They can probably be dropped (of course after double checking if existing SQL uses it) or you simply qualify the name: td_sysfnlib.greatest(....), then you got up to 10 parameters.

Dieter

sk8s3i 35 posts Joined 06/13
15 Aug 2016

Worked as a charm! Thanks Dieter! :-D

-Thanks Shardul

You must sign in to leave a comment.