October 31, 2011 at 5:27 am
Can't we create #temp tables in functions?
Is there any other solution for this?
Please let me know.
Thanks & REgards,
Sri
October 31, 2011 at 6:56 am
Functions can't create objects or modify data. You can use table variables in them, if they are either multi-value or scalar, but not table functions.
Can you post the code you have so far, and what you need it to do? Might be able to suggest an alternate solution that will get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 31, 2011 at 7:03 am
GSquared, Thank you so much for the quick reply. I am trying to use use #temp tables in Table-valued function.
After seeing your reply I came to know I cannot use #temp or @temp in these functions.
Now just trying to see alternative solution.
Here is my function.
---------
Alter FUNCTION RPT.fn_GetInitialFinalAuditValues
(
-- Add the parameters for the function here
@SerailNumber varchar(50)
)
RETURNS @tempValues TABLE (
SerialNumber varchar(50),
INITIAL_LT_UNBALANCE float,
INITIAL_LT_ANGLE float,
INITIAL_CR_UNBALANCE float,
INITIAL_CR_ANGLE float,
INITIAL_CR2_UNBALANCE float,
INITIAL_CR2_ANGLE float,
INITIAL_RT_UNBALANCE float,
INITIAL_RT_ANGLE float,
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
SELECT vw_DATA0.VALUE, QIS.STD_CHAR.CHAR_NAME, vw_DATA0.TSTAMP,vw_DATA0.COLL_ID,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'OP_STATUS')) AS OP_STATUS,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'REJECT_CODE')) AS REJECT_CODE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_CYCLE_TIME')) AS DS_CYCLE_TIME,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_SPIN_COUNT')) AS DS_BALANCER_SPIN_COUNT,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_LT_UNBALANCE')) AS DS_BALANCER_LT_UNBALANCE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_LT_ANGLE')) AS DS_BALANCER_LT_ANGLE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_CR_UNBALANCE')) AS DS_BALANCER_CR_UNBALANCE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_CR_ANGLE')) AS DS_BALANCER_CR_ANGLE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_RT_UNBALANCE')) AS DS_BALANCER_RT_UNBALANCE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_BALANCER_RT_ANGLE')) AS DS_BALANCER_RT_ANGLE,
(SELECT RPT.vw_DATA.VALUE
FROM RPT.vw_DATA INNER JOIN
QIS.STD_CHAR ON RPT.vw_DATA.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (RPT.vw_DATA.COLL_ID = vw_DATA0.COLL_ID) AND (QIS.STD_CHAR.CHAR_NAME = 'DS_MACH_AUDIT_MODE')) AS DS_MACH_AUDIT_MODE
into #temp
FROM RPT.vw_DATA as vw_DATA0 INNER JOIN
QIS.STD_CHAR ON vw_DATA0.CHAR_ID = QIS.STD_CHAR.CHAR_ID
WHERE (vw_DATA0.VALUE = @SerailNumber)
Delete from #temp where op_Status='X'
--select * into sri_Test from #temp
DECLARE @INITIAL_LT_UNBALANCE float
DECLARE @INITIAL_LT_ANGLE float
DECLARE @INITIAL_CR_UNBALANCE float
DECLARE @INITIAL_CR_ANGLE float
DECLARE @INITIAL_CR2_UNBALANCE float
DECLARE @INITIAL_CR2_ANGLE float
DECLARE @INITIAL_RT_UNBALANCE float
DECLARE @INITIAL_RT_ANGLE float
select top 1 @INITIAL_LT_UNBALANCE = DS_BALANCER_LT_UNBALANCE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_LT_ANGLE = DS_BALANCER_LT_ANGLE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_CR_UNBALANCE = DS_BALANCER_CR_UNBALANCE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_CR_ANGLE = DS_BALANCER_CR_ANGLE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_CR2_UNBALANCE = DS_BALANCER_CR2_UNBALANCE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_CR2_ANGLE = DS_BALANCER_CR2_ANGLE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_RT_UNBALANCE = DS_BALANCER_RT_UNBALANCE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
select top 1 @INITIAL_RT_ANGLE = DS_BALANCER_RT_ANGLE from #temp where isnull(DS_MACH_AUDIT_MODE,0) <> 1 order by TStamp
insert into @tempValues
(SerialNumber,
INITIAL_LT_UNBALANCE,
INITIAL_LT_ANGLE,
INITIAL_CR_UNBALANCE,
INITIAL_CR_ANGLE,
INITIAL_CR2_UNBALANCE,
INITIAL_CR2_ANGLE,
INITIAL_RT_UNBALANCE,
INITIAL_RT_ANGLE,
)
values
(
@SerialNumber,
@INITIAL_LT_UNBALANCE,
@INITIAL_LT_ANGLE,
@INITIAL_CR_UNBALANCE,
@INITIAL_CR_ANGLE,
@INITIAL_CR2_UNBALANCE,
@INITIAL_CR2_ANGLE,
@INITIAL_RT_UNBALANCE,
@INITIAL_RT_ANGLE,
)
RETURN
END
GO
October 31, 2011 at 7:13 am
Okay, that can definitely be improved relatively easily.
I'll need object definitions for the views/tables it's pulling data from to really rebuild it, but it can be done as a single Select statement with a few sub-queries (at worst), and will almost certainly be much faster than what you're trying to do here.
If you don't want that, just declare a table variable instead of Select Into a temp table, and you'll have code that will work. It'll be slower than a more standard solution, but it will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 31, 2011 at 7:40 am
GSquared,
So do you think that table variables will work in table-valued function?
I cannot put a direct query on the objects since the data I am pulling is related to properties and they are rows not columns.
Thanks & Regards,
Sri.
October 31, 2011 at 7:51 am
Table variables definitely work. Just not temp tables (or "real" tables, of course).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 31, 2011 at 9:01 am
GSquared, Thanks a lot. I really appreciate your help
It worked.
October 31, 2011 at 9:39 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply