It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>
Monday, February 27, 2012
help me....
hi everyone
i got 2 questions plz
1- how to use the datadiff function ....plz write the code
2- is there a Trim function or statment that cuts the unneeded spaces before and after the word
thanx ...
Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @.intCount int
SET @.intCount = 0
WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)
RETURN @.strValue
END
Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
thanx for answering
but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion
for an example lets say wa have a string " hi " i want to remove the spaces before and after hi
so how can i use it ?
|||Hi,if you don't want to use my function, simply use LTRIM(RTRIM(Expression))
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
help me...
below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do?
Message posted via http://www.droptable.com
There is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via droptable.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do?
> --
> Message posted via http://www.droptable.com
>
|||ejoeyz_85 via droptable.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do?
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca
|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via droptable.com wrote:
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do?
Message posted via http://www.droptable.com
There is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via droptable.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do?
> --
> Message posted via http://www.droptable.com
>
|||ejoeyz_85 via droptable.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do?
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca
|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via droptable.com wrote:
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca
help me...
below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do'
--
Message posted via http://www.sqlmonster.comThere is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via SQLMonster.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do'
> --
> Message posted via http://www.sqlmonster.com
>|||ejoeyz_85 via SQLMonster.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do'
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via SQLMonster.com wrote:
>> below is my sql query statement... hope u understand
>> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
>> Period_of_monitoring, m.Ongoing_maintenance,
>> m.Savings_for_inverter_replacement, m.Monitoring, m.
>> Total_anual_maint_and_monitor
>> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
>> WHERE p.Performance_id=m.MonitoringCost_id and
>> pv.PV_id=p.Performance_id and
>> pv.PV_id=m.MonitoringCost_id and
>> u.User_id =p.Performance_id and
>> u.User_id =pv.PV_id and
>> u.User_id = m.MonitoringCost_id
>> when i execute this query, it cannot executed because i got this error
>> message...
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the varchar value 'ejoy' to data type int.
>> what should i do'
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do'
--
Message posted via http://www.sqlmonster.comThere is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via SQLMonster.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do'
> --
> Message posted via http://www.sqlmonster.com
>|||ejoeyz_85 via SQLMonster.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do'
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via SQLMonster.com wrote:
>> below is my sql query statement... hope u understand
>> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
>> Period_of_monitoring, m.Ongoing_maintenance,
>> m.Savings_for_inverter_replacement, m.Monitoring, m.
>> Total_anual_maint_and_monitor
>> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
>> WHERE p.Performance_id=m.MonitoringCost_id and
>> pv.PV_id=p.Performance_id and
>> pv.PV_id=m.MonitoringCost_id and
>> u.User_id =p.Performance_id and
>> u.User_id =pv.PV_id and
>> u.User_id = m.MonitoringCost_id
>> when i execute this query, it cannot executed because i got this error
>> message...
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the varchar value 'ejoy' to data type int.
>> what should i do'
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca
Help me. "recbase.cpp"
Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.
jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.
jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
Help me. "recbase.cpp"
Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
Help me. "recbase.cpp"
Hello~
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
When I run my SQL Server Agent with a rather large database,
I get following event.
"SQL Server assertion: File: <recbase.cpp>, Line=1374"
"SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
EXCEPTION_ACCESS_VIOLATIO"
"SQL Server Stopping..."
So. I found resolution that running "DBCC CheckDB", But No Error Accured.
Can anyone help me with this error, Please
The SQL version is 2000 and all defaults are set for sp_configure
parameters.
jabul, in Seoul, Korea.jabul,
What service pack level are you on? Do any of these help? Looks like a
SQL Server bug, if none of these help then contact MS PSS.
FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt
to Add New Table Column
http://support.microsoft.com/?id=317852
An assertion in the Recbase.cpp file or the Record.inl file may occur
when an operation is performed on an instance of SQL Server
http://support.microsoft.com/?id=828337
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
jabul wrote:
> Hello~
> When I run my SQL Server Agent with a rather large database,
> I get following event.
> "SQL Server assertion: File: <recbase.cpp>, Line=1374"
> "SqlDumpExceptionHandler: 1696 Process, Fatal Exception c0000005
> EXCEPTION_ACCESS_VIOLATIO"
> "SQL Server Stopping..."
> So. I found resolution that running "DBCC CheckDB", But No Error Accured.
> Can anyone help me with this error, Please
> The SQL version is 2000 and all defaults are set for sp_configure
> parameters.
>
> jabul, in Seoul, Korea.
>
Subscribe to:
Posts (Atom)