more question about adding values from user defined function 
Author Message
 more question about adding values from user defined function

Hi,

Thanks Steve for helping me to solve my first problem. I am having another
problem that similar to
the first, but...

there are many rows in table Name( AuthorString, ReferenceFk, Created_When,
Created_Who).
I want to add all the rows from table Name to table AuthorTeam( AuthorTeam,
XXFk, Created_When, Created_Who),
 but the values of XXFk are from function dbo.NextFreeCitationId(ReferenceFk
* 1000000).
the Name table is something like this:

      NameId
     AuthorString
     ReferenceFk
     Created_When
     Created_Who

      1
     C.Hartm.
     1
     22/04/2002
     BlaBla

      2
     fffffffff
     1
     01/02/2001
     hhhh

      3
     jjkkkkk
     1
     16/12/2001
     jhgjghj

      4
     hjhjjj
     2
     04/12/2001
     gfhgfh

      5
     hjhjhj
     2
     05/05/2002
     bggfhgh

 the code of function is:



BEGIN




 END

END
the source code is :
INSERT INTO AuthorTeam( AuthorTeam, XXFk, Created_When, Created_Who)
SELECT dbo.NextFreeCitationId(ReferenceFk*1000000), GetDate(), CreatedWho
FROM Name

I want to get the result like this:

      AuthorId
     AuthorTeam
     XXFk
     Created_When
     Created_Who

      1
     C.Hartm.
     1000000
     22/04/2002
     BlaBla

      2
     fffffffff
     1000001
     01/02/2001
     hhhh

      3
     jjkkkkk
     1000002
     16/12/2001
     jhgjghj

      4
     hjhjjj
     2000000
     04/12/2001
     gfhgfh

      5
     hjhjhj
     2000001
     05/05/2002
     bggfhgh

 but I can not. since if the ReferenceFk is '1', thej result of XXFk will
always be '1000000', and '2' will be '2000000'.

Can anyone provide some idea on this problem?

Thanks

J.Li



Mon, 11 Oct 2004 00:39:41 GMT
 more question about adding values from user defined function

INSERT INTO AuthorTeam(AuthorTeam, XXFk, Created_When, Created_Who)
SELECT AuthorString, --you're missing this in your query?!
    dbo.NextFreeCitationId(ReferenceFk*1000000),
    GetDate(),
    CreatedWho
FROM Name

--
-oj

http://www.rac4sql.com


Quote:
> Hi,

> Thanks Steve for helping me to solve my first problem. I am having another
> problem that similar to
> the first, but...

> there are many rows in table Name( AuthorString, ReferenceFk,
Created_When,
> Created_Who).
> I want to add all the rows from table Name to table AuthorTeam(
AuthorTeam,
> XXFk, Created_When, Created_Who),
>  but the values of XXFk are from function

dbo.NextFreeCitationId(ReferenceFk
Quote:
> * 1000000).
> the Name table is something like this:

>       NameId
>      AuthorString
>      ReferenceFk
>      Created_When
>      Created_Who

>       1
>      C.Hartm.
>      1
>      22/04/2002
>      BlaBla

>       2
>      fffffffff
>      1
>      01/02/2001
>      hhhh

>       3
>      jjkkkkk
>      1
>      16/12/2001
>      jhgjghj

>       4
>      hjhjjj
>      2
>      04/12/2001
>      gfhgfh

>       5
>      hjhjhj
>      2
>      05/05/2002
>      bggfhgh

>  the code of function is:



> BEGIN




>  END

> END
> the source code is :
> INSERT INTO AuthorTeam( AuthorTeam, XXFk, Created_When, Created_Who)
> SELECT dbo.NextFreeCitationId(ReferenceFk*1000000), GetDate(), CreatedWho
> FROM Name

> I want to get the result like this:

>       AuthorId
>      AuthorTeam
>      XXFk
>      Created_When
>      Created_Who

>       1
>      C.Hartm.
>      1000000
>      22/04/2002
>      BlaBla

>       2
>      fffffffff
>      1000001
>      01/02/2001
>      hhhh

>       3
>      jjkkkkk
>      1000002
>      16/12/2001
>      jhgjghj

>       4
>      hjhjjj
>      2000000
>      04/12/2001
>      gfhgfh

>       5
>      hjhjhj
>      2000001
>      05/05/2002
>      bggfhgh

>  but I can not. since if the ReferenceFk is '1', thej result of XXFk will
> always be '1000000', and '2' will be '2000000'.

> Can anyone provide some idea on this problem?

> Thanks

> J.Li



Mon, 11 Oct 2004 01:49:40 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. add values into a table from user defined function

2. call a user defined function from another user-defined function

3. How to add User Defined Function?

4. How do you add a VB -user defined function

5. A tough one - passing in a list of values to a user defined function

6. parameters to table valued user defined functions

7. User defined functions doesn't return the correct value in Excel XP

8. User Defined Functions -- default values?

9. User-defined function question

10. Question about user-defined functions

11. User defined function question

12. Newbie questions on User-defined functions in SQL server


 
Powered by phpBB® Forum Software