|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
UDF functionCan I create a function that can be a part of a SELECT statement and it
would be capable of accepting a parameter that is a column name from one of the tables in the statement? Thanks I am not sure but how about a case statement
declare @columnNumber int set @columnNumber=1 select name, type, id, xtype, dynamiccolumn =case when @columnNumber=1 then convert(varchar(20),name) when @columnNumber=2 then convert(varchar(20),type) when @columnNumber=3 then convert(varchar(20),id) when @columnNumber=4 then convert(varchar(20),xtype) end from sysobjects -- Show quoteHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Mark Goldin" <mgol***@ufandd.com> wrote in message news:ewcLH%2310GHA.4796@TK2MSFTNGP03.phx.gbl... > Can I create a function that can be a part of a SELECT statement and it > would be capable of accepting a parameter that is a column name from one > of the tables in the statement? > > Thanks > <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message
news:ewcLH%2310GHA.4796@TK2MSFTNGP03.phx.gbl...</DIV>> Can I create a function that can be a part of a SELECT statement and it> would be capable of accepting a parameter that is a column name from one Yes, that's basically what functions are for.> of the tables in the statement? > Although if it is a Table-Valued function this only works on SQL 2005 using the APPLY operators. David Here is my statement
select runningjobs.id, runningjobs.job, nneedqty, description, rtrim(name) as name, reason, seconds, amountnoscans, downtime, total_scan from udf_GetJobs('09/08/2006', 5) a inner join runningjobs on a.job = runningjobs.job left join jobclosingdetail on runningjobs.id = jobclosingdetail.job_id inner join jobstatus on active = jobstatus.status inner join categorymachines on jobcategory = categorymachines.id inner join runningmachine r on r.runningjob_id = runningjobs.id cross apply udf_GetTotalBeingMade(r.id) as totalmade order by runningjobs.job I am getting an error in cross apply udf_GetTotalBeingMade(r.id) as totalmade Can you help, please? Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:%23yyEVL20GHA.1040@TK2MSFTNGP06.phx.gbl... > > > <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message > news:ewcLH%2310GHA.4796@TK2MSFTNGP03.phx.gbl...</DIV>> Can I create a > function that can be a part of a SELECT statement and it >> would be capable of accepting a parameter that is a column name from one >> of the tables in the statement? >> > > Yes, that's basically what functions are for. > > Although if it is a Table-Valued function this only works on SQL 2005 using > the APPLY operators. > > David > >"Mark Goldin" <mgol***@ufandd.com> wrote in message Please provide DDL and sample data.>news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >Here is my statement .. . . >Can you help, please? http://www.aspfaq.com/etiquette.asp?id=5006 David Like I said I ran a sample from BOL, and it's the same result (error).
Does that sample work for you? Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... > >"Mark Goldin" <mgol***@ufandd.com> wrote in message > >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>Here is my statement > . . . >>Can you help, please? > > > Please provide DDL and sample data. > http://www.aspfaq.com/etiquette.asp?id=5006 > > > > David > <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message
news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I said I ran a sample from BOL, and it's the same result (error).> Does that sample work for you? What sample?> David Show quote > "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in > message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>Here is my statement >> . . . >>>Can you help, please? >> >> >> Please provide DDL and sample data. >> http://www.aspfaq.com/etiquette.asp?id=5006 >> >> >> >> David >> > > BOL has a sample code of using APPLY.
I am copying it into QA and it gives me same error. Kind of funny. Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... > > > <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message > news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I said I ran a > sample from BOL, and it's the same result (error). >> Does that sample work for you? >> > > What sample? > > David >> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >> message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>Here is my statement >>> . . . >>>>Can you help, please? >>> >>> >>> Please provide DDL and sample data. >>> http://www.aspfaq.com/etiquette.asp?id=5006 >>> >>> >>> >>> David >>> >> >> You've probably already checked this, but please verify your version:
SELECT SERVERPROPERTY('productversion') -- Show quoteHTH Kalen Delaney, SQL Server MVP "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... > BOL has a sample code of using APPLY. > I am copying it into QA and it gives me same error. > Kind of funny. > > "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in > message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >> >> >> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message >> news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I said I ran a >> sample from BOL, and it's the same result (error). >>> Does that sample work for you? >>> >> >> What sample? >> >> David >>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>> message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>Here is my statement >>>> . . . >>>>>Can you help, please? >>>> >>>> >>>> Please provide DDL and sample data. >>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>> >>>> >>>> >>>> David >>>> >>> >>> > > 9.00.1399.06
is what I have. Show quote "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:OlQ$E860GHA.720@TK2MSFTNGP02.phx.gbl... > You've probably already checked this, but please verify your version: > > SELECT SERVERPROPERTY('productversion') > > -- > HTH > Kalen Delaney, SQL Server MVP > > > "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message > news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... >> BOL has a sample code of using APPLY. >> I am copying it into QA and it gives me same error. >> Kind of funny. >> >> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >> message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >>> >>> >>> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message >>> news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I said I ran a >>> sample from BOL, and it's the same result (error). >>>> Does that sample work for you? >>>> >>> >>> What sample? >>> >>> David >>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>>> message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>>Here is my statement >>>>> . . . >>>>>>Can you help, please? >>>>> >>>>> >>>>> Please provide DDL and sample data. >>>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>>> >>>>> >>>>> >>>>> David >>>>> >>>> >>>> >> >> > > What's your compatibility level:
EXEC sp_dbcmptlevel 'your_database' -- Show quoteHTH Kalen Delaney, SQL Server MVP "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message news:eMqrY%2360GHA.4108@TK2MSFTNGP04.phx.gbl... > 9.00.1399.06 > is what I have. > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:OlQ$E860GHA.720@TK2MSFTNGP02.phx.gbl... >> You've probably already checked this, but please verify your version: >> >> SELECT SERVERPROPERTY('productversion') >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> >> >> "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message >> news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... >>> BOL has a sample code of using APPLY. >>> I am copying it into QA and it gives me same error. >>> Kind of funny. >>> >>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>> message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >>>> >>>> >>>> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in >>>> message news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I >>>> said I ran a sample from BOL, and it's the same result (error). >>>>> Does that sample work for you? >>>>> >>>> >>>> What sample? >>>> >>>> David >>>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>>>> message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>>>Here is my statement >>>>>> . . . >>>>>>>Can you help, please? >>>>>> >>>>>> >>>>>> Please provide DDL and sample data. >>>>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>>>> >>>>>> >>>>>> >>>>>> David >>>>>> >>>>> >>>>> >>> >>> >> >> > > The current compatibility level is 80.
Show quote "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:uyMv4E70GHA.1256@TK2MSFTNGP04.phx.gbl... > What's your compatibility level: > > EXEC sp_dbcmptlevel 'your_database' > > -- > HTH > Kalen Delaney, SQL Server MVP > > > "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message > news:eMqrY%2360GHA.4108@TK2MSFTNGP04.phx.gbl... >> 9.00.1399.06 >> is what I have. >> >> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >> news:OlQ$E860GHA.720@TK2MSFTNGP02.phx.gbl... >>> You've probably already checked this, but please verify your version: >>> >>> SELECT SERVERPROPERTY('productversion') >>> >>> -- >>> HTH >>> Kalen Delaney, SQL Server MVP >>> >>> >>> "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message >>> news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... >>>> BOL has a sample code of using APPLY. >>>> I am copying it into QA and it gives me same error. >>>> Kind of funny. >>>> >>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>>> message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >>>>> >>>>> >>>>> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in >>>>> message news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I >>>>> said I ran a sample from BOL, and it's the same result (error). >>>>>> Does that sample work for you? >>>>>> >>>>> >>>>> What sample? >>>>> >>>>> David >>>>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote >>>>>> in message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>>>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>>>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>>>>Here is my statement >>>>>>> . . . >>>>>>>>Can you help, please? >>>>>>> >>>>>>> >>>>>>> Please provide DDL and sample data. >>>>>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>>>>> >>>>>>> >>>>>>> >>>>>>> David >>>>>>> >>>>>> >>>>>> >>>> >>>> >>> >>> >> >> > > It needs to be 90 to use APPLY.
-- Show quoteHTH Kalen Delaney, SQL Server MVP "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message news:ORkduM70GHA.4108@TK2MSFTNGP04.phx.gbl... > The current compatibility level is 80. > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:uyMv4E70GHA.1256@TK2MSFTNGP04.phx.gbl... >> What's your compatibility level: >> >> EXEC sp_dbcmptlevel 'your_database' >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> >> >> "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message >> news:eMqrY%2360GHA.4108@TK2MSFTNGP04.phx.gbl... >>> 9.00.1399.06 >>> is what I have. >>> >>> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >>> news:OlQ$E860GHA.720@TK2MSFTNGP02.phx.gbl... >>>> You've probably already checked this, but please verify your version: >>>> >>>> SELECT SERVERPROPERTY('productversion') >>>> >>>> -- >>>> HTH >>>> Kalen Delaney, SQL Server MVP >>>> >>>> >>>> "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message >>>> news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... >>>>> BOL has a sample code of using APPLY. >>>>> I am copying it into QA and it gives me same error. >>>>> Kind of funny. >>>>> >>>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>>>> message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >>>>>> >>>>>> >>>>>> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in >>>>>> message news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I >>>>>> said I ran a sample from BOL, and it's the same result (error). >>>>>>> Does that sample work for you? >>>>>>> >>>>>> >>>>>> What sample? >>>>>> >>>>>> David >>>>>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote >>>>>>> in message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>>>>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>>>>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>>>>>Here is my statement >>>>>>>> . . . >>>>>>>>>Can you help, please? >>>>>>>> >>>>>>>> >>>>>>>> Please provide DDL and sample data. >>>>>>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> David >>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > The code in the BOL APPLY sample worked for me.
-- Show quoteHTH Kalen Delaney, SQL Server MVP "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message news:eMqrY%2360GHA.4108@TK2MSFTNGP04.phx.gbl... > 9.00.1399.06 > is what I have. > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:OlQ$E860GHA.720@TK2MSFTNGP02.phx.gbl... >> You've probably already checked this, but please verify your version: >> >> SELECT SERVERPROPERTY('productversion') >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> >> >> "Mark Goldin" <markgoldin_2***@yahoo.com> wrote in message >> news:%23WqBy060GHA.1588@TK2MSFTNGP02.phx.gbl... >>> BOL has a sample code of using APPLY. >>> I am copying it into QA and it gives me same error. >>> Kind of funny. >>> >>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>> message news:%235koC150GHA.1292@TK2MSFTNGP03.phx.gbl... >>>> >>>> >>>> <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in >>>> message news:uIhe0o20GHA.4392@TK2MSFTNGP04.phx.gbl...</DIV>> Like I >>>> said I ran a sample from BOL, and it's the same result (error). >>>>> Does that sample work for you? >>>>> >>>> >>>> What sample? >>>> >>>> David >>>>> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in >>>>> message news:unzfOm20GHA.4796@TK2MSFTNGP03.phx.gbl... >>>>>> >"Mark Goldin" <mgol***@ufandd.com> wrote in message >>>>>> >news:ew21tZ20GHA.4656@TK2MSFTNGP04.phx.gbl... >>>>>>>Here is my statement >>>>>> . . . >>>>>>>Can you help, please? >>>>>> >>>>>> >>>>>> Please provide DDL and sample data. >>>>>> http://www.aspfaq.com/etiquette.asp?id=5006 >>>>>> >>>>>> >>>>>> >>>>>> David >>>>>> >>>>> >>>>> >>> >>> >> >> > > I ran sample form BOL, and I am getting same error.
Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:%23yyEVL20GHA.1040@TK2MSFTNGP06.phx.gbl... > > > <DIV>"Mark Goldin" <mgol***@ufandd.com> wrote in message > news:ewcLH%2310GHA.4796@TK2MSFTNGP03.phx.gbl...</DIV>> Can I create a > function that can be a part of a SELECT statement and it >> would be capable of accepting a parameter that is a column name from one >> of the tables in the statement? >> > > Yes, that's basically what functions are for. > > Although if it is a Table-Valued function this only works on SQL 2005 > using the APPLY operators. > > David  |
|||||||||||||||||||||||
Other interesting topics