SQL Server Performance Forum – Threads Archive
inline table function
Pls help me to get the result for the below query..it says "Line 1: Incorrect syntax near ‘.’."select (select top 1 dtmovement from dbo.fn_storagefrom(txns.cntrid,404,12,2005)) from containertxns txns
where dbo.fn_storagefrom is inline table function , it can work for scalar function but its too slow….if you give how do use the above function in joins it will be great help
Thanks in advance
Hi ya, I don’t think that you can use a column name in the call to an inline table function… As it would cause a different table for every row in the txns table which I don’t think would make sense from a sql point of view Cheers
Twan
Hi, You can’t do like this. You can’t use the alias in the inner query (Which is used by u), Whatever you mention after from clause, you can’t use those tables alias in select clause. Try to use the inner query as inline table. example: select b.* from containertxns a, (select top 1 dtmovement from dbo.fn_storagefrom(txns.cntrid,404,12,2005)) b where a.Column=B.Column. Try this !! -Saravanan.
Thanks for ur reply. I can get output if i write like this: select (dbo.fn_storagefrom(1385,404,12,2005)) from containertxns –oR select (dbo.fn_storagefrom(c.cntrid,404,12,2005)) from containertxns c where c.cntrid=1385 ——–
but instead of 1385 i have to get every value in containertxns table (provided the above function is scalar not table) but the problem is slow performance for even a single record. but i have to get all values… kindly help me. I am trying in the past one week.
Your description is confsing … Is this an inline table function, yes or no? If you care to tell us roughly what the UDF does with those parameters, we might be able to suggest a better approach. For instance, if the UDF just does a lookup in another table, with the parameters taken from the current row in the table that you’re querying, then you should drop the UDF and add a direct JOIN to the lookup table, on the columns from which you take the parameters. You often see this approach taken by programmers used to procedures and functions, who are beginning to learn SQL. In SQL queries, you have to learn to use the set-based approach wherever possible (granted that you will find exceptions where you must use a different approach). If the parameters are fixed, not taken from the current row of the table that you’re querying, then the result from the UDF will not change and there is no point in calling the UDF within the main SELECT statement. In that case you store the result of the UDF in a variable, and add that variable to your column list.
Did you try the query suggested by saravanan? select b.* from containertxns a, (select top 1 dtmovement from dbo.fn_storagefrom(txns.cntrid,404,12,2005)) b where a.Column=B.Column. Madhivanan Failing to plan is Planning to fail
Thank you adrian. I here attached the queries…
–this is main qry, it works but since it is subquery, performance degrade even for single record like in where clause "from containers c where –c.id=1835" –container is the master table and containertxns are detail transaction table select distinct top 10 c.id,c.cntrno,
(dbo.fn_storagefrom(c.id,12,2005)) as storagefrom
from containers c
–if i can able to pass c.id as parameter in "in-line table function" then my query performance will be increased (i think bcas i can use join instead of subquery) but i Can’t able to pass dynamic parameter in "inline table function" —–
create function fn_storagefrom(@cid as int,@mmonth tinyint,@yyear as smallint)
returns smalldatetime
begin
return(
select top 1 dtmovement as StorageFrom from containertxns t1 where slcntrstatuscd in (‘ma’,’dl’,’fu’) and [email protected] and dtmovement –between starts
between (select top 1 dtmovement from vcontainertxns where [email protected] and dtmovement <(select dbo.fn_storageFrom1(@cid,@mmonth,@yyear)) order by dtmovement desc) and
(select dbo.fn_storageFrom1(@cid,@mmonth,@yyear)) –between ends
)
end —– alter function fn_storagefrom1(@cid as int,@mmonth tinyint,@yyear as smallint)
returns smalldatetime
as
begin
return(
select top 1 dtmovement from vcontainertxns where [email protected] and month(dtmovement)[email protected] and year(dtmovement)[email protected] order by dtmovement desc)
end
select b.* from containertxns a, (select top 1 dtmovement from dbo.fn_storagefrom(a.cntrid,404,12,2005)) b where a.cntrid=B.cntrid
gives result as Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘.’.
What is really killing performance for you is that your UDF is calling another UDF. To be honest, the variables that you’re passing are just the value from the column in the base table of your main query, plus what looks to be a set of year/month criteria. So yes, you should rework this as a correlated subquery. Using UDFs to simplify the handling of complex criteria is usually a bad idea – you’re usually much better off letting SQL Server handle a complex WHERE clause: you are telling SQL Server to do it one-by-one, where SQL Server – given the option – would start using indexes and statistics to do it in a more efficient way. Try rewriting the UDFs as a straight subquery, even if it goes two or three levels down. You could also try using a derived table to do the date range filtering, then join on that to filter the main data.
—————————————————————————————-
select b.* from containertxns a, (select top 1 dtmovement from dbo.fn_storagefrom(a.cntrid,404,12,2005)) b where a.cntrid=B.cntrid
gives result as Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘.’.
——————————————————————————————- Hi, This is what from your side. Here where is "B.cntrid". Because you are not selecting this column in your query. How many fields your function will return??? Becasue the same thing i tested. It is working fine. -Saravanan.
i have 2 tables: containers (master), containertxns (detail) In containers i have cntrno,cntrid(PK) In Containertxns i have (id,cntrid(FK),dtmovement,locid,slcntrstatuscd) slcntrstatuscd may be one of ‘ms’,’ma’,’dl’,’fu’ i have to generate a report with the following results:
cntrno, storagefrom, storageto, difference
i have to generate report by giving the values for locid,month,year conditions are : there may be morethan one ‘ms’/’ma’/’dl’ for the particular cntrid in containertxns. i have to select only 2 ms between given month and next nearest month. (may be in the same month) and year=2005 and locid=404
between that i have to check ‘dl’ or ‘fu’, if it exists then i have to select the second ‘ma’ date (top 2 @dt=dtmovement desc; select @dt) this is the "storage from". storageto is simply the dateof ‘ms’ (select top 1 from containertxns where slcntrstatuscd=’ms’ and month(dtmovement)=12 and year(dtmovement)=2005 and locid=404 order by cntrid,id,locid desc) now difference is simply storageto-storagefrom
——————- I could not get how to tackle the below query, I can get result for single cntrid but not able to get for all in the particular month
Hi, Can you give sample data, so that i could try easily and try to give the solution. -Saravanan.
select top 2 id,cntrno from containers
id cntrno
1BHCU3010412
2BHCU3012884 select id as txnid,cntrid,dtmovement,locid,slcntrstatuscd from containertxns where slcntrstatuscd in(‘ms’,’ma’,’dl’,’fu’) and cntrid=294 and locid=320 and month(dtmovement)<=12 order by id txnid cntrid dtmovement locid slcntrstatucd
743382942003-04-28 00:00:00320MA
819382942003-05-08 00:00:00320MS
1631192942003-08-20 00:00:00320MA
3690262942004-05-04 00:00:00320MA
3931512942004-05-28 00:00:00320MS
in the above i have to select 163119’th date along with cntrno (from containers table) for this container but i have to select for all container ids not only 294. Hope u understand.
Not entirely sure about the vcontainertxns table name in the UDF – just a typo? If you’re looking for the earliest dtmovement in a given month, for a given container id, and then the next dtmovement, you should use a derived table to give you that earliest date. Next, you add a subquery to the main query to lookup the next date based on the aggregate date from the derived table. select c.id, c.cntrno, t1.StorageFrom,
(select min(t2.dtmovement) from containertxns t2
where t2.slcntrstatuscd in (‘ma’,’dl’,’fu’)
and t2.dtmovement > x1.StorageFrom and t2.cntrid = c.id) StorageTo
from containers c
inner join
(select t1.cntrid, MIN(t1.dtmovement) StorageFrom
from containertxns t1
where t1.slcntrstatuscd in (‘ma’,’dl’,’fu’)
and YEAR(t1.dtmovement) = 2005 and month(t1.dtmovement) = 12) x1
on c.id = x1.cntrid I know, there’s some other considerations, but this should help you to combine the different parts in a better way. [edit]Subquery wasn’t right.[/edit]
]]>