SQL function help needed 1

[MeanJoeGreen13]

I am working on a sql function to be fed an invoice number, then calculate the difference between the current system date and the last GL posting "GLPOSTDT"

The function is not returning any results as of yet...see attached code

USE [TEST]
GO
/****** Object:  UserDefinedFunction [dbo].[CheckInvoiceDate]    Script Date: 01/08/2008 11:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER FUNCTION [dbo].[CheckInvoiceDate] (@invoicecode varchar(21))
RETURNS int AS  
BEGIN 
  declare @NumofDays int
  SET @NumofDays = 
( SELECT datediff(day, max(rm2.GLPOSTDT), getdate())
    FROM rm20201 rm2
    JOIN sop30200 sop on rm2.aptodcnm = sop.sopnumbe
    WHERE aptodcnm=@invoicecode and sop.soptype = 3) +
( SELECT datediff(day, max(rm2.GLPOSTDT), getdate())
    FROM rm30201 rm2
    JOIN sop30200 sop on rm2.aptodcnm = sop.sopnumbe
    WHERE aptodcnm=@invoicecode and sop.soptype = 3)
  return @NumofDays
END

 

[Qik3Coder]

You have verified that there is data in the table that you are looking for?
I am assuming you have a current copy of sop30200 in the TEST database.

I don't understand why you are taking the datediff and adding it to itself. If anything i would run the datediff and return (@numOfDays + @numOfDays), because it would be a lighter function.

-Sometimes the answer to your question is the hack that works

 

[bborissov]

Try (not tested):

USE [TEST]
GO
/****** Object:  UserDefinedFunction [dbo].[CheckInvoiceDate]    Script Date: 01/08/2008 11:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER FUNCTION [dbo].[CheckInvoiceDate] (@invoicecode varchar(21))
RETURNS int AS  
BEGIN
  declare @NumofDays int

  SELECT @NumofDays = SUM(Days)
        FROM (SELECT datediff(day, max(rm2.GLPOSTDT), getdate())
    FROM rm20201 rm2
    JOIN sop30200 sop on rm2.aptodcnm = sop.sopnumbe
    WHERE aptodcnm=@invoicecode and sop.soptype = 3
    UNION ALL
 SELECT datediff(day, max(rm2.GLPOSTDT), getdate())
    FROM rm30201 rm2
    JOIN sop30200 sop on rm2.aptodcnm = sop.sopnumbe
    WHERE aptodcnm=@invoicecode and sop.soptype = 3) TestMe
  return @NumofDays
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[MeanJoeGreen13]

Yes, there is data in the TEST db.

Sorry let me clarify: RM20201 is the open invoices table, and RM30201 is the invoice history table. The field GLPOSTDT simply needs to be checked for in both tables. I do not want to add the result together. My SQL is incorrect, I just am not sure how to check through both tables.

 

[RobertT687]

Is this SQLServer 2000 or 2005.
If 2000, you cannot use the Getdate() system function within your custom function.

 

[MeanJoeGreen13]

It is SQL 2005

 

[Qik3Coder]

you need to return either an int code, or a flag (text or bit(boolean) to identify whether or not the data is there.

Somthing like:
Function:
Declare @rm20201flag bit
set @rm20201flag = 0

if (datepart(day,GetDate())>0)
begin
set @rm20201flag = 1 --Success, data present
end

return @rm20201flag


-Sometimes the answer to your question is the hack that works

 

[Qik3Coder]

There is a work around for that. You create a view that does the getdate, then access the view.

-Sometimes the answer to your question is the hack that works