Re: Max of character string Function problems

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 26 Feb 2001 18:02:57 +0100
Message-ID: <97e282$ojt$1@s1.read.news.oleane.net>

In your select statement the names of the column have a highest priority than the name of the parameters. As your parameters have the same name as your columns, Oracle assume that you reference the column not the parameter. So you always get the max in your table whatever the parameters you give.

You have to change the name of your parameters. For example, prefix them with p_.

--
Have a nice day
Michel


<april.mcclellan_at_HydroOne.com> a écrit dans le message news:
2F96BE4C7CC6D211A55F0008C7A439760174249D_at_OHSC-EX4...


> Can someone tell me what it wrong with the function below?  It always

> returns the value '083'.

> I am simply trying to retrieve the highest NMProjNo in the table tblProject.

> NMProjNo is a string value.  If I type the same SQl statement into SQL*Plus

> I will get the correct return value.

>

>

> CREATE OR REPLACE FUNCTION SPGETPROJECTNO (

> CONTPREFIX   IN VARCHAR2,

> CONTYR      IN VARCHAR2,

> CONTSLANUM   IN VARCHAR2,

> CONTSLAVERSION   IN VARCHAR2,

> CONTAWDNUM   IN VARCHAR2)

>

> RETURN VARCHAR2 IS MaxofNMProjNo VARCHAR2(3);

>

> BEGIN

>

>    SELECT Max(NMProjNo)INTO MaxofNMProjNo FROM tblProject WHERE

>    tblProject.ContPrefix = ContPrefix AND tblProject.ContYr = ContYr And

> tblProject.ContSLANum = ContSlaNum

>    AND tblProject.ContSLAVersion = ContSLAVersion AND tblProject.ContAwdNum

> = ContAwdNum;

>

>    RETURN MaxofNMProjNo;

>

>    EXCEPTION

>      WHEN NO_DATA_FOUND THEN

>        MaxofNMProjNo := '000';

> RETURN MaxofNMProjNo;

>      WHEN OTHERS THEN

>        ROLLBACK;

> RAISE;

>

> END SPGETPROJECTNO;

>

> Thanks very much,

>

> April McClellan

>

>

>

> --

> Posted from thor.hydroone.com [192.75.116.12]

> via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Mon Feb 26 2001 - 11:02:57 CST