Adding a new table to a complex join statement

All help appreciated.

I currently use the following select statement as a custom query.

SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)

Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].

I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards

here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql

Adding a day to a date.

Pls help if you can. I have two dates, date1 and date2.
Basically I want to set date2 = date1 + 1 day for a range of dates
that I can select out.

Is there any way to do this?"Chachu" <cpatel1@.gmail.com> wrote in message
news:d7c4c47b.0410050757.6d4bc3a1@.posting.google.c om...
> Pls help if you can. I have two dates, date1 and date2.
> Basically I want to set date2 = date1 + 1 day for a range of dates
> that I can select out.
> Is there any way to do this?

See DATEADD in Books Online:

update dbo.MyTable
set date2 = dateadd(dd, 1, date1)
where ...

Simon

adding a counter to a select query?

I am using SQL Server 2000 v8
Is there a way to add a counter to SELECT statement without writing to
tables
Ultimately I am trying to get a dataset from a SQL statement split into
blocks of 80000 rowsSQL Server 2005 has a function
row_number()
which does just that.
There was a thread somewhere about doing this in 2000 but the solution
looked quite complex.
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Do a search in this forum on "Rank" and you will find several examples.
This article also has some pointers...
http://www.aspfaq.com/show.asp?id=2427
Also check out this article on paging...
http://www.aspfaq.com/show.asp?id=2120
However, I dont think either solution is intended for that volume of data.
You will have to see how they perform.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||When I need a counter, I create a temp or variable table.
CREATE TABLE #mytemp ( entryid int IDENTITY (1,1), UserName varchar(12)
Insert into #mytemp (UserName)
Select UserName from dbo.Users order by UserName
Select entryid, UserName from #mytemp
DROP TABLE #mytemp
That gives me a counter.
2005 has the feature. the above is for 2000 pretty much.
Be careful, you don't want to do this for 1,000,000 rows. Use some common
sense.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Thanks guys for pointing me in the right direction
I have managed to solve the problem by creating a table variable using
the entryid sloan suggested
Cheers!
Kay

add_months function

Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
BethOriginally posted by elisabeth
Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
Beth
Never use a character string literal like '01-JUL-2003' where a DATE is required. Instead, use TO_DATE with an explicit format mask to properly convert it to a date:

TO_DATE('01-JUL-2003','DD-MON-YYYY')

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add values with select

Hi

Thnks for the time

I need to insert some values and select a value from another table to insert

insertinto products values(10,'proname','desc',(select modelfrom Products))

How can this be done.

Try:

insert into products(c1, ..., cn)

select 10, 'proname', 'desc', model

from dbo.products

go

AMB

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

you can do it the other way around

Code Snippet

insertinto products

select 10,'proname','desc', Model

from Products

|||

Hi

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Put the result of the "select" statement into a variable and use the variable.

declare @.model varchar(25)

set @.modele = (select model from products where producti = @.productid)

insert into products values(10, 'proname', 'desc', @.model)

go

AMB

add seconds to time.

Hello how would i add 5 seconds to this value in a select statement?
2005-02-16 04:12:44.000Use DATEADD function.
declare @.d datetime
set @.d = '2005-02-16 04:12:44.000'
select dateadd(second, 5, @.d)
go
AMB
"Fab" wrote:

> Hello how would i add 5 seconds to this value in a select statement?
> 2005-02-16 04:12:44.000
>
>

Add data file to filegroup

I tried to add new file to new filegroup:
Created a new database in ER, successful.
Then, right click this new database, select propertied.
Goto Data Files tab, add a new data file at the second row and select a new
Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
the only option PRIMARY.
Alan
Here's a way I am doing
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uM8cNRqqEHA.3252@.TK2MSFTNGP14.phx.gbl...
> I tried to add new file to new filegroup:
> Created a new database in ER, successful.
> Then, right click this new database, select propertied.
> Goto Data Files tab, add a new data file at the second row and select a
new
> Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
> the only option PRIMARY.
>

Add data file to filegroup

I tried to add new file to new filegroup:
Created a new database in ER, successful.
Then, right click this new database, select propertied.
Goto Data Files tab, add a new data file at the second row and select a new
Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
the only option PRIMARY.Alan
Here's a way I am doing
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uM8cNRqqEHA.3252@.TK2MSFTNGP14.phx.gbl...
> I tried to add new file to new filegroup:
> Created a new database in ER, successful.
> Then, right click this new database, select propertied.
> Goto Data Files tab, add a new data file at the second row and select a
new
> Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
> the only option PRIMARY.
>

Add consecutive Id in Insert mode

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
YossiCould you set your ID attribute to an IDENTITY and let the system sort it out?|||Originally posted by Paul Young
Could you set your ID attribute to an IDENTITY and let the system sort it out?
Thanks for your replay.
The Id column has a meaning.
Not every time I will use the Insert routine the Id should get the consecutive value. Thats why i need to know the current Id and from that value to work on. with Identity the values will raise up to the roof.
Yours
Yossi|||Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.

I will work on it....

Thanks a bunch mate.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.
Hi Paul,
I have tried that and I have the following problems:
1.Product_Num is a barcode string, min function cannot help us here.
2.ID_Column can be with no value at first (when the table is empty there is no value) but it cannot be nulled because its the PK so I got an Error trying to draw the Max value. (i can deal with that).

The main problem is 1.
Can you help with that?
Thanks|||Sorry,

1. Yes, min will work with strings. You will need to adjust the local variable datatypes to match your table attributes. Of course if ProductNum is not unique this approach probably wont work well but then I suspect you will have other problems as well. if this is still giving you fits, post the ddl from the Product and LanTable tables.

2. The quick fix on the ID_Column is to test for a null i.e. isnull(max(ID_COLUMN) + 1,1)|||Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.|||Originally posted by Paul Young
Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.

I will do that.
cheers|||I am glad to see that you are following Paul's advice. While I was reading your post, the description was a primary key field that needed to be incremented - and I was curious why you said you did not want to use IDENTITY (I could only think of the gaps as Paul mentioned - as a down side). Anyway, you changed your mind - so good luck.

Add connection error: 40

Hi i just reinstalled windows, and installed visual studio .net 2005 and the Sql server.

But when im trying to add connection, select my computer at Server

name, type a databasename then pressing OK i get this message:

An error has occured while estabilishing a connection to the server.

When connection to SQL server 2005, this faliure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections. (provider: Names pipes provider, error: 40 - Could not

open a connection to SQL Server)

Does anyone know what todo?

hermanTry this: open the SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, highlight protocols for MSSQLSERVER and enable TCIP/IP.

Mike

Add column with fixed number of values (text) to the select statement

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

I found on the net something to start with

Adding Columns in SQL SELECT statements by Andy Kramek http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

SELECT *, CAST( 0 AS INT) AS newint FROM sample

but it just add a column with value 0 to the set of results.

|||

What is the version you are using... (2000/2005) Give some sample data & required output..

|||

I'm using ver. 2005. The select is very simple concerns the tables with historical rates for curves.

So there are four columns:

1) Date

2) Name of the curve

3) DF

4) Implied rate

there are 17 rows of data

and I need to add column with fixed tenors

O/N

T/N

S/N

1W

2W

1M

2M

3M

4M

5M

6M

7M

8M

9M

10M

11M

1Y

|||

Ok.. do you want the result as bellow,

1) Date

2) Name of the curve

3) DF

4) Implied rate

5)O/N

6)T/N

7)S/N

8)1W

9)2W

10)1M

..

..

17)IY

if yes what data you want to display on this 17 columns

|||

Your questions not clear properly. But I think this will help you,

USE PUBS
Go

SELECT COUNT(*) row_num, A.au_lname
FROM authors A, authors B
WHERE A.au_lname > B.au_lname
GROUP BY A.au_lname
ORDER BY COUNT(*)

|||

I want results to look like this:

Tenor / Date / Name of the curve/ DF / Implied rate

O/N 1/08/07 EUR_CURVE 0.9999987 4.020000

T/N 1/08/07 EUR_CURVE . .

S/N 1/08/07 EUR_CURVE . .

1W 1/08/07 EUR_CURVE . .

2W 1/08/07 EUR_CURVE . .

1M 1/08/07 EUR_CURVE . .

.

.

.

1Y 1/08/07 EUR_CURVE . .

tks

Add Cases to Select Statment

I need to add some cases to the select statment for cpeorderstatus:

Here is my Select statement:

"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"

I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

This is really unclear. It looks like you're selecting

O.STATUS AS CPEORDERSTATUS

but you're not doing anything with it other than returning it. Why not simply adding 5 different values to whatever inserting into the orders table?

Add auto number field to view

I have a simple view something like "SELECT * from <table_name> order
by field1,field2,field3
I have a program that needs to read these values in by the order I have
ordered them in the database however it needs an incremental numeric
value to do so. Is there any way to add to the view, a field that after
the order by is done, that places a numeric incremental value in
another virtual or real column?
Thanks.
JRhttp://www.aspfaq.com/2427
"JR" <jriker1@.yahoo.com> wrote in message
news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...
>I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
>|||JR wrote:
> I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
on 2005, use row_number()|||Using SQL Server 2000.
Alexander Kuznetsov wrote:
> JR wrote:
> on 2005, use row_number()|||I was looking at the rank option however with that it seems to want you
to define each and every column in your view in the group by clause.
Aaron Bertrand [SQL Server MVP] wrote:
> http://www.aspfaq.com/2427
>
>
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...|||>I was looking at the rank option however with that it seems to want you
> to define each and every column in your view in the group by clause.
Which you should be doing anyway; don't be lazy, it doesn't pay off in the
long run!
http://www.aspfaq.com/2096|||OK, I'll give it a try however I have over 50 columns so kind of a
pain.
Aaron Bertrand [SQL Server MVP] wrote:
> Which you should be doing anyway; don't be lazy, it doesn't pay off in the
> long run!
> http://www.aspfaq.com/2096|||> OK, I'll give it a try however I have over 50 columns so kind of a
> pain.
CREATE VIEW tmp_vw_foo
AS
SELECT * FROM SomeTable
GO
SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tmp_vw_foo'
ORDER BY ORDINAL_POSITION
The most work you're going to do, aside from copy and paste, is deleting the
last comma.
Yep, that's a real pain. :-)|||Sweet. Thanks for that Aaron.
Aaron Bertrand [SQL Server MVP] wrote:
> CREATE VIEW tmp_vw_foo
> AS
> SELECT * FROM SomeTable
> GO
> SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'tmp_vw_foo'
> ORDER BY ORDINAL_POSITION
> The most work you're going to do, aside from copy and paste, is deleting t
he
> last comma.
> Yep, that's a real pain. :-)

add a Primary Key (or something like that) to a View

Hi,
I have a View like this in my SQL Server 2000:
CREATE VIEW vw_oas_linkhead
AS
SELECT *
FROM oas_linkhead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION
The problem is: When, inside an Access-applciation, I put a link to this
View, I can't delete records from the View unless I have a Primary Key
defined on the View.
I can define that Primary Key in Access, but when I refresh the Linked Table
(View) the Primary Key disspaears. This doesn't happen when the Table itself
has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
possiblity to put a Primary Key on a View in Sql Server (I thought this
isn't possible?) or to kind of simulate this on another way (with a check
constraint, ... ?)?
Does anybody has any idea?
Thanks a lot!
PieterI would assume that Access would pick up the underlying table's primary key definition. Do you have
such?
You can't define a PK on a view as the view doesn't store any data in itself. You can, in some
cases, make the view with SCHEMABINING and create a unique index on the view. The question is, of
course, whether Access would pick up on that. But that is a question for the Access experts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a View like this in my SQL Server 2000:
> CREATE VIEW vw_oas_linkhead
> AS
> SELECT *
> FROM oas_linkhead
> WHERE (cmpcode = SUSER_SNAME())
> WITH CHECK OPTION
> The problem is: When, inside an Access-applciation, I put a link to this
> View, I can't delete records from the View unless I have a Primary Key
> defined on the View.
> I can define that Primary Key in Access, but when I refresh the Linked Table
> (View) the Primary Key disspaears. This doesn't happen when the Table itself
> has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
> possiblity to put a Primary Key on a View in Sql Server (I thought this
> isn't possible?) or to kind of simulate this on another way (with a check
> constraint, ... ?)?
> Does anybody has any idea?
> Thanks a lot!
> Pieter
>|||Well, the problem is that the underlying table doesn't have a Primary Key,
but in access it should.
I'm not allowed to change the udnerlying table, so I should put something on
theView...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> I would assume that Access would pick up the underlying table's primary
key definition. Do you have
> such?
> You can't define a PK on a view as the view doesn't store any data in
itself. You can, in some
> cases, make the view with SCHEMABINING and create a unique index on the
view. The question is, of
> course, whether Access would pick up on that. But that is a question for
the Access experts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a View like this in my SQL Server 2000:
> > CREATE VIEW vw_oas_linkhead
> > AS
> > SELECT *
> > FROM oas_linkhead
> > WHERE (cmpcode = SUSER_SNAME())
> > WITH CHECK OPTION
> >
> > The problem is: When, inside an Access-applciation, I put a link to this
> > View, I can't delete records from the View unless I have a Primary Key
> > defined on the View.
> >
> > I can define that Primary Key in Access, but when I refresh the Linked
Table
> > (View) the Primary Key disspaears. This doesn't happen when the Table
itself
> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
a
> > possiblity to put a Primary Key on a View in Sql Server (I thought this
> > isn't possible?) or to kind of simulate this on another way (with a
check
> > constraint, ... ?)?
> >
> > Does anybody has any idea?
> >
> > Thanks a lot!
> >
> > Pieter
> >
> >
>|||The option for "putting something on the view" you find in my prior post. But the bigger question is
why the table doesn't have a PK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Well, the problem is that the underlying table doesn't have a Primary Key,
> but in access it should.
> I'm not allowed to change the udnerlying table, so I should put something on
> theView...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> I would assume that Access would pick up the underlying table's primary
> key definition. Do you have
>> such?
>> You can't define a PK on a view as the view doesn't store any data in
> itself. You can, in some
>> cases, make the view with SCHEMABINING and create a unique index on the
> view. The question is, of
>> course, whether Access would pick up on that. But that is a question for
> the Access experts.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a View like this in my SQL Server 2000:
>> > CREATE VIEW vw_oas_linkhead
>> > AS
>> > SELECT *
>> > FROM oas_linkhead
>> > WHERE (cmpcode = SUSER_SNAME())
>> > WITH CHECK OPTION
>> >
>> > The problem is: When, inside an Access-applciation, I put a link to this
>> > View, I can't delete records from the View unless I have a Primary Key
>> > defined on the View.
>> >
>> > I can define that Primary Key in Access, but when I refresh the Linked
> Table
>> > (View) the Primary Key disspaears. This doesn't happen when the Table
> itself
>> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
> a
>> > possiblity to put a Primary Key on a View in Sql Server (I thought this
>> > isn't possible?) or to kind of simulate this on another way (with a
> check
>> > constraint, ... ?)?
>> >
>> > Does anybody has any idea?
>> >
>> > Thanks a lot!
>> >
>> > Pieter
>> >
>> >
>>
>|||I don't know why it doesn't have a Primary Key: It's a table of a big
Accountant Software, so I can't change anything to the table... :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> The option for "putting something on the view" you find in my prior post.
But the bigger question is
> why the table doesn't have a PK...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Well, the problem is that the underlying table doesn't have a Primary
Key,
> > but in access it should.
> > I'm not allowed to change the udnerlying table, so I should put
something on
> > theView...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> I would assume that Access would pick up the underlying table's primary
> > key definition. Do you have
> >> such?
> >> You can't define a PK on a view as the view doesn't store any data in
> > itself. You can, in some
> >> cases, make the view with SCHEMABINING and create a unique index on the
> > view. The question is, of
> >> course, whether Access would pick up on that. But that is a question
for
> > the Access experts.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have a View like this in my SQL Server 2000:
> >> > CREATE VIEW vw_oas_linkhead
> >> > AS
> >> > SELECT *
> >> > FROM oas_linkhead
> >> > WHERE (cmpcode = SUSER_SNAME())
> >> > WITH CHECK OPTION
> >> >
> >> > The problem is: When, inside an Access-applciation, I put a link to
this
> >> > View, I can't delete records from the View unless I have a Primary
Key
> >> > defined on the View.
> >> >
> >> > I can define that Primary Key in Access, but when I refresh the
Linked
> > Table
> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
> > itself
> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
is
> > a
> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
this
> >> > isn't possible?) or to kind of simulate this on another way (with a
> > check
> >> > constraint, ... ?)?
> >> >
> >> > Does anybody has any idea?
> >> >
> >> > Thanks a lot!
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||I see :-(. I suggest you post this to an Access forum to see whether you can define in Access what
column define uniqueness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I don't know why it doesn't have a Primary Key: It's a table of a big
> Accountant Software, so I can't change anything to the table... :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
>> The option for "putting something on the view" you find in my prior post.
> But the bigger question is
>> why the table doesn't have a PK...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
>> > Well, the problem is that the underlying table doesn't have a Primary
> Key,
>> > but in access it should.
>> > I'm not allowed to change the udnerlying table, so I should put
> something on
>> > theView...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> >> I would assume that Access would pick up the underlying table's primary
>> > key definition. Do you have
>> >> such?
>> >> You can't define a PK on a view as the view doesn't store any data in
>> > itself. You can, in some
>> >> cases, make the view with SCHEMABINING and create a unique index on the
>> > view. The question is, of
>> >> course, whether Access would pick up on that. But that is a question
> for
>> > the Access experts.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I have a View like this in my SQL Server 2000:
>> >> > CREATE VIEW vw_oas_linkhead
>> >> > AS
>> >> > SELECT *
>> >> > FROM oas_linkhead
>> >> > WHERE (cmpcode = SUSER_SNAME())
>> >> > WITH CHECK OPTION
>> >> >
>> >> > The problem is: When, inside an Access-applciation, I put a link to
> this
>> >> > View, I can't delete records from the View unless I have a Primary
> Key
>> >> > defined on the View.
>> >> >
>> >> > I can define that Primary Key in Access, but when I refresh the
> Linked
>> > Table
>> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
>> > itself
>> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
> is
>> > a
>> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> this
>> >> > isn't possible?) or to kind of simulate this on another way (with a
>> > check
>> >> > constraint, ... ?)?
>> >> >
>> >> > Does anybody has any idea?
>> >> >
>> >> > Thanks a lot!
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Well I did, and I just got the answer!
It shoudl have been the nicest oslution if I could implement it on the view,
but now I have some VBA that puts the index on the linked view after I
refreshed everything... It seemsto work fine.
Thanks a lot for the effort!
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOSsf5A6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> I see :-(. I suggest you post this to an Access forum to see whether you
can define in Access what
> column define uniqueness.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I don't know why it doesn't have a Primary Key: It's a table of a big
> > Accountant Software, so I can't change anything to the table... :-)
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> >> The option for "putting something on the view" you find in my prior
post.
> > But the bigger question is
> >> why the table doesn't have a PK...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> >> > Well, the problem is that the underlying table doesn't have a Primary
> > Key,
> >> > but in access it should.
> >> > I'm not allowed to change the udnerlying table, so I should put
> > something on
> >> > theView...
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> >> I would assume that Access would pick up the underlying table's
primary
> >> > key definition. Do you have
> >> >> such?
> >> >> You can't define a PK on a view as the view doesn't store any data
in
> >> > itself. You can, in some
> >> >> cases, make the view with SCHEMABINING and create a unique index on
the
> >> > view. The question is, of
> >> >> course, whether Access would pick up on that. But that is a question
> > for
> >> > the Access experts.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I have a View like this in my SQL Server 2000:
> >> >> > CREATE VIEW vw_oas_linkhead
> >> >> > AS
> >> >> > SELECT *
> >> >> > FROM oas_linkhead
> >> >> > WHERE (cmpcode = SUSER_SNAME())
> >> >> > WITH CHECK OPTION
> >> >> >
> >> >> > The problem is: When, inside an Access-applciation, I put a link
to
> > this
> >> >> > View, I can't delete records from the View unless I have a Primary
> > Key
> >> >> > defined on the View.
> >> >> >
> >> >> > I can define that Primary Key in Access, but when I refresh the
> > Linked
> >> > Table
> >> >> > (View) the Primary Key disspaears. This doesn't happen when the
Table
> >> > itself
> >> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if
there
> > is
> >> > a
> >> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> > this
> >> >> > isn't possible?) or to kind of simulate this on another way (with
a
> >> > check
> >> >> > constraint, ... ?)?
> >> >> >
> >> >> > Does anybody has any idea?
> >> >> >
> >> >> > Thanks a lot!
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Add a constant to a select result

Hi,
I have an statement select a, b, c from table with returns the
the contents of the columns a, b, c. I now would like to add a constant to
the result. What does a statement look like to return
content of a, b, c, 1
with 1 is my constant?
Thanks for any advice in advance
Pete
Pete Smith"Pete Smith" <msdn@.nospam.com> wrote in message
news:Oku5O9eUGHA.1672@.tk2msftngp13.phx.gbl...
> Hi,
> I have an statement select a, b, c from table with returns the
> the contents of the columns a, b, c. I now would like to add a constant to
> the result. What does a statement look like to return
> content of a, b, c, 1
> with 1 is my constant?
> Thanks for any advice in advance
> Pete
> --
> Pete Smith
>
SELECT a, b, c, 1 AS d
FROM tbl ;
d is a name for the column containing the constant. Although SQL doesn't
require it, it's always better to give every column a name.
Better still, be explicit about the datatype for your new column. Don't rely
on the server to guess what you intended because it may not always guess
right:
SELECT a, b, c, CAST(1 AS INTEGER) AS d
FROM tbl ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,

> SELECT a, b, c, CAST(1 AS INTEGER) AS d
> FROM tbl ;
Thanks, great help. Works excellent!
Thanks again!
Pete

ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
LeilaOn May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understand
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegroups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>> Hi,
>> I have the following query:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand
>> that about my query. What does it show?
>> Thanks in advance,
>> Leila
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand that about my query. What does it show?
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> That's ok! But I'd like to know the meaning of these two items. For
>> example I cannot understand this from BOL: A rebind means that one or
>> more of the correlated parameters of the join changed and the inner side
>> must be reevaluated. A rewind means that none of the correlated
>> parameters changed and the prior inner result set may be reused
>> How does the "correlated parameters of the join" can change during the
>> execution?
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> writes:
>I will have to admit that I'm quite much in the dark myself. It would
>help to have a query where Actual Rebinds/Rewinds are non-zero (save for
>sorting operations then). I've been trying to find such a query, but
>since I don't know what I'm looking for, I have not been successful.
OK, so here is an example where Actual Rewinds and Actual Rebinds are > 0.
I can't say that I understand exactly what is going on, but the
query has a Lazy Spool operator, and if you read about the Lazy Spool
operator in Books Online, you will see that it talks about rewinds and
rebinds. The query plan is in any case a disaster.
To try this, you first need to download the script for the Northgale
database, an inflated version of Northwind from
http://www.sommarskog.se/dynsearch/Northgale.sql.
The script first sets up some stuff, and then issues two query. It is
the first, slow, query which has the rewinds and the rebinds.
-- This is the setup.
SELECT *, GUID = newid() INTO Orders FROM Northgale..Orders
go
CREATE UNIQUE CLUSTERED INDEX clust ON Orders (CustomerID, OrderDate, EmployeeID, GUID)
ALTER TABLE Orders ADD CONSTRAINT pk_orders PRIMARY KEY (OrderID)
go
CREATE TABLE Orderlinks
(prevorderid int NOT NULL REFERENCES Orders(OrderID),
succorderid int NOT NULL REFERENCES Orders(OrderID),
filler char(16) NOT NULL DEFAULT ' ',
PRIMARY KEY (prevorderid, succorderid)
)
go
CREATE INDEX succorderid_ix ON Orderlinks(succorderid)
go
INSERT Orderlinks (prevorderid, succorderid)
SELECT a.OrderID, b.OrderID
FROM (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID ASC)
FROM Orders) AS a
JOIN (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID DESC)
FROM Orders) AS b ON a.rn = b.rn
WHERE a.rn <= 1000
go
UPDATE STATISTICS Orderlinks WITH FULLSCAN
go
DROP TABLE #temp1
CREATE TABLE #temp1
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
CREATE TABLE #temp2
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
go
-- Here starts the actual test.
SET STATISTICS IO ON
go
SELECT getdate()
go
-- Slow query.
INSERT #temp1(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(*)
FROM Orders O
WHERE EXISTS
(SELECT *
FROM Orderlinks L
WHERE O.OrderID IN (L.prevorderid, succorderid))
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
-- Logically the same query, but fast.
INSERT #temp2(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(DISTINCT O.OrderID)
FROM Orders O
JOIN Orderlinks L ON O.OrderID IN (L.prevorderid, succorderid)
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
SET STATISTICS IO OFF
Erland Sommarskog, Stockholm, esquel@.sommarskog.se