Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a function on SELECT error Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 10:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 4:32 PM
Points: 257, Visits: 535
Hello comunity

I have create this function to return total pairs:

CREATE FUNCTION [dbo].[fnQtRefCor]
(@bostamp varchar(25), @ref varchar(20), @cor varchar(50))
RETURNS int
AS
BEGIN
DECLARE @retval int

SELECT @retval = SUM(qtt) FROM bi
WHERE @bostamp = bostamp AND @ref = ref AND @cor = cor
and bi.ref <> ''

SET @retval = ISNULL(@retval, 0)
RETURN @retval
END

If i run this query:
SELECT SUM(qtt) FROM bi
WHERE bostamp = 'jm13010767476,323000001' AND ref = '2120910'
and (bi.ref <> '')
group by bi.bostamp,ref,cor

I obtain the correct value of : 24

if i run this select to test the result :
select [dbo].[fnQtRefCor] from bi where bi.bostamp = 'jm13010767476,323000001 '

This error appear :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.fnQtRefCor" could not be bound.

I can run this SELECT without error ?

Many thanks,
Luis Santos





Post #1406482
Posted Sunday, January 13, 2013 10:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 4:32 PM
Points: 257, Visits: 535
Sorry, i forget to mencioned that the compatibility level is :
SQL SERVER 2000 (80)

Maybe the problem is here ??

Thanks



Post #1406483
Posted Sunday, January 13, 2013 10:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
luissantos (1/13/2013)
Hello comunity

I have create this function to return total pairs:

CREATE FUNCTION [dbo].[fnQtRefCor]
(@bostamp varchar(25), @ref varchar(20), @cor varchar(50))
RETURNS int
AS
BEGIN
DECLARE @retval int

SELECT @retval = SUM(qtt) FROM bi
WHERE @bostamp = bostamp AND @ref = ref AND @cor = cor
and bi.ref <> ''

SET @retval = ISNULL(@retval, 0)
RETURN @retval
END

If i run this query:
SELECT SUM(qtt) FROM bi
WHERE bostamp = 'jm13010767476,323000001' AND ref = '2120910'
and (bi.ref <> '')
group by bi.bostamp,ref,cor

I obtain the correct value of : 24

if i run this select to test the result :
select [dbo].[fnQtRefCor] from bi where bi.bostamp = 'jm13010767476,323000001 '

This error appear :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.fnQtRefCor" could not be bound.

I can run this SELECT without error ?

Many thanks,
Luis Santos




One thing I see is that you aren't passing in any parameters to the function.

Try the following with the proper values for the parameters.

select [dbo].[fnQtRefCor](@bostamp = ??, @ref = ??, @cor= ??);



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1406489
Posted Sunday, January 13, 2013 10:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 4:32 PM
Points: 257, Visits: 535
Hello Lynn Pettis

Yes, you are right i forget my parameters (bostamp,ref,cor)

Thanks for your reply
Luis Santos



Post #1406492
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse