dear all,

I have a table called "distributor" having "distributor_id" as a PK
and taking an integer data type as well as "distributor_parent_id"
which also takes an integer data type but can accept null values;

I tried to make this simple function that returns the parent_id by
taking a parameter representing the distributor id; still it fails, I
assume due to the delimiters.

I appreciate any assistance to this regard.

DELIMITER $$

CREATE FUNCTION last_inserted_parent_id ( inserted_id INT )
RETURNS INT
BEGIN
DECLARE parent_id INT$$
SET parent_id = (
SELECT distributor_parent_id
FROM distributor
WHERE distributor_id = inserted_id
)
RETURN parent_id$$
END$$
$$

DELIMITER ;

On Nov 7, 1:35 pm, coosa <coos...@gmail.com> wrote:
> dear all,
>
> I have a table called "distributor" having "distributor_id" as a PK
> and taking an integer data type as well as "distributor_parent_id"
> which also takes an integer data type but can accept null values;
>
> I tried to make this simple function that returns the parent_id by
> taking a parameter representing the distributor id; still it fails, I
> assume due to the delimiters.
>
> I appreciate any assistance to this regard.
>
> DELIMITER $$
>
> CREATE FUNCTION last_inserted_parent_id ( inserted_id INT )
> RETURNS INT
> BEGIN
> DECLARE parent_id INT$$
> SET parent_id = (
> SELECT distributor_parent_id
> FROM distributor
> WHERE distributor_id = inserted_id
> )
> RETURN parent_id$$
> END$$
> $$
>
> DELIMITER ;

any answers?

On Tue, 06 Nov 2007 21:35:32 -0800, coosa
<coosa76@gmail.com> wrote:

>dear all,
>
>I have a table called "distributor" having "distributor_id" as a PK
>and taking an integer data type as well as "distributor_parent_id"
>which also takes an integer data type but can accept null values;
>
>I tried to make this simple function that returns the parent_id by
>taking a parameter representing the distributor id; still it fails, I
>assume due to the delimiters.

Your assumption is correct.
Read it up in the manual, there is a nice example in the
first hit when you search the MySQL 5.1 Reference manual
for DELIMITER.

>I appreciate any assistance to this regard.

I would appreciate if you would assist yourself.
You can express your appreciation for the creators of
MySQL and its manual by using that same manual.

>DELIMITER $$
>
>CREATE FUNCTION last_inserted_parent_id ( inserted_id INT )
> RETURNS INT
> BEGIN
> DECLARE parent_id INT$$
> SET parent_id = (
> SELECT distributor_parent_id
> FROM distributor
> WHERE distributor_id = inserted_id
> )
> RETURN parent_id$$
> END$$
>$$
>
>DELIMITER ;
--
( Kees
)
c[_] Remember that you are unique. Just like everyone else. (#13)