user-defined functions that return a table can't takes getdate() as parameters

user-defined functions that return a table can't takes getdate() as parameters

Post by kit » Sat, 01 Dec 2001 15:14:56



Hi,

I have just come across a problem that I can't feed getdate
() to functions that return a table while feeding constant
values like '1/1/01' is allowed. Anybody has comments?
Functions returning a scalar value seem to be fine.
-------------------------

returns table
as
return(
/* select statement here*/
)

---------------------
calling the function
select * from dbo.dum(getdate())

error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

whereas feeding constant is fine
select * from dbo.dum(getdate())

 
 
 

user-defined functions that return a table can't takes getdate() as parameters

Post by oj » Sat, 01 Dec 2001 23:58:31


kit,

getdate() is nondeterministic which is not allowed in
user-defined-functions.

please see sql book online for more info on how to create function.

-oj


> Hi,

> I have just come across a problem that I can't feed getdate
> () to functions that return a table while feeding constant
> values like '1/1/01' is allowed. Anybody has comments?
> Functions returning a scalar value seem to be fine.
> -------------------------

> returns table
> as
> return(
> /* select statement here*/
> )

> ---------------------
> calling the function
> select * from dbo.dum(getdate())

> error message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.

> whereas feeding constant is fine
> select * from dbo.dum(getdate())


 
 
 

user-defined functions that return a table can't takes getdate() as parameters

Post by BP Margoli » Sun, 02 Dec 2001 08:53:36


kit,


returns table
as

go




-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hi,

> I have just come across a problem that I can't feed getdate
> () to functions that return a table while feeding constant
> values like '1/1/01' is allowed. Anybody has comments?
> Functions returning a scalar value seem to be fine.
> -------------------------

> returns table
> as
> return(
> /* select statement here*/
> )

> ---------------------
> calling the function
> select * from dbo.dum(getdate())

> error message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.

> whereas feeding constant is fine
> select * from dbo.dum(getdate())

 
 
 

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

I think this is a bug of Excel 2002 using cubes in OLAP
server
Can you help me doing the following steps?

1. Create an easy ActiveX DLL with Visual Basic 6.0 with
the only following lines:
Public Function Simple5() As Double
    Simple5 = 5
End Function
2. Make the project in a DLL file
3. Open the Analysis Manager, FoodMart 2000 database and
edit the Budget Cub
4. Insert a New Calculated Member and press the Register
button, click add and select the DLL compiled in step 2,
and then click close to the Register Function Libraries
window
5 Type Simple5()In the Value expression and click ok to
the Calculated Member Builder
6. Click the Data tab and you can see the 5 in the New
Calculated Member. Until now all is right and you can save
the cube and test the cube with the MDX sample application
or the Microsoft Data Analyzer, or a VB consult with DAO
and all will be good.
7. Open the cube with Excel XP and I can see all the data
in the budget cub except the New Calculated Member who
return an error in the cell.

Can some body help me?

2. Event ID 1001 error with SQLCTR70

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

4. Moving .DAT and .LOG files from server to server

5. Problem in User Defined Function returning Table Datatype

6. Test for Table Exist?

7. user-defined function return table with dynamic sql?

8. informix error 244 and 113

9. Using a User defined function returning a table in a subquery

10. Using GETDATE() within a user defined function

11. Using GETDATE() in User-Defined Functions

12. GetDate in user defined functions error

13. using getdate() in user defined functions in sqlserver2000