'claim#' is not a recognized OPTIMIZER LOCK HINTS option.

[misgirlny]

Hi there,
I'm running the following query (SQL Server2000):

select claim#,y.proc1,qty1,y.proc2,qty2,y.proc3,qty3
from claims_tables,
udf_claim_procs(claim#) y


udf_claim_procs is a user-defined function accepting one parameter claim# which is a primery key for claims_tables,
and returning a table containing 1 row:
proc1,qty1,proc2,qty2,proc3,qty3

I get an error message
'claim#' is not a recognized OPTIMIZER LOCK HINTS option.
No explanations have been found on Microsoft.com
If I explicitly enter claim# like '123456' the query works fine. I use UDFs returning a value in the same way and do not have any problems.
Any ideas? Your help is greatly appreciated

 

[nigelrivett]

You are trying to get a value from another table and use it in the from clause to create a table which won't work (even if it did which value would it take?). You can use it in the where clause or select.

Not sure what you want but probably a different udf to produce each value for the select or process the data in a temp table first.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[misgirlny]

Thank you for your response. You are probably right.
I assumed that in my case the UDF result set(table) is working like a correlated subquery in the FROM clause and produces result set for each claim# from the outer query.
I'll try to modify UDF.
Thanks again

 

[stnkyminky]

misgirlny,

I'm having a similar problem posted at thread183-805811

Would you be willing to provide the solution you settled on for your problem?

Scott
Programmer Analyst
<{{><

 

[misgirlny]

Hi stnkyminky,
It took me awhile to refresh my memory. So I was trying to create a table claim#,proc1,qty1,proc2,qty2,proc3,qty3 using the following query:

select claim#,y.proc1,qty1,y.proc2,qty2,y.proc3,qty3
from claims_tables,
udf_claim_procs(claim#) y

I mistakenly expected that for each claim# in the table the UDF will return an entry for the result set. Unfortunately it does not work that way.
What I did is a following - very streight forward. If you know the better solution - pls let me know.

create table #temp
(
claim# varchar(12),
proc1 char(5),
qty1 int,
proc2 char(5),
qty2 int,
proc3 char(5),
qty3 int
)


declare claim_cursor cursor for
select x.claim#
from claims_table
declare @claim varchar(12)

open claim_cursor
fetch claim_cursor into @claim

while (@@fetch_status = 0)
begin
insert #temp select @claim,
proc1,qty1,
proc2,qty2,
proc3,qty3
from udf_claim_procs(@claim)

fetch claim_cursor into @claim
end

close claim_cursor
deallocate claim_cursor

That works.

 

[zhavic]

Can you post there your function udf_claim_procs ?

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[misgirlny]

CREATE FUNCTION claim_procs ( @claim varchar(12) )
RETURNS @pivot TABLE
(
proc1 char(5),
qty1 int,
proc2 char(5),
qty2 int,
proc3 char(5),
qty3 int
)

AS
BEGIN


declare @temp_pivot table
(
proc# char(5),
id_num int IDENTITY(1,1),
rec# varchar(7) null
)


insert into @temp_pivot
select distinct PROC#
from dbo.Claim_Dtls
where claim# = @claim

update @temp_pivot
set rec# = 'proc'+ convert(varchar(2),id_num)



INSERT @pivot
SELECT proc1 = (select proc# from @temp_pivot where rec# = 'proc1'),
SUM(CASE WHEN y.rec# = 'proc1' THEN 1 ELSE 0 END) AS 'qty1',
proc2 = (select proc# from @temp_pivot where rec# = 'proc2'),
SUM(CASE WHEN y.rec# = 'proc2' THEN 1 ELSE 0 END) AS 'qty2',
proc3 = (select proc# from @temp_pivot where rec# = 'proc3'),
SUM(CASE WHEN y.rec# = 'proc3' THEN 1 ELSE 0 END) AS 'qty3'
FROM dbo.Claim_Dtls x, @temp_pivot y
where x.proc# = y.proc# and
x.claim# = @claim



RETURN
END

 

[zhavic]

Do you need this done with only one select statement ( at this time I don't know how ) or can it be done with more select statements ( without using function ) ?

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.