Scalar Function Columns

Jul 20, 2005

Is it ill-advised to have columns whose values pull from scalar functions
using other fields in the record as parameters? For example, if I have

create table a(iID int primary key)
create table b(
iID int ,iDetail int,
CONSTRAINT PK PRIMARY KEY(iID,iDetail),
CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID)
)


Let's say in table b I put price information for each detail and in table a
I'd like to put a column that sums these prices for the children of each
record. Should I make a computed column that references a function using
iID as a parameter? Or would it be better to create a view for this kind of
purpose?


Regards,


Tyler

View 4 Replies


ADVERTISEMENT

UD Scalar Function

Mar 5, 2008

I want to add four fields of a table and place the toatal in a new field.Also I wanna have the average of the fields.

For e.g
I have created a marksheet having four subjects.Now I wanna add the subjects and find the average of the subjects and place them in two different fields in the same table along with the respective names in the table.Pls help.

Thanks in advance.

View 8 Replies View Related

T-SQL (SS2K8) :: UDF Scalar Value Function

Jun 24, 2015

I build a UDF scalar function like this:

CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT

[Code] ....

Curiously when i call my function the same one return always the same value, ex:

Select VerificaAcessoPerfil(2)

the return value is : 698 ??

but if i run the Select statment like this:

SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC

The value are:

1695
2211
41
10698
11697
13696

View 9 Replies View Related

SQL Server 2012 :: How To Use Scalar Function Without WHILE

Jan 19, 2014

I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:

;WITH cte1 AS (
SELECT b.FirstName,
(SELECT fn_similarity('Test', b.FirstName)) AS [Value],
b.LastName
FROM [AdventureWorks2012].[Person].[Person] b
)

SELECT *
FROM cte1
WHERE [Value] > 50.00
ORDER BY [Value] DESC

Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.

At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?

View 9 Replies View Related

How To Handle Error In Scalar Function?

Nov 28, 2007

Hi, I have the follow function:

CREATE FUNCTION [dbo].[ToTime]
(
@intHora int, --A valid hour
@intMin int -- A valid minute
)
RETURNS smalldatetime
AS
BEGIN
declare @strTime smalldatetime
declare @errorvar int

select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar)
return @strTime;
END

the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error.
I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example

if @intHora < 0 or @intHora >23
begin
set @intHora = 0
end
if @intMin <0 or @intMin>59
begin
set @intMin = 0
end

please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.

View 4 Replies View Related

T-SQL (SS2K8) :: Proper Use Of CTE In Scalar Valued Function?

Dec 1, 2014

I have troubles with this scalar-valued UDF:

I get the error:

Select statements included within a function cannot return data to a client.

Is this a proper way to include a CTE in a function?

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)

[code].....

View 4 Replies View Related

Transact SQL :: Storing Parameter Value Used By Scalar Function

May 14, 2015

I've a scalar function which is killing my performance. I've been used the SQL profiler and also DMVs to catch execution information. I'd like to store the value received by this function and also the time that it happened, but I can't think in a way to do it.

View 5 Replies View Related

SOLVED: How To Call Scalar Function From JDBC

Jan 28, 2008

As an example, I have a scalar function called TRIM that takes a VARCHAR parameter, does a LTRIM(TRIM(VARCHAR)), and returns the result.

How can I call this function from java using JDBC? I have only had luck calling basic stored procedures, but I need to call functions as well.

Thanks, Ken

View 1 Replies View Related

Table Scalar Function Syntax. . How Wrong And How Far Am I?

Aug 3, 2006

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION DetailedView

AS

BEGIN

Declare @fieldname varchar(10)

Declare @stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @stmt = 'Select pono, myid, billtype'

Open Fields

Fetch Next From Fields Into @fieldname

While @@Fetch_Status = 0 Begin

Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname

Fetch Next From Fields Into @fieldname

End

Close Fields

Deallocate Fields

Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '

return Exec(@stmt)

END

View 3 Replies View Related

Return NULL From A CLR Scalar-Valued Function

Jul 11, 2007

Hi,



I have an assembly that contains the following function:



Public Class Lookup



<SqlFunction()> _

Public Shared Function MyTest() As Integer

Return System.Data.SqlTypes.SqlInt64.Null

End Function



End Class



Then in SSMS:



CREATE ASSEMBLY IRS_MyTest

FROM '\machine empmyAssembly.dll'

GO

CREATE FUNCTION dbo.MyTest() RETURNS INT

AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest

GO



when I run:



SELECT dbo.MyTest()



the following is returned:



Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlInt64.get_Value()

at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)

at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()





Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.







Thanks,



Jan.

View 1 Replies View Related

Display Single Number Using ObjectDataSource Scalar Function

Mar 10, 2008

I wrote a Scalar UDF in SQL2005 that returns an integer.  I want to be able to display this integer in a ASP.Net 2.0 web page.  I typically use a DAL for all data so I added an ObjectDataSource as a Qeury that contains only the UDF.  How do I easily display the value in a Label Control or?
I have tried to use a Repeater with a label, a Formview with a Label, all to no avail.  Any suggestions?

View 12 Replies View Related

SQL Server 2012 :: Scalar Function Returning Zero When It Shouldn't

Jun 16, 2015

I have this code:

Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)

and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:

select dbo.fnGet_Rec_Count('ACA_RSF') as cnt

here is the code:

alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN

-- Declare the return variable here

DECLARE @count bigint

-- Add the T-SQL statements to compute the return value here

select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')

-- Return the result of the function

RETURN @count
END
GO

I get zero regardless of where @count is declared as in or bigint.

View 9 Replies View Related

Alternate Of Scalar Function For Comma Sepated Selection

Aug 9, 2007

I have 2 Tables as below,



1) Purchase_Invoice
a. PurchaseInvoiceID
b. SupplierName
c. BillNo
d. BillDate
2) Purchase_Invoice_Items
a. PurchaseInvoiceItemID
b. PurhcaseInvoiceID (FK to Purchase_Invoice Table)
c. ItemName
d. Quantity
e. Rate

Now I want to select all the records of Purhcase_Invoice table exactly once with one column at last containing comma separated Item name of particular PurhcaseInvoiceID as below

PurchaseInvoiceID | SupplierName | BillNo | BillDate | Items (Comma Separated Items)

Currently I am using Scalar Function which takes PurchaseInvoiceID as Argument and Returns Comma Separated Itemnames€¦

It works well but when number of records are large than performance is very poor.

Is there another way of doing same thing? By Join or By some System Function?

Nilesh

View 1 Replies View Related

Must Declare The Scalar Variable In Table-valued Function

May 18, 2007

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@dateStart DATETIME,
@dateEnd DATETIME
)
RETURNS @arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @arTemp.Insurer AND
Pims.NABP = @arTemp.NABP AND
Pims.NumRx = @arTemp.NumRx
);

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View 10 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

SQL XML :: Scalar Valued Function Slow Querying XML Passed As Parameter

May 28, 2015

I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.

My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?

The two versions of the SVF (they return the rowcount in this simplified example):

CREATE FUNCTION [dbo].[FastFunction]
(
@inDetaljerText varchar(MAX)
)
RETURNS int

[Code] ....

The two versions of the SP

CREATE PROCEDURE [dbo].[FastProcedure]
AS
BEGIN
SET NOCOUNT ON;
select
dbo.FastFunction(al.Detaljer)

[Code] ....

View 2 Replies View Related

Calling Scalar Valued Function From SSIS OleDB Command Transformation

Mar 2, 2007

Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!!

View 8 Replies View Related

How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly

Jul 26, 2006

Dear all,

Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.

I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:

select dbo.CLRHTMLString('abc')

The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).

To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:

EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.

Note: The 'webuser' database role has Execute permission on the Assembly.

I have also tested this from my actual web page, with the following results:
(1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly.
(2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.

Further test results:
(3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.

(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.

Thankful for any advice on this matter.

View 4 Replies View Related

Text To Columns Function?

Sep 11, 2013

I have the following Table:

ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR

All the values in the 'SKUs' column are combined together, seperated by a comma.

Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:

ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR

Here's some code:

SELECT ORDER_ID, SKU FROM ORDERS

View 4 Replies View Related

Passing Columns To CLR Function

Jul 31, 2007



Hello,
I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:




Code Snippet
public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)
{
//Do Something...
x = ....
y=...
z=...
}


I want to call this code from SQL code:






Code Snippet

create table #Temp (float a,float b)
declare @x float
declare @y float
declare @z float
exec dbo.DoSomething(a,b,@x,@y,@z) ---????






Do someone have an idea?

View 1 Replies View Related

Need Help With MAX Function Using Two Tables With Common Columns

Apr 9, 2006

I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan

View 4 Replies View Related

COUNT FUNCTION ON MULTIPLE COLUMNS

Nov 14, 2006

I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.

View 1 Replies View Related

Function That Returns Highest Of Two Columns?

Jul 20, 2005

Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???

View 2 Replies View Related

SQL Server 2008 :: Using Decimal Columns In SUM Function

Jul 21, 2015

Recently I have come across a requirement where i need to design a table.

There are some columns in table like below with DECIMAL Datatype:

BldgLength

BldgHeight

BldgWeight

Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

Now as per MSDN,

Precision => 1 - 9
Storage bytes => 5

so i can create column as:

BldgLengthDECIMAL(6,2) DEFAULT 0

OR

BldgLengthDECIMAL(9,2) DEFAULT 0

Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

So my question is what should be value of DataType for above column ?

View 8 Replies View Related

Transact SQL :: Subtract Two Columns With Windows Function

Dec 5, 2015

How can i subtract two columns:

I have table:
       
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2

But to continue for each row and value is reduced by the previously value.

My output should be like as:

ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100

View 3 Replies View Related

Using COUNT Function On Multiple Columns With GROUP BY Restrictions

Mar 10, 2014

Consider the following dataset:

COL1 | COL2 | COL3 | COL4
1 | FD | DR. A | Y
2 | FD | DR. A | Y
3 | FD | DR. A | N
4 | FD | DR. A | Y
5 | FD | DR. A | Y
6 | PF | DR. A | Y
7 | FD | DR. B | Y
8 | PF | DR. B | N

Consider the script below:

SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
FROM CASES
GROUP BY COL2, COL3
ORDER BY COL3, COL2

The script above produces the following output:

COL2 | COL3 | TOTALS
FD | DR. A | 5
PF | DR. A | 1
FD | DR. B | 1
PF | DR. B | 1

I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:

COL2 | COL3 | TOTALS | NEWCOL
FD | DR. A | 5 | 4
PF | DR. A | 1 | 1
FD | DR. B | 1 | 1
PF | DR. B | 1 | 0

I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.

View 2 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record

Aug 25, 2006

Hello,

I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.

Any help you can provide will be greatly appreciated!

Thank you,
Nathan

View 17 Replies View Related

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

May 12, 2015

I've to write a function to return a comma delimited values from a table columns

If a table has Tab1 ( Col1,Col2,Col3).

E.g. as below ( the columnName content I want to use as columns for my pivot table

CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;

I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....

View 13 Replies View Related

How To Keep The Toggle Columns Function When I Render A Tabular Report To An Excel Format?

Apr 15, 2008



Hi all,

Currently, I am using the SSRS 2005 and try to toggle the report columns similar to Excel's column grouping function. Users can expand "+" / collapse "-" the columns in the render excel report. It works fine when I excute the tabular report in the web yet the toggle function is missing when I render the report to an Excel report. I am not sure the toggle function will only work for the row items or both as the excel report can keep the toggle function for the row items. Please help.

View 2 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

Need Help - UDF SQL 00 To Scalar SQL 05

Jul 7, 2006

I'm having trouble with the UDF's I had in SQL 2000 that were migrated to SQL 2005 under the SCALAR FUNCTION item. Before in 2000 you just created the UDF, named it and saved. IN SQL 2005 you create/modify and then have to save it as a .SQL file to a folder. First of all, what folder should it go to so that all can use and secondly, when I do this and run the functions it doesn't work... I need to modify the scalar function and
save it with out saving to a .SQL file. How do I do this or is this the new way to create/modify functions. Any help is much appreciated
thanks

View 6 Replies View Related

Scalar Value

Nov 5, 2006

What is the definition of a scalar value in a relational database?

thx,

Kat

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved