|
HARI JJ wrote: Getting error
We have no way of knowing what the error is unless you let us know.
|
|
|
|
|
|
What have you tried? What is the error that you get? If you expect someone to write the function for you, then you're in the wrong place.
|
|
|
|
|
What precisely are you expecting us to do with this information? You need to supply the script for the function and the error you are getting for us to help you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
What I have nothing better to do than write your code for you tsk tsk not going to happen I'm afraid. How can you expect to learn if you don't research and try and do the coding, getting help when you stumble.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try something like
CREATE FUNCTION func_check_seat_availability
(
@in_bUS_No int,
@in_DateOfJourney datetime,
@in_NoOfAdults int,
@in_NoOfChildren int
)
RETURNS decimal(36,30)
AS
BEGIN
--considering 64 is the total count of bus
declare @value decimal(36,30)
select
@value=case when sum(@in_NoOfAdults+@in_NoOfChildren) >= 34 then 0
when sum(@in_NoOfAdults+@in_NoOfChildren) < 14 then 1
end
from Details where BusNo=@in_bus_No and DateOfJourney=@in_DateOfJourney
return @value
END
|
|
|
|
|
Good Day
i have been hit by this error this night.
I have a UDF defined like this
CREATE FUNCTION [dbo].[funcFormatPercentages_Extended]
(
@parPercentageToBeFormatted nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
RETURN CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(4, 1)) AS nvarchar(5)) + N'%'
END
so i will call the UDF with the value like this
select [dbo].[funcFormatPercentages_Extended_numeric]('-43.4703076923077')
OR
select [dbo].[funcFormatPercentages_Extended_numeric]('36.403813624481')
i get an Error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
I tried to change the datatypes and Precision around , but still the error.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
A guess only, try removing the N'%' and see if it makes a difference. % is an operator and the interpretor may be making a mistake on the operation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
decimal(4, 1) means a number with three digits before the decimal point and one digit after:
http://msdn.microsoft.com/en-gb/library/ms187746.aspx[^]
Based on your provided inputs: 36.403 * 100 = 3640.3
Therefore, your result requires four digits before the decimal point, so the precision should be 5:
RETURN CAST(CAST(CONVERT(float, @parPercentageToBeFormatted) * 100 AS decimal(5, 1)) AS nvarchar(5)) + N'%'
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
<pre>hi all below is my Store Proc
SELECT vb.Sno,
vb.Vehicle_Number,
vb.Vehicle_Booking_Date,
vb.Time_From,
vb.Time_To,
vb.Vehicle_Used_By,
vb.Cost_Code,
vb.Budget_Line,
lb.Vehicle_Number,
lb.Vehicle_Booking_Date,
lb.Time_From,
lb.Time_To,
lb.KM_Start,
lb.KM_End,
lb.Vehicle_Used_By,
lb.Cost_Code,
lb.Budget_Line
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno
where vb.Vehicle_Number = @Vehicle_Number
AND DATEPART(MONTH,vb.Vehicle_Booking_Date) = MONTH(getdate())
it is working fine. But here I need to put a condition, it should give priority to mtblLog_Book means if mtblLog_Book having data then it should show from mtblLog_Book table otherwise it should shows data from mtblVehicle_Booking for a particular Sno.
Actually firstly on the behalf of mtblVehicle_Booking I am binding a Gridview after updating the data it should show the data from mtblLog_Book.
So let me know please how to put a condition in my sp.
Thanks</pre>
|
|
|
|
|
Use a Case When or IsNull condition like this:
SELECT isnull(lb.somefield,vb.anotherfield) as columnname
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno If lb.somefield is null it will be replaced by vb.anotherfield
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
your query may be right, let me complete my sentence. according to you query it will show the not null columns, Yes I need that,
but suppose I update vehicle_no in LogBook, now both Vehcile_Booking table and LogBook tabel both have Different Vehicle_no, but after updation I just want to show it from LogBook table instead of Vehicle_Booking table.
|
|
|
|
|
Did you actually try what Jorgen wrote, the isnull test does exactly what you are asking for:
select ISNULL(LogBook, Vehcile_Booking) from .. .
Will test logbook and return a non null result, if it is null then it will return vehicle_booking.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote:
if it is null then it will return vehicle_booking
and if it is not null then, it will show value from LogBook?
|
|
|
|
|
No, if LogBook is not null then the not null value will be returned from logbook
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<pre lang="SQL">SELECT vb.Sno,
ISNULL(lb.Vehicle_Number,vb.Vehicle_Number) as Vehicle_Number,
ISNULL(lb.Vehicle_Booking_Date,vb.Vehicle_Booking_Date) as Vehicle_Booking_Date,
ISNULL(lb.Time_From,vb.Time_From) as vb.Time_To,
ISNULL(lb.Time_To,vb.Time_To) as vb.Time_To,
ISNULL(lb.Vehicle_Used_By,vb.Vehicle_Used_By) as Vehicle_Used_By,
ISNULL(lb.Cost_Code,vb.Cost_Code) as Cost_Code,
ISNULL(lb.Budget_Line,vb.Budget_Line) as Budget_Line,
lb.KM_Start,
lb.KM_End
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno
where vb.Vehicle_Number = @Vehicle_Number
AND DATEPART(MONTH,vb.Vehicle_Booking_Date) = MONTH(getdate())</pre>
showing error
Incorrect syntax near '.'.
|
|
|
|
|
Hello Experts,
As per Difference between Delete and truncate.
Everybody knows that We Can Recover the Data from if the Delete Query is fire and we can't Recover the Data if truncate is execute.
SO I need to know that How can we recover the Data when Delete is executed.
|
|
|
|
|
My google foo got me this article[^], don't know if it helps.
I consider a restore the only reliable way of getting the data back but then I have never attempted the log recovery method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's Really Superb... thanks RAH.
|
|
|
|
|
database design for a self food ordering system
|
|
|
|
|
It does not work like this here.
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.
Try them and tell if you face issues.
Members will be more than happy to help like this.
|
|
|
|
|
declare @i int
set @i=1
select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
i am trying to retrieve holidays list from db.but i am unable to retrieve ....can anyone tell me pls what is the error in my query
|
|
|
|
|
Assuming you are using SQL Server - take your code to SSMS and test it, then tell us that the error is.
If there is no error then check there is a record to get.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i don't know how to use if...exists
i am new to asp.net & database as well
my requirement is to get 3 consecutive working days
declare @i int
declare @count int
set @i=0
set @count=0
while(@count <= 3)
begin
1) select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
the above statement is to check whether holiday exists
2) IF DATEPART(day, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
the 2nd statement is to check whether weekend or not
set @i = @i + 1
if there no holiday and not weekend then only the following code is executed
set @count = @count + 1
here i have to retrieve the date...i don't know how to get the date...because i vil get 3 dates...
end
actually i no need to select holiday name in 1)statement...there i am checking for holiday..
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
