Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 30, 2012
Query question
Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>
Wednesday, March 28, 2012
Query problem using groups
Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
Damien
How do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started =
(SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
David Portas
SQL Server MVP
|||Yes that solves it for me, thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
Damien
How do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started =
(SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
David Portas
SQL Server MVP
|||Yes that solves it for me, thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Query problem using groups
Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
DamienHow do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started = (SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
--
David Portas
SQL Server MVP
--
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
DamienHow do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started = (SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
--
David Portas
SQL Server MVP
--
Query problem
I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!
It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>
|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!
It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>
|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>
Query problem
I want to restrict the results of a query only where the field 'email' has
something in it. I had a record with data in 'email' and deleted it and my
query still returns the record even thoug part of my statements reads:
Select * from Users
Where email is NOT NULL OR email <> ''
Even though I deleted what was in the 'email' field I still get the
recordset and the email field is blank.
Any clues ?
AleksHow can we reproduce the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:
> I want to restrict the results of a query only where the field 'email' has
> something in it. I had a record with data in 'email' and deleted it and my
> query still returns the record even thoug part of my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
> Any clues ?
> Aleks
>
>|||Aleks wrote:
> I want to restrict the results of a query only where the field
> 'email' has something in it. I had a record with data in 'email' and
> deleted it and my query still returns the record even thoug part of
> my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
>
Change "OR" to "AND".
When you deleted the contents, you likely did not set the field to Null, so
"email is NOT NULL" evaluates to True, resulting in the OR expression
evaluating to True.
Better yet, do something like this:
Where email > CHAR(1)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
something in it. I had a record with data in 'email' and deleted it and my
query still returns the record even thoug part of my statements reads:
Select * from Users
Where email is NOT NULL OR email <> ''
Even though I deleted what was in the 'email' field I still get the
recordset and the email field is blank.
Any clues ?
AleksHow can we reproduce the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:
> I want to restrict the results of a query only where the field 'email' has
> something in it. I had a record with data in 'email' and deleted it and my
> query still returns the record even thoug part of my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
> Any clues ?
> Aleks
>
>|||Aleks wrote:
> I want to restrict the results of a query only where the field
> 'email' has something in it. I had a record with data in 'email' and
> deleted it and my query still returns the record even thoug part of
> my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
>
Change "OR" to "AND".
When you deleted the contents, you likely did not set the field to Null, so
"email is NOT NULL" evaluates to True, resulting in the OR expression
evaluating to True.
Better yet, do something like this:
Where email > CHAR(1)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Monday, March 26, 2012
Query problem
I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'äº?次é'æ?¹')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
> >I am using SQL SERVER 2005 FT-enable database,
> >
> > repleat same query it returns the results are expect,but try five times
> > later,
> >
> > it returns record is no data,
> >
> > Has anyone seen this issue ?
> >
> > THANKS!!
> >
>
>
repleat same query it returns the results are expect,but try five times later,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'äº?次é'æ?¹')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
> >I am using SQL SERVER 2005 FT-enable database,
> >
> > repleat same query it returns the results are expect,but try five times
> > later,
> >
> > it returns record is no data,
> >
> > Has anyone seen this issue ?
> >
> > THANKS!!
> >
>
>
Query problem
I am using SQL SERVER 2005 FT-enable database,
repleat same query it returns the results are expect,but try five times late
r,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/genera...br />
red.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/genera... />
wered.html )
>
> The less 'set up' work we have to do, the more likely you are going to hav
e
> folks tackle your problem and help you. Without this effort from you, we a
re
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>
repleat same query it returns the results are expect,but try five times late
r,
it returns record is no data,
Has anyone seen this issue ?
THANKS!!It is difficult to understand your question.
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/genera...br />
red.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"akira-888" <akira888@.discussions.microsoft.com> wrote in message
news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>I am using SQL SERVER 2005 FT-enable database,
> repleat same query it returns the results are expect,but try five times
> later,
> it returns record is no data,
> Has anyone seen this issue ?
> THANKS!!
>|||Sorry my poor english
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok
but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode
Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record
"Arnie Rowland" wrote:
> It is difficult to understand your question.
> It would help us better assist you if you could include table DDL, query
> strategy used so far, sample data in the form of INSERT statements, and an
> illustration of the desired results. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/genera... />
wered.html )
>
> The less 'set up' work we have to do, the more likely you are going to hav
e
> folks tackle your problem and help you. Without this effort from you, we a
re
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "akira-888" <akira888@.discussions.microsoft.com> wrote in message
> news:E8DEDB72-A958-455A-8458-85FF94B0B7AD@.microsoft.com...
>
>
Monday, March 12, 2012
query output format
Hey all, another question with the same SQL line:
AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||
If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:
***
Smith, H
Johnson, P
***
Amid, D
Is it possible to strip the comma from the Supervisor name records so that it will return:
***
Smith H
Johnson P
***
Amid D
??
Any help would be appreciated!
DeeOriginally posted by psdcc
Hey all, another question with the same SQL line:
AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||
If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:
***
Smith, H
Johnson, P
***
Amid, D
Is it possible to strip the comma from the Supervisor name records so that it will return:
***
Smith H
Johnson P
***
Amid D
??
Any help would be appreciated!
Dee
Hi dee,
Use oracle replace function for the final output string
ex : select replace(outputtext, ',',' ')
regards
AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||
If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:
***
Smith, H
Johnson, P
***
Amid, D
Is it possible to strip the comma from the Supervisor name records so that it will return:
***
Smith H
Johnson P
***
Amid D
??
Any help would be appreciated!
DeeOriginally posted by psdcc
Hey all, another question with the same SQL line:
AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||
If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:
***
Smith, H
Johnson, P
***
Amid, D
Is it possible to strip the comma from the Supervisor name records so that it will return:
***
Smith H
Johnson P
***
Amid D
??
Any help would be appreciated!
Dee
Hi dee,
Use oracle replace function for the final output string
ex : select replace(outputtext, ',',' ')
regards
Wednesday, March 7, 2012
Query on syslogins table
Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgdsThe hasaccess column of the syslogins view is build based on the xstatus column of the sysxlogins table. If you will read carefully the design of thw view you will find the answer.|||Need help with correct SQL string rather than reference as gather fm Books online that hasaccess column is integer
Quote
hasaccess int -- 1, if login has been granted access to the server.
Unquote
hence do not still understand why it is not working ?|||Have you tried this?
select name,hasaccess from master..syslogins where hasaccess=1
I guess problem is QA - just select results in text not in grid.|||select * from sysxlogins where (xstatus&2) = 2
select * from sysxlogins where (xstatus&2) <> 2|||Trust also that's the case
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgdsThe hasaccess column of the syslogins view is build based on the xstatus column of the sysxlogins table. If you will read carefully the design of thw view you will find the answer.|||Need help with correct SQL string rather than reference as gather fm Books online that hasaccess column is integer
Quote
hasaccess int -- 1, if login has been granted access to the server.
Unquote
hence do not still understand why it is not working ?|||Have you tried this?
select name,hasaccess from master..syslogins where hasaccess=1
I guess problem is QA - just select results in text not in grid.|||select * from sysxlogins where (xstatus&2) = 2
select * from sysxlogins where (xstatus&2) <> 2|||Trust also that's the case
query on primary key field
Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
Andrew
Yes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
Andrew
Yes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
query on primary key field
Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
query on primary key field
Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would b
e
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this databas
e
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records i
n
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_t
id
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would b
e
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this databas
e
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records i
n
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_t
id
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew
Query on large record
First thing I am new to write a query. I have two tables that I need
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:
>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:
> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:
>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:
> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>
Subscribe to:
Posts (Atom)