Passing parameters to function from a table error

[hwkranger]

I have the following:

select top 1
Loan_Num,
Due_date = (Select due_date from dbo.fnc_FetchSingleLoanInfo(Loan_num, add_date))
from
tblComments
order by
Add_date desc

Where definition of fnc_FetchSingleLoanInfo:

ALTER Function dbo.fnc_FetchSingleLoanInfo
(
@charLoanNum as char(10),
@dteSnapDate as datetime
)

RETURNS TABLE
as
RETURN
Select TOP 1 * FROM tblKeyLoanChanges
Where
Record_Date <=@dteSnapDate
AND
Loan_Num = @charLoanNum
Order by Record_Date desc

I'm recieving the following error:

Server: Msg 155, Level 15, State 1, Line 3
'Loan_num' is not a recognized OPTIMIZER LOCK HINTS option.

I know I can normally pass columns into a function and return a value... can I not do this when it returns a table? I'm not sure what the problem is here.

Thanks for the help!

Randall Vollen
National City Bank Corp.

 

[Catadmin]

Try this:

...(due_date from dbo.fnc_FetchSingleLoanInfo(Loan_num, add_date)) as DueDate
....

SELECT statements don't like = signs.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[hwkranger]

Didn't work. tried it :-(

I looked online and there was something about the TOP statement that someone had an issue with.. but it was referencing using OPENQUERY and ACCESS. I'm just using Query Analyzer.

I'm using SQL2k as well.

Thank you.

Randall Vollen
National City Bank Corp.

 

[hwkranger]

I didn't think this would work, but I tried it as well:

select top 1
Loan_Num,
due_date
from
tblComments C Inner join
fnc_FetchSingleLoanInfo(C.Loan_num, C.Add_Date) F
on C.Loan_Num = F.Loan_Num
order by
Add_date desc

The problem I have, is that I can't seem to pass the columns as parameters to the function..... hints are appreciated

Randall Vollen
National City Bank Corp.

 

[Catadmin]

Oh. Doh. It won't let you use a column in the function, because by the time the function is "run", you don't have a value for LoanNum because the rest of the query hasn't run yet.

Declare @LoanNum int, @AddDate datetime

Set @LoanNum = (select top 1  Loan_Num
                 from  tblComments
                 order by  Add_date desc)

Set @AddDate = (Select top 1 Add_Date
                from tblComments
                where Loan_num = @LoanNum
                Order by Add_date Desc)
--you may not need that Order By statement, BTW

Exec dbo.fnc_FetchSingleLoanInfo(@Loannum, @adddate)

--Or this might work.

Select @LoanDate, @AddDate, dbo.fnc_FetchSingleLoanInfo(@Loannum, @adddate)

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[hwkranger]

I was hoping to run this over the ENTIRE Table...

I thought that was my problem..... what I don't understand though is I can do the following:

SELECT
 Man,
 Investor,
 EI_Score, 
 Item_Count = Count(*),
 BucketID = (dbo.fnc_rtnBucketID(Due_Date, getdate())),
 Record_Date = CONVERT(char(12), GETDATE(), 1)
FROM
 dbo.vw_Delinquent_Loans
WHERE
 EI_Score is not null
  AND
 left(Loan_Num, 3) <> '610'
GROUP BY
 Man,
 Investor,
 EI_Score, 
 (dbo.fnc_rtnBucketID(Due_Date, getdate()))

if you notice, dbo.fnc_rtnBucketID(Due_Date, getdate()) works.....

it's definition though is:

ALTER          function dbo.fnc_rtnBucketID
(
@due_date datetime,
@record_date datetime,
@ShowTrueBucket bit = 0
)
Returns Int
as 
-- COMPLETED 8/5/06
BEGIN
 SET @record_date=dateadd("d", -1, @record_date)
 DECLARE @int_Bucket integer
    
    If Month(@due_date) = Month(@record_date) Or Month(@due_date) > Month(@record_date) 
        set @int_Bucket=(case when @due_date > @record_date then -1 else 0 end)
    Else
        If day(@due_date) > 1 
            set @int_Bucket= (month(@record_date)) - Month(DateAdd("m", 1, @due_date))
        Else
            set @int_Bucket= (month(@record_date)) - (month(@due_date))

IF @ShowTrueBucket = 1
BEGIN
    SET @int_Bucket= @int_Bucket + 12*(year(@record_date) - year(@due_date))
END
IF @ShowTrueBucket = 0
 BEGIN
   SET @int_Bucket  = @int_Bucket + 12*(year(@record_date) - year(@due_date))
   IF @int_Bucket < -1
     BEGIN
       SET @int_Bucket = -1  
     END
 END
RETURN @int_Bucket


/*
	DECLARE @int_age as int
	set @int_age = datediff("m", @due_date, @record_date)

	return (@int_age)
*/
END

Is the difference in it working/not working due to the return type?

Randall Vollen
National City Bank Corp.

 

[Catadmin]

I don't know about the return type being an issue. I haven't written a lot of functions.

I'm on a WHILE Loop kick lately, so my brain is too wrapped up in using that as a solution to recommend any other. Sorry.

Anyone else have any ideas of how he can do this without a Cursor or a WHILE loop in his function?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[hwkranger]

Well. I think I figured this out. I think it has to do with the compiling order, which I suspected, but I was hoping that I could get around it.

This is what I did to fix it:

select top 1
 Loan_Num,
 due_date=(dbo.fnc_FetchSingleLoanInfo (Loan_num, add_date) )
from 
 tblComments

ALTER  Function dbo.fnc_FetchSingleLoanInfo
 (
  @charLoanNum as char(10),
  @dteSnapDate as datetime
 )

RETURNS DATETIME
as
	BEGIN
	DECLARE @RETURN as Datetime
	
	IF EXISTS(Select TOP 1 * FROM tblKeyLoanChanges
	 Where
	   Record_Date <=@dteSnapDate
	    AND
	   Loan_Num = @charLoanNum
	 Order by Record_Date desc)
	BEGIN
	 SET @RETURN = (Select TOP 1 DUE_DATE FROM tblKeyLoanChanges
	 Where
	   Record_Date <=@dteSnapDate
	    AND
	   Loan_Num = @charLoanNum
	 Order by Record_Date desc)
	END
	RETURN @RETURN
END

Notice I changed the return type. Apparently, since the UDF isn't precompiled, it gets compiled in the subquery FIRST. Normally, the FROM clause is compiled first when passing columns between tables in a select with a subquery. This would be true in the following:

Select
 A,
 Something = (Select TOP 1 Blah From Table2 where ID = A),
From
 Table2

I asked a friend for help -- he didn't really give me the answer, but I'll post what I replied to him and what I thought was the issue. If someone knows better -- I would appreciate being corrected:

him:

This was just a quick look but here is my take on it. I don't think you can do what you are trying to do because you are using the function to determine the table that comprises the from clause. This alone is not an error but you are using something in the outer select statement as a parameter. The compilation order of an SQL statement goes something like this. FROM < WHERE < ...... < SELECT. What you are doing with your subquery is running a select statement without a table to search through. It's a circular dependency. If you look at what you posted in tek-tips you aren't using the function for any type of query. Therefore the function will run during the SELECT phase of compilation giving you no errors. I'm pretty sure this is what is messing you up. I'm no MCDBA or anything but it makes sense to me. I gotta bounce for a bit. Call me later if you want a more detailed explanation.

My reply

It’s in the select, so it’s run last. It should be able to pass.



Example:

SELECT
Man,
Investor,
EI_Score,
Item_Count = Count(*),
BucketID = (dbo.fnc_rtnBucketID(Due_Date, getdate())),
Record_Date = CONVERT(char(12), GETDATE(), 1)
FROM



WORKS



But

select top 1
Loan_Num,
Due_date = (Select due_date from dbo.fnc_FetchSingleLoanInfo(Loan_num, add_date))
from



Does not.


It’s nested too deeply. The from clause is compiled in the SELECT First since it’s a subquery. There’s no circular dependency – It’s a compile order issue. I need to change it from a return type of TABLE to DATE for just that specific field.



Although this doesn’t totally make sense to me, because I know that something like:



Select

A,

Something = (Select TOP 1 Blah From Table2 where ID = A),

From

Table2



Will work. I’m not 100% sure why there’s a difference, but I think it’s because the function has to be compiled first since it needs parameters at runtime. Whereas……… the join between tables depend on indexed pairs (ideally) or at the least the path can be precompiled – which is not the case with a UDF.

Randall Vollen
Meryl Lynch