Hello, DECLARE @x DECIMAL
SET @x = 65.554
SELECT ROUND(@x, 1)--this returns 66
SELECT ROUND(65.554, 1)--this returns 65.600 can someone explain to me why is like that?
Thank you
I'm a Reporting Services New-Be.
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 :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.
Low Qty High Qty Fee Actual Qty Price
0 7500 0.025 7500 187.50
7501 15000 0.020 2221 44.42
Below is the table definition that we have to work with (ugghh).
CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,
[Code] ....
What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628
The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.
ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)
[Code] ....
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
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:
ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[code]....
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
[Code] ....
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
Why this statement doesn't work correctly?
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
The select statement:
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
CAST(Date AS DATETIME) + CAST(Time AS DATETIME))
FROM [DataGapTest]
Gives the right output:
NULL
1
1
3548
0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time)
RETURNS INT
AS
BEGIN
DECLARE @timeInterval INT
SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))
FROM dbo.[DataGapTest]
RETURN @timeInterval
END
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
I have a money field, which I want to Round price in it to nearest .95 cent . The problem I have is, I don't know how to give 0.95 to Round function.
Thanks
Mazdak
Set @AVG = ROUND ((V1*5+V2*5)/10,0)
What is the difference between these codes?
Set @AVG = ROUND ((V1*5+V2*5)/10.0,0)
For values V1=80, V2=85;
First code gives 82! Second gives 83!:confused: :confused: :confused:
Hi all
if i run
SELECT ROUND(700/1224) -- from sql server queyr analysier iam getting value as "0"
but same query i run in Oracle
SELECT ROUND(700/1224) FROM DUAL; iam getting value ".571895425"
what is reason can some body explan me
Hi
I use round function
select round(1234.4545,2)
GO
Result
1234.4500
I want last two zero to be eliminate.
Please advise how?
Thanks
Jawad
Trying below instructions
create table t
(
indexvalue float
)
go
insert t
values (109.1)
insert t
values (109.3)
insert t
values (109.5)
insert t
values (109.9)
go
select *
from t
select sum (indexvalue) / count (*)
from t
select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect
from t --why round function doesn't work correctly in this select
select round (109.45, 1) -- but this line results 109.5 that is correct
I have a datetime stamp and I want to round off the the time to 11:00 if the timestamp is between 5AM and 5PM. If it is beyond then round off to 23:00. I don't use date I only use timestamp in my query so I just want to round off the timestamp. Is there is function to do that.
View 3 Replies View RelatedCan somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
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)
How can I code a stored procedure to do this?
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
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.
SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH )
PRINT @MDF_FILE_PATH
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
CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)
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)
SQLCommander.Commandtype = Commandtype.StoredProcedure
SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)
'// this section not working right, it wont write return id
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue
response.write(SQLCommander.Parameters("ReturnValue").Value)
SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()
End sub
Can anybody see anything I missing? I appreciate any imput
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
Thanks
Maro
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.
//this is the function
double sem_3 ( double gpa_sem_1, double gpa_sem_2 )
{
int back_3;
char grade [2];
double score = 0;
double gpa_sem_3;
double cgpa_sem_3;
double problem_solving_and_programming;
double fundamentals_of_finance;
printf("FCCS1013 Problem Solving and Programming : ");
scanf("%s",&grade );
score = grade_selection( grade );
problem_solving_and_programming = score * 3;
printf("FBFF1013 Fundamentals of Finance : ");
scanf("%s",&grade );
score = grade_selection( grade );
fundamentals_of_finance = score * 3;
gpa_sem_3 = ( ( problem_solving_and_programming + fundamentals_of_finance ) / 6 );
cgpa_sem_3 = ( ( gpa_sem_1 + gpa_sem_2 + gpa_sem_3 ) / 3 );
printf("GPA = %.4f", gpa_sem_3);
printf("CGPA = %.4f", cgpa_sem_3);
printf("press 0 to go back to option menu: ");
scanf("%d", &back_3);
if( back_3 == 0 )
{
system("cls");
main();
}
else
{
printf("invalid option ");
}
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.
double grade_selection ( char grade[] )
{
double score = 0;
if (strcmp( grade, "A+" ) ==0 || strcmp( grade, "a+" ) == 0)
{
score += 4.00;
}
else if (strcmp( grade, "A-" ) == 0 || strcmp( grade, "a-" ) == 0 )
{
score += 3.70;
}
else if (strcmp( grade, "B+" ) == 0 || strcmp( grade, "b+" ) == 0 )
{
score += 3.30;
}
else if (strcmp( grade, "B" ) == 0 || strcmp( grade, "b" ) == 0 )
{
score += 3.00;
}
else if (strcmp( grade, "B-" ) || strcmp( grade, "b-" ) == 0)
{
score += 2.70;
}
else if (strcmp( grade, "C+" ) || strcmp( grade, "c+" ) == 0 )
{
score += 2.30;
}
else if (strcmp( grade, "C" ) || strcmp( grade, "c" ) == 0 )
{
score += 2.00;
}
else if (strcmp( grade, "D" ) || strcmp( grade, "d" ) == 0 )
{
score += 1.00;
}
else if (strcmp( grade, "F" ) || strcmp( grade, "f" ) == 0 )
{
score += 0.00;
}
else
{
printf( "invalid grade" );
printf( "please enter again: " );
getchar( grade[2] );
}
return score;
}
-------------------------------------------------------------------------------------------------------------
thanks.
Hi,
I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.
How can I solve this problem and return varchar(MAX)?
Thanks
Hi guys!
the problem is this:
I have a procedure that execute a function and i want to catch the return function value but i'm getting a null value.
I'm using:
exec @a = <function>
but the @a variable is allways null...
If i exec the function directly, i get the correct value.
What am i doing wrong?
Thanks in advance.
I have a function that is using a select case decision structure to arrive at a return value:
[dbo].[fn_Window]
(@Start AS INT,
@End AS INT) RETURNS VARCHAR(5)
AS
BEGIN
SELECT
CASE
/*** Pick up the extended periods here ***/
WHEN @Start = 1 AND @End = 1 THEN 'TRUE'
ELSE 'FALSE'
END
END
**** from here how do I get the function to return true or false? ****
Can I assign the select case result to a variable? If so how?