I have this UDF SQL Server 2005
ALTER function [dbo].[ftsls030](@comno VARCHAR(3)) RETURNS @T TABLE(COMNO VARCHAR(3),T$CUNO VARCHAR(6),T$ITEM VARCHAR(16),T$CNTR VARCHAR,T$DILE INT,T$STDT varchar(10),T$TDAT varchar(10),T$DISC DECIMAL(6,4))
/*----------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @T TABLE(COMNO VARCHAR(3),T$CUNO VARCHAR(6),T$ITEM VARChAR(16),T$CNTR VARCHAR,T$DILE INT,T$STDT varchar(10),T$TDAT varchar(10),T$DISC DECIMAL(6,4))
DECLARE @COMNO VARcHAR(3);SELECT @COMNO='010'
-----------------------------------------------------------------------------------------------------------------------------------------------------*/
begin
set quoted_identifier off
if dbo.fsBaanServer()="Hades"
Begin
IF @COMNO='010'
INSERT @T
select * from openQuery(Hades ,"select '010' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030010 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')" )
IF @COMNO='020'
INSERT @T
SELECT * FROM OPENquery(hades,"select '020' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030020 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')")
end
if dbo.fsBaanServer()="Athena"
Begin
IF @COMNO='010'
INSERT @T
select * from openQuery(athena ,"select '010' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030010 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')" )
IF @COMNO='020'
INSERT @T
SELECT * FROM OPENquery(Athena,"select '020' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030020 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')")
end
return
end
Which compiles fine .. But when I do
select * from ftsls030("010")
I get the following error
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'select '020' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yy'.
BTW: when select clause is executed as is it works fine too.. How can I resolve this ? Thanks
can you alter the function to have 10,20,30+40 instead of 10,20,10,20 and add those results to your question, just so we know where the issue is?