select
'[Cust Code]' ColName
,RowId
from
Temp_Upload_Table
where
[cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
The code above is giving an error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The [cust code] column is float .
The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...
Is this a miracle, a bug or is there something I need to know ?I can't seem to replicate the problem over here. Here is what I am using:
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
Am I missing something?|||dont know what is happening .. the code runs good on your tables ... gives an error on mine|||There was a thread a little while ago, maybe February that I remember had a similar problem. Danged if I can find it, now, but it may not apply. In the thread, it was found that some of the older notations for numbers (like 2.0e05 for 200,000) were making life miserable for the guy with the question. I think Pat Phelan may have been in on that thread, but I can't be too sure. That was a few too many beers ago.|||Cut and paste this, and tell me it doesn't work...
USE Northwind
GO
SET NOCOUNT ON
create table temp_upload_table
([cust code] float,
rowid int)
create table KNVV_View
(KUNNR varchar(10))
insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')
insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
GO|||Found the thread I was after. Here it is:
http://www.dbforums.com/t993650.html
I think Pat was up a bit late last night (or early this morning), so he may not be able to reply for a while.|||Added that link to my SQL Server favorites folder...|||Huh?!?! What, who me?
Did anyone check to see if 1 = IsNumeric([cust code])?
-PatP|||He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)|||Yes I did pat ...
CREATE TABLE [KNVV] (
[VTWEG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKORG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKGRP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKBUR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPART] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR5] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR4] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KUNNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KDGRP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BZIRK] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create view KNVV_View as
select distinct VKGRP
,VKBUR
,SPART
,MANDT
,KVGR5
,KVGR4
,KVGR2
,KVGR1
,KUNNR
,KDGRP
,BZIRK
from KNVV
where BZIRK <> '999999'
and kvgr1 <> '999'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [Temp_Upload_Table] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[AOP-Value] [float] NULL ,
[AOP-Volume] [float] NULL ,
[Cust Code] [float] NULL
) ON [PRIMARY]
GO
Brett .. bcp in the data and tell me this works ;)
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)|||And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ... and hey this works on the same data ...
select * from Temp_Upload_Table where convert(varchar(20),Cust_Code) not in ( select convert(float,(select KUNNR where isnumeric(kunnr) = 1)) from KNVV_View )
Any explanations ?|||Hmm. Having a bit of trouble BCP-ing the temp_upload_table file in. I must be missing something obvious.
bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t\t -Smyserver
is giving me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
anyone else getting this, or is this because of the big bowl of stupid flakes I had this morning?|||Checked that out ... try using EM ... import/export wizard ;D|||bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver
?|||Enterprise Manager? Pfft! I can't stand Enterprise Manager. It worked, though. ;-) And I am now getting the error. See if I get any results, though....|||OK, I have a piece of this figured out. It looks from the query plan that when you use "not in", the optimizer is checking for nulls. This check is not necessary when you are only interested in "in". I think that the implicit comparison for null is what is getting you. This apparently works:
SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in (
select isnull (Convert(Float, KUNNR), 0) from KNVV_View
where isnumeric(kunnr) = 1
)|||Are you talking about the nulls in [cust code] column|||I think the optimizer is more worried about nulls returned by the subquery. I think if there is a null in the not in list, SQL Server may get confused. Something about that three way logic. This may be why the optimizer if pulling the possibility aside in the query plan.
What confuses me, though, is why it is not doing that extra "side comparison" for queries that simply use "IN (select...)". It may be that in a query with "in (...)", the nulls in the list are simply tossed, since null compared with anything returns nothing. You, however have a "not in (...)" in your query, so the ghost of DeMorgan may be haunting you.
Make any better sense?|||The funny thing here is that KUNNR in KNVV_View table does not have nulls ... so why is that happening ...|||Probably the optimizer does not want to risk it, so it has to check.
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Monday, March 26, 2012
Minor incompatibility between 2000 and 2005
Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)Hello Sergey Lipnevich sergey.at.optimaltec.com,
> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).
> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!
> These are both known features. No, really. Features. :)
Known... Yes. Features... I don't know :-).
Sergey.|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)Hello Sergey Lipnevich sergey.at.optimaltec.com,
> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).
> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!
> These are both known features. No, really. Features. :)
Known... Yes. Features... I don't know :-).
Sergey.|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.
Minor incompatibility between 2000 and 2005
Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)
Hello Sergey Lipnevich sergey.at.optimaltec.com,
> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).
> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!
> These are both known features. No, really. Features.
Known... Yes. Features... I don't know :-).
Sergey.
|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.
|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)
Hello Sergey Lipnevich sergey.at.optimaltec.com,
> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).
> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!
> These are both known features. No, really. Features.
Known... Yes. Features... I don't know :-).
Sergey.
|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.
|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.
Friday, March 23, 2012
minimum speed to coonectSQL Server over inernet
What the minimal speed is required to connect to SQL Server over internet to
give a good connection?
I mean where i can open forms, select item from the list boxes, generate
reports, inputing data...
Thanks
José
no technical minimum...the limitation is more in your application and where
it resides. Describe what you are using to connect to SQL Server
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jos" <Jos@.discussions.microsoft.com> wrote in message
news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
> What the minimal speed is required to connect to SQL Server over internet
> to
> give a good connection?
> I mean where i can open forms, select item from the list boxes, generate
> reports, inputing data...
> Thanks
> Jos
>
|||I'm testing an aplication.
I tested up 128kb/s is acceptable, I test dial connection, is too slow...
For each form, I don't have many information. The data in table, is a table
with 70 records and 20 columns maximum...
thanks
"Kevin3NF" wrote:
> no technical minimum...the limitation is more in your application and where
> it resides. Describe what you are using to connect to SQL Server
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "José" <Jos@.discussions.microsoft.com> wrote in message
> news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
>
>
|||Access? .Net? Powerbuilder?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...[vbcol=seagreen]
> I'm testing an aplication.
> I tested up 128kb/s is acceptable, I test dial connection, is too slow...
> For each form, I don't have many information. The data in table, is a
> table
> with 70 records and 20 columns maximum...
> thanks
> "Kevin3NF" wrote:
|||I'm connecting by MS Access 2003 (ADP)
"Kevin3NF" wrote:
> Access? .Net? Powerbuilder?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
> news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...
>
>
give a good connection?
I mean where i can open forms, select item from the list boxes, generate
reports, inputing data...
Thanks
José
no technical minimum...the limitation is more in your application and where
it resides. Describe what you are using to connect to SQL Server
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jos" <Jos@.discussions.microsoft.com> wrote in message
news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
> What the minimal speed is required to connect to SQL Server over internet
> to
> give a good connection?
> I mean where i can open forms, select item from the list boxes, generate
> reports, inputing data...
> Thanks
> Jos
>
|||I'm testing an aplication.
I tested up 128kb/s is acceptable, I test dial connection, is too slow...
For each form, I don't have many information. The data in table, is a table
with 70 records and 20 columns maximum...
thanks
"Kevin3NF" wrote:
> no technical minimum...the limitation is more in your application and where
> it resides. Describe what you are using to connect to SQL Server
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "José" <Jos@.discussions.microsoft.com> wrote in message
> news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
>
>
|||Access? .Net? Powerbuilder?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...[vbcol=seagreen]
> I'm testing an aplication.
> I tested up 128kb/s is acceptable, I test dial connection, is too slow...
> For each form, I don't have many information. The data in table, is a
> table
> with 70 records and 20 columns maximum...
> thanks
> "Kevin3NF" wrote:
|||I'm connecting by MS Access 2003 (ADP)
"Kevin3NF" wrote:
> Access? .Net? Powerbuilder?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
> news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...
>
>
minimum speed to coonectSQL Server over inernet
What the minimal speed is required to connect to SQL Server over internet to
give a good connection?
I mean where i can open forms, select item from the list boxes, generate
reports, inputing data...
Thanks
Joséno technical minimum...the limitation is more in your application and where
it resides. Describe what you are using to connect to SQL Server
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jos" <Jos@.discussions.microsoft.com> wrote in message
news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
> What the minimal speed is required to connect to SQL Server over internet
> to
> give a good connection?
> I mean where i can open forms, select item from the list boxes, generate
> reports, inputing data...
> Thanks
> Jos
>|||I'm testing an aplication.
I tested up 128kb/s is acceptable, I test dial connection, is too slow...
For each form, I don't have many information. The data in table, is a table
with 70 records and 20 columns maximum...
thanks
"Kevin3NF" wrote:
> no technical minimum...the limitation is more in your application and wher
e
> it resides. Describe what you are using to connect to SQL Server
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "José" <Jos@.discussions.microsoft.com> wrote in message
> news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
>
>|||Access? .Net? Powerbuilder?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...[vbcol=seagreen]
> I'm testing an aplication.
> I tested up 128kb/s is acceptable, I test dial connection, is too slow...
> For each form, I don't have many information. The data in table, is a
> table
> with 70 records and 20 columns maximum...
> thanks
> "Kevin3NF" wrote:
>|||I'm connecting by MS Access 2003 (ADP)
"Kevin3NF" wrote:
> Access? .Net? Powerbuilder?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
> news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...
>
>
give a good connection?
I mean where i can open forms, select item from the list boxes, generate
reports, inputing data...
Thanks
Joséno technical minimum...the limitation is more in your application and where
it resides. Describe what you are using to connect to SQL Server
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jos" <Jos@.discussions.microsoft.com> wrote in message
news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
> What the minimal speed is required to connect to SQL Server over internet
> to
> give a good connection?
> I mean where i can open forms, select item from the list boxes, generate
> reports, inputing data...
> Thanks
> Jos
>|||I'm testing an aplication.
I tested up 128kb/s is acceptable, I test dial connection, is too slow...
For each form, I don't have many information. The data in table, is a table
with 70 records and 20 columns maximum...
thanks
"Kevin3NF" wrote:
> no technical minimum...the limitation is more in your application and wher
e
> it resides. Describe what you are using to connect to SQL Server
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "José" <Jos@.discussions.microsoft.com> wrote in message
> news:D42C3EDA-88A8-4261-BB6F-3401FADD2B0F@.microsoft.com...
>
>|||Access? .Net? Powerbuilder?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...[vbcol=seagreen]
> I'm testing an aplication.
> I tested up 128kb/s is acceptable, I test dial connection, is too slow...
> For each form, I don't have many information. The data in table, is a
> table
> with 70 records and 20 columns maximum...
> thanks
> "Kevin3NF" wrote:
>|||I'm connecting by MS Access 2003 (ADP)
"Kevin3NF" wrote:
> Access? .Net? Powerbuilder?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jose Perdigao" <JosePerdigao@.discussions.microsoft.com> wrote in message
> news:552C6722-D866-47A7-976E-295638A6ED6A@.microsoft.com...
>
>
Monday, March 19, 2012
MIN() + MAX() Deadlock ?
Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
Eval
Pleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval
|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
Eval
Pleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval
|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
MIN() + MAX() Deadlock ?
Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId = '1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId => '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId = '1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId => '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
MIN() + MAX() Deadlock ?
Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId =
'1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)
min with a bit
Hi,
I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.
Is there an alternative to my query?
Many thanks,
Chrismin(cast(deleted as int))
"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?
> min(cast(deleted as int))
Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...
Cheers,
Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
> Cheers,
> Chris
Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).
Simon|||How a bit could be (-1) ?
"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buocfi$ub9$1@.ucsnew1.ncl.ac.uk...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
How about .... min(cast(bit as varchar(1))) ?
Pete Brown
Falls Creek
Oz|||When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).
HTH,
Gert-Jan
Not Me wrote:
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1) in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Igor Raytsin" <n&i@.cyberus.ca> wrote in message
news:400fee3f_1@.news.cybersurf.net...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > I'm trying to grab records with a priority over those marked as yes (-1)
> in
> > a certain field.
> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?
> How a bit could be (-1) ?
Ask Bill :o)
Chris|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0401220728.58b967ae@.posting.google.c om...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> > "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> > news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > > operator doesn't work on bits.
> > > > > > Is there an alternative to my query?
> > > min(cast(deleted as int))
> > Thanks for that, I do though get an error when trying it, I guess it's
> > because I'm using an mdb file and linked tables to the sql server... any
> > other ideas? Could create a quick function I guess...
>
> Your question isn't really clear - a bit column can only hold 0,1 or
> NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
> an MSSQL query, then please consider posting the CREATE TABLE
> statement for your table, as well as the exact query that you're
> using, and the output you expect (sample data would also be useful).
Thanks for your help, yes the -1 just seems to be how access likes to
display the info.
The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.
So, a table could show
id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games
and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)
So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).
So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..
Hope you understand the problem!!
Any help is greatly appreciated.
Cheers,
Chris|||Not Me (Not.Me@.faker.fake.fa.ke) writes:
> So, a table could show
> id current job
> #1 yes databases
> #1 no graphics
> #2 no statistics
> #2 no games
> and I would want to return one record for each id#, with a preference of a
> current job (if no current job, any non-current job will do)
> So far I've only managed to do a "select all current jobs union select
> all non-current jobs that don't appear in the current jobs list" The
> problem here is that it becomes very very slow when performing the "jobs
> that don't appear in the current jobs list" (done by where x not in
> (select x from y)).
Here is one way that you may want to try:
DECLARE @.temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)
INSERT @.temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC
SELECT t.id, c.current, t.job
FROM @.temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @.temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id
By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.
I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
min value for a date
Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <= @.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >= @.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
10026823 100 2008-02-08 00:00:00.000
10026824 150 2008-02-08 00:00:00.000
10026824 150 2008-02-09 00:00:00.000
10026823 100 2008-02-10 00:00:00.000
10026823 100 2008-02-11 00:00:00.000
10026823 100 2008-02-13 00:00:00.000
10026823 100 2008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?|||Hi
it seems that rate_id is not needed in the group by
select min(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
order by A.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
select min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
) b ON b.date = c.date AND b.minrate = s.rate
order by b.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>|||On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
> Hi,
> I have the following query which returns
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.valid_=from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date) >=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> order by
> =A0 =A0 =A0 =A0 A.date, minRate
> rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
Hi,
I have re-written your query a bit. You might try this although
untested.
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and R.room_type_id =3D 72 -- Double room
and R.board_type =3D 6 -- Bed and breakfast
and month(R.valid_from_date) <=3D @.month and
year(R.valid_from_date) <=3D
@.year
and month(R.valid_to_date) >=3D @.month and year(R.valid_to_date)
>=3D
@.year
and r.min_nights =3D 1 -- Standard type
and a.date >=3D getDate() -- we dont go back in
time
group by
r.Rate_id, A.date
having min(r.rate) =3D
(select min(s.rate)
from
hotel_acc_rates S
inner join
hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and S.room_type_id =3D 72 -- Double room
and S.board_type =3D 6 -- Bed and breakfast
and month(S.valid_from_date) <=3D @.month and
year(S.valid_from_date) <=3D
@.year
and month(S.valid_to_date) >=3D @.month and year(S.valid_to_date)
>=3D
@.year
and s.min_nights =3D 1 -- Standard type
and b.date >=3D getDate() -- we dont go back in
time
and R.Rate_id =3D S.Rate_id
and A.date =3D B.date
group by
s.Rate_id, B.date
)
order by
A.date, minRate|||On Feb 11, 2:41=A0pm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
>
>
> > Hi,
> > I have the following query which returns
> > select
> > =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> > from
> > =A0 =A0 =A0 =A0 hotel_acc_rates R
> > inner join
> > =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> > where
> > =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> > =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room=
> > =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and= breakfast
> > =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.vali=d_from_date) <=3D
> > @.year
> > =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_=to_date) >=3D
> > @.year
> > =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standar=d type
> > =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- =we dont go back in time
> > group by
> > =A0 =A0 =A0 =A0 r.Rate_id, A.date
> > order by
> > =A0 =A0 =A0 =A0 A.date, minRate
> > rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> > However I want to exclude row 2 as it is the same day but a higher
> > rate. I am working on an extremley old legacy system which allows lots
> > of different rates for the same day to entered which does not help!.
> > The structure cannot be changed so I am stuck with what I have.
> > How can I rewrite my query without creating a temp table?
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and
> year(R.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> having min(r.rate) =3D
> (select min(s.rate)
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates S
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and S.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and S.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(S.valid_from_date) <=3D @.month and
> year(S.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(S.valid_to_date) >=3D @.month and year(S.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and s.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and b.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> =A0 =A0 =A0 =A0 and R.Rate_id =3D S.Rate_id
> =A0 =A0 =A0 =A0 and A.date =3D B.date
> group by
> =A0 =A0 =A0 =A0 s.Rate_id, B.date
> )
> order by
> =A0 =A0 =A0 =A0 A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
=2E...
and A.date =3D B.date
group by
B.date
)
HTH
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <= @.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >= @.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
10026823 100 2008-02-08 00:00:00.000
10026824 150 2008-02-08 00:00:00.000
10026824 150 2008-02-09 00:00:00.000
10026823 100 2008-02-10 00:00:00.000
10026823 100 2008-02-11 00:00:00.000
10026823 100 2008-02-13 00:00:00.000
10026823 100 2008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?|||Hi
it seems that rate_id is not needed in the group by
select min(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
order by A.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
select min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
) b ON b.date = c.date AND b.minrate = s.rate
order by b.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>|||On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
> Hi,
> I have the following query which returns
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.valid_=from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date) >=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> order by
> =A0 =A0 =A0 =A0 A.date, minRate
> rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
Hi,
I have re-written your query a bit. You might try this although
untested.
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and R.room_type_id =3D 72 -- Double room
and R.board_type =3D 6 -- Bed and breakfast
and month(R.valid_from_date) <=3D @.month and
year(R.valid_from_date) <=3D
@.year
and month(R.valid_to_date) >=3D @.month and year(R.valid_to_date)
>=3D
@.year
and r.min_nights =3D 1 -- Standard type
and a.date >=3D getDate() -- we dont go back in
time
group by
r.Rate_id, A.date
having min(r.rate) =3D
(select min(s.rate)
from
hotel_acc_rates S
inner join
hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and S.room_type_id =3D 72 -- Double room
and S.board_type =3D 6 -- Bed and breakfast
and month(S.valid_from_date) <=3D @.month and
year(S.valid_from_date) <=3D
@.year
and month(S.valid_to_date) >=3D @.month and year(S.valid_to_date)
>=3D
@.year
and s.min_nights =3D 1 -- Standard type
and b.date >=3D getDate() -- we dont go back in
time
and R.Rate_id =3D S.Rate_id
and A.date =3D B.date
group by
s.Rate_id, B.date
)
order by
A.date, minRate|||On Feb 11, 2:41=A0pm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
>
>
> > Hi,
> > I have the following query which returns
> > select
> > =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> > from
> > =A0 =A0 =A0 =A0 hotel_acc_rates R
> > inner join
> > =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> > where
> > =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> > =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room=
> > =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and= breakfast
> > =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.vali=d_from_date) <=3D
> > @.year
> > =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_=to_date) >=3D
> > @.year
> > =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standar=d type
> > =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- =we dont go back in time
> > group by
> > =A0 =A0 =A0 =A0 r.Rate_id, A.date
> > order by
> > =A0 =A0 =A0 =A0 A.date, minRate
> > rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> > However I want to exclude row 2 as it is the same day but a higher
> > rate. I am working on an extremley old legacy system which allows lots
> > of different rates for the same day to entered which does not help!.
> > The structure cannot be changed so I am stuck with what I have.
> > How can I rewrite my query without creating a temp table?
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and
> year(R.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> having min(r.rate) =3D
> (select min(s.rate)
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates S
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and S.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and S.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(S.valid_from_date) <=3D @.month and
> year(S.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(S.valid_to_date) >=3D @.month and year(S.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and s.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and b.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> =A0 =A0 =A0 =A0 and R.Rate_id =3D S.Rate_id
> =A0 =A0 =A0 =A0 and A.date =3D B.date
> group by
> =A0 =A0 =A0 =A0 s.Rate_id, B.date
> )
> order by
> =A0 =A0 =A0 =A0 A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
=2E...
and A.date =3D B.date
group by
B.date
)
HTH
Min Function Problem
<%
sql="select MIN(psprice)as askBA from transactions where namecompany='BA'and act='Limit sell order'"
set AskBA=server.createobject("adodb.recordset")
AskBA.open sql,conn,1,3%>
<td height="22" width="41"><div align="center">
<% if not AskBA.eof then
response.write AskBA("askBA")
else
response.write "-"
end if
AskBA.close%>
When there is Data which namecompany=BA and act=Limit sell order in table transactions , it's OK. It shows the minimun price.
But the problem is that when there is no Data which namecompany=BA and act=Limit sell order in table transactions, it should show - But it does not show anything.
I don't know why it does not go to condition Else.The query has returned a row, with NULL for askBA. So EOF is not true, instead you need to test the value of askBA.
sql="select MIN(psprice)as askBA from transactions where namecompany='BA'and act='Limit sell order'"
set AskBA=server.createobject("adodb.recordset")
AskBA.open sql,conn,1,3%>
<td height="22" width="41"><div align="center">
<% if not AskBA.eof then
response.write AskBA("askBA")
else
response.write "-"
end if
AskBA.close%>
When there is Data which namecompany=BA and act=Limit sell order in table transactions , it's OK. It shows the minimun price.
But the problem is that when there is no Data which namecompany=BA and act=Limit sell order in table transactions, it should show - But it does not show anything.
I don't know why it does not go to condition Else.The query has returned a row, with NULL for askBA. So EOF is not true, instead you need to test the value of askBA.
Subscribe to:
Posts (Atom)