median values and collection type in oracle 9i
During that process, I had to get Median values out of it. With oracle9 gettign median value is so easy. However, once I had to deal with oracle8 in the first stage of the development process and oracle8 didn’t have the built in function.
Anyways, here’s oracle 9 approach to get median value in basic form:
SELECT
percentile_cont(0.5) within group (order by a.”Setup Duration” ASC)
from
studysetup_metric;
See? It’s that simple.
One another thing I had to deal with was that string value passing from asp.net to oracle procedure. Actually it’s related to string length limitation in sql statement in oracle.(I’m not sure whether it applies to another sql..)
Usually string is passed from asp.net, it is stored in one of variables in oracle stored procedure. The variable is used in sql statement within the procedure. But the string length of the variable that is used in sql is limited to somewhere 255 as I recall(but I’m not 100% sure.. please look for the information on internet for accuracy). What if string values are longer than what’s supposed to be. In this case, you would want to use collection type instead. I’ll write this about collection type and where-in sometimes later…
to be continued…
