I have created a function to return values, which works fine, but I can't do calculations in it.
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )
I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.
CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int
SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID
SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID
SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)
If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END
insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)
RETURN
END
I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
Hi, We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int), As Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Return End
This Works Fine :- And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int) As Begin Declare @cnt Int Set @cnt = 1 While @cnt <= 2 If @cnt =1 Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Set @cnt = @cnt + 1 End If @cnt =2 Begin Insert @k2 Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B On A.intcustomerid = B.intcustomerid Where Chremail <> ' ' And Chremail Is Not Null And Intpreferenceid In (6,7,2,3,12,10) Set @cnt2 = @cnt2 + 1 End End Return End
Can We Return Two Tables Or Is It Not Possible ? Pls Chk Into This And Tell Me.
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,....
I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT  A.CID, A.ANI, A.PID, B.RD FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
Hello, Below statement in tsql returns int value (0).
SELECT 5/10
How to force it to return decimal value (0.5)? I tried following but it did not help me 1) SELECT Convert(numeric(7,2),5/10) 2) Declare @Var1 numeric(7,2) SET @Var1 = SELECT Convert(numeric(7,2),5/10) SELECT @Var1
I am trying to apply cint function in the expression editor for the textbox
eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value).
This is returning error
The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow.
Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.
I have a table in my report which shows sales values for each month by looking at the month number
so for Jan i use
=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)
for Feb
=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)
this is all good and I get a series of rows per customer with the correct value in the correct column
JAN FEB Mar
Customer A 250
Customer A 350
Customer A 5000
.
However I want to create a summary a line
JAN FEB Mar
Customer A 250 350 5000
I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.
Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.
I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.
Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).
Report has three groups Org , Assessor and Unit.
Can someone please help me with this?
Appreciate help.
Thank you,
Ski
Org 1(Group1)
Unit Credits Units Assessed(# of Trainees) TotalCredits
Any idea why this expression would not work in SSRS?
Based on a expression on a group textbox I get 0 records that match: =Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)
I've evaluated in the proc & there should be a count of 29 records.
I found a work around by using this in the stored proc & I get my record count:
ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END
and changed the expression in SSRS to this & it works: =sum(Fields!ACCRUAL_CODE.Value)
Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID: Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)I cant seem to accomplish this. It returns nothing. Please help.TIA,Stue<code>Function Get_AttendID(ByVal strAttendID As String) As SqlDataReaderDim connString As String = ConfigurationSettings.AppSettings("ClassDB")Dim sqlConn As New SqlConnection(connString)Dim sqlCmd As SqlCommandDim dr As SqlDataReader sqlConn.Open()Dim strSQL As String = "Select AttendID from attendees Where FirstName=@FirstName and LastName=@LastName and classbegdt = @classbegdt and survey = '0'" sqlCmd = New SqlCommand(strSQL, sqlConn) sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@FirstName").Value = tbFirstName.TextsqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@LastName").Value = tbLastName.TextsqlCmd.Parameters.Add("@classbegdt", SqlDbType.DateTime, 8)sqlCmd.Parameters("@classbegdt").Value = calBegDate.SelectedDate.ToShortDateStringdr = sqlCmd.ExecuteReader()dr.Close()sqlConn.Close() Return dr End Function</code>
I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.
Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.
You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQLData" instead of "D:MSSQLData" I then supply the backslash in the SET statement. I assume that this has something to do with my question.
Any suggestions? Thank you.
HERE IS T-SQL FOR THE FUNCTION IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL BEGIN DROP FUNCTION fn_sqlmgr_get_mdf_filepath END GO
CREATE FUNCTION fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database. ) RETURNS NVARCHAR AS
BEGIN
--Extract the file path for the database MDF physical file. SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename)) FROM master..sysfiles mdf WHERE mdf.groupid = 1
SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('', REVERSE(@MDF_FILE_PATH)))
RETURN @MDF_FILE_PATH
END
HERE IS THE TEST I AM USING AGAINST THE FUNCTION SET NOCOUNT ON
DECLARE @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.
I'm new to SSRS (We are using SQL 2014) and have been asked to create a report from SharePoint list data. One of the columns in my list contains XML data. I need to pull three different fields from this XML. I see lots on using xml as a datasource but I'm using a SharePoint list as my datasource. Before I get too far down the wrong road I thought I'd ask here if there was a built in means of parsing XML or if I should look at using the Text Function under Common Functions in the Epression builder?
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script. This is my SP
AS Insert Into Tbl_ad (ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location) VALUES (@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity return GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats") Dim subcatid = Request.form("subcatrad") Dim adtitle = Request.Form("txttitle") Dim AdDescription = Request.form("txtdescription") Dim uid = getUID(Context.User.Identity.Name) Dim areaid = Request.form("ddarea") SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
Hii every one When i use the function of (select) from the data bass it return dataset or some thing else But I need it to return string or the data element which in the query not all the query
like
I dont need that _____________ | Id | Name | ----------------- | 1 | Bill | -------------------- I dont need All of that to display But I need to display the name only in Label or textbox like Bill
Hi, I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows). Is there a common way to avoid this? Thanx
Dear all, can i write a table return function like this?
create function my_function( returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME) insert into @mytable select col1,col2,col3,col4 from tab1 go select col1,col2,col3,col4 from tab2 go select col1,col2,col3,col4 from tab3 go select col1,col2,col3,col4 from tab4 go return end
am i doing correct? what i'm expecting from this function is, i need all the data from select statements should be inserted into one table. please guide me in this regard
Vinod Even you learn 1%, Learn it with 100% confidence.
return gpa_sem_3; } -------------------------------------------------------------------------------------------------------------------------------------------- //this is the main program(part of it only)
case 1:
system ( "cls" ); gpa_sem_1 = sem_1( );
break; -------------------------------------------------------------------------------------------------------------------------------------------------- // why doesn't the error promp when the user enters an invalid entry? in the codes below.
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC  Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.