Invalid Udate SQL statement DOES NOT cause error... Does anyone know why?

Here's my update statement:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE itemid in
(SELECT itemid FROM Item2)

The thing is: the table Item2 DOES NOT HAVE a field called itemid.
So, I should receive an error, right? Not so.Instead, every single
record in Item1 was updated.

Does anyone know why SQL Serverr does not trown an error?

Thanks guys,

-Silvio SouzaBecause the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.|||The rule for subqueries is that a column name that can't be resolved to
column within the subquery is assumed to reference a column in the outer
query. If in doubt, use the two-part column name including the table
name/alias.

--
David Portas
SQL Server MVP
--|||"no spam" <chuck@.sheckmedia.com> wrote in message news:<vCWhc.71068$Lh2.5553@.bignews1.bellsouth.net>...
> Because the sub query can reference fields from the update. itemid in this
> case will be retrieved from Item1.

I don't think so. SQL certainly doesn't say to itself "Since I can't
find that value in Item2 I'll assume that they must mean the value in
Item1" - that would be catastrophic.

I've just tried this myself, and whilst it didn't give any error, it
didn't update any rows in Item1 either. This makes sense, because
the subquery is simply evaluating to FALSE, so 0 rows are updated in
the main query.|||> I don't think so. SQL certainly doesn't say to itself "Since I can't
> find that value in Item2 I'll assume that they must mean the value in
> Item1" - that would be catastrophic.

The problem isn't to do with *values* it's to do with resolution of *column
names*. Substitute the word "column" for "value" and your statement
describes exactly what SQL does.

Assuming the column Itemid doesn't exist in Item2, the UPDATE statement you
posted is equivalent to:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE Item1.itemid IN
(SELECT Item1.itemid FROM Item2)

As long as there is at least one row in Item2, every row in Item1 should get
updated.

--
David Portas
SQL Server MVP
--|||Any field reference in a sub query will always look for the field internally
first and if not found it will look in the outer query. The reason for this
behaviour is that the sub query can use values from the outer query as
selection criteria, in case statements etc.

This is not a bug, it is by design. By always using table qualifiers in all
sql it will never cause a problem even if the developer mistypes a field
name.
Sloppy SQL (without proper table qualifiers etc) may behave funny as in the
example provided by the OP.

Also, if you look at the execution plan for this and similar queries it will
be more clear why. The optimizer usually turn sub queries like this into
joins.

Invalid object name sysperfinfo

When I execute this statement through ASP.NET

select cntr_value FROM sysperfinfo

I get this error,

System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'.

Any ideas why?

It sounds to me like your connectionstring (the string that points to the database which you are querying) is pointing to a database that does not have a table entitled 'sysperfinfo'|||

To add to the previous post some things have changes about sysperfinfo. Try the links below for more. Hope this helps.

sysperfinfo

In SQL Server 2005,sysperfinfo returns abigint value for thecntr_value column. Modify applications that usesysperfinfo to make sure that they can handle thebigint values of thecntr_value column.

In SQL Server 2005,sysperfinfo is a compatibility view. You should use thesys.dm_os_performance_counters dynamic management view instead.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

http://www.sqlservercentral.com/columnists/jsack/troubleshootingsqlserverwiththesysperfinfotable.asp

|||

Maybe you're in the wrong database, try:

select cntr_value FROM master..sysperfinfo

Invalid Object Name - Grr...

This is what I have. It works fine until I get to the select statement, then it tells me that I have an invalid object name. What am I missing? Thanks!

DECLARE @.SvrName varchar(100)

if @.@.SERVERNAME='pubs' begin
set @.SvrName=pubs.books.isbn
print @.SvrName
end
if @.@.SERVERNAME='MGMFILENET' begin
set @.SvrName=store.books.isbn
print@.SvrName
end

print @.@.SERVERNAME
PRINT @.SvrName
SELECT * FROM "@.SvrName"Doesn't work that way

DECALRE @.SQL
SET @.SQL = 'SELECT * FROM ' + @.SvrName
EXEC(@.SQL)

But why do this...uhh dynamic sql...|||Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.|||Originally posted by acral
Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.

Huh?|||This will be running as a stored proc... the proc takes many steps in moving data around, but first I need to determine what environment the user is in (i.e. what server)...

The there will be a series of statements such as Update this table, email a percentage to that group, make a temp table over there, and so on. In one environment, all of the databases are on one server, in another environment, the database are on different servers. In both cases, they have to interact.

So if YOU are logged into the system, when the stored proc executes, it will see which server you are logged to then point you from there by way of the rest of the statements.

Make sense?|||Not to me, but that's not saying much..

What's the application layer?|||Wouldn't it just be simpler to write a stored procedure that does what you need on each server, then call the stored procedure on the appropriate server? This gets about a gazillion RPC calls and cross-server queries (with potential cross-server joins) out of the way.

That way each box can call one stored procedure (you could even make it an sp_ if you wanted to make thing simple), and there are so many fewer moving parts.

-PatP|||Okay, here's what I ended up doing...

using a string like

Exec(@.SQL) was not going to cut it, so I have an if/then scenario that checks @.@.SERVERNAME then sets a variable. The contents of that variable in turn point to the right server for the right instance.

The reasoning is this.. in one evironment the databases referenced are on the same server, in another environment they are on different servers.

Thanks for the help... I would have kept pounding on that stupid "string" half the night had you guys not set me straight. :)

invalid object name

In my database I have created a table called "com01" owned
by "lisa" and if I try to select the rows with the
statement "select * from lisa.com01" it works fine !
But if I submit the statement "select * from com01" it
return the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'com01'.
Why '
In both cases I'm connected to the database with
user "lisa" who is the owner of the table. There isn't
other table called "com01" in the database.
Help me please !!!Is 'lisa' a member of the sysadmin role? In this case, the default owner
will be 'dbo' instead of 'lisa' when resolving object names. You can
determine the name used for object name resolution with SELECT USER.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||HI
Read Books Online: object names -> Object Visibility and Qualification Rules
Andras Jakus MCDBA
"paolo" wrote:
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
>|||Thank you for your help Andras !
I've just read the documentation as you suggest me, but
unfortunately the problem is not solved:
"lisa" is the owner of the table "com01" and is the user
connected to the database, but if I want to select from
that table I've to specified the owner_name dot table_name
(lisa.com01) and not only the table_name (com01). Is the
same also for other tables owned by "lisa" !
If I try to select from table owned by "dbo" only with
table_name (sysobjects) it works fine !
"lisa" is db_owner of the database.
Any other suggestions '
Thank you!
Bye Paolo.
>--Original Message--
>HI
>Read Books Online: object names -> Object Visibility and
Qualification Rules
>Andras Jakus MCDBA
>"paolo" wrote:
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>.
>|||Bingo Dan ! "lisa" is a member of sysadmin role and the
statement "select user" return "dbo".
Is it possible to select from tables owned by "lisa"
without specified the owner ? I can't disable sysadmin
role for lisa !
Thank you !!!
Bye Paolo.
>--Original Message--
>Is 'lisa' a member of the sysadmin role? In this case,
the default owner
>will be 'dbo' instead of 'lisa' when resolving object
names. You can
>determine the name used for object name resolution with
SELECT USER.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"paolo" <paolo.ricci@.gidi.it> wrote in message
>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>
>.
>|||You could create a view
create view dbo.com01 as select * from lisa.com01
then
select * from com01
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||Hopefully, Wayne's view suggestion will address your issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<paolo.ricci@.gidi.it> wrote in message
news:75b501c494df$edd69f20$a301280a@.phx.gbl...
> Bingo Dan ! "lisa" is a member of sysadmin role and the
> statement "select user" return "dbo".
> Is it possible to select from tables owned by "lisa"
> without specified the owner ? I can't disable sysadmin
> role for lisa !
> Thank you !!!
> Bye Paolo.
>>--Original Message--
>>Is 'lisa' a member of the sysadmin role? In this case,
> the default owner
>>will be 'dbo' instead of 'lisa' when resolving object
> names. You can
>>determine the name used for object name resolution with
> SELECT USER.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"paolo" <paolo.ricci@.gidi.it> wrote in message
>>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
> owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>>
>>.

Invalid Object Name

Greetings
I have recently migrated several databases from SQL 7 to SQL 2000. When
accessing one table in one database with a simple SQL statement (SELECT *
FROM tablename) I am receiving error -2147217865-Invalid object name
'tablename'.
Is this related to the migration to SQL 2000 from SQL 7? I have recreated
the table, but still receiving the same error.
Thank you for any insight and help you can provide.
Sidney Mark Croy
Two questions -
1) Does 'tablename' actually exist in the database?
2) Assuming you're using Query Analyzer, are you in the right database
(check the drop-down list up top)?
3) Is 'tablename' a standard ANSI name? If not, try putting brackets [ ]
around it
4) Is the database marked suspect, offline, or any of that good stuff?
5) Are you using the same collation on your SQL 2K server as on SQL 7?
Thanks,
Michael C.
"Sidney Mark Croy" <smcroy@.hotmail.com> wrote in message
news:eldKVP3aEHA.2812@.TK2MSFTNGP11.phx.gbl...
> Greetings
> I have recently migrated several databases from SQL 7 to SQL 2000. When
> accessing one table in one database with a simple SQL statement (SELECT *
> FROM tablename) I am receiving error -2147217865-Invalid object name
> 'tablename'.
> Is this related to the migration to SQL 2000 from SQL 7? I have recreated
> the table, but still receiving the same error.
> Thank you for any insight and help you can provide.
> Sidney Mark Croy
>
|||Greetings
The tablename actually exists, and the query is pointing to the correct
database. The database is not marked suspect, offline, etc.
"Michael C" <nospam@.lol.net> wrote in message
news:vJiKc.32650$Kz3.3168666@.news4.srv.hcvlny.cv.n et...[vbcol=seagreen]
> Two questions -
> 1) Does 'tablename' actually exist in the database?
> 2) Assuming you're using Query Analyzer, are you in the right database
> (check the drop-down list up top)?
> 3) Is 'tablename' a standard ANSI name? If not, try putting brackets [ ]
> around it
> 4) Is the database marked suspect, offline, or any of that good stuff?
> 5) Are you using the same collation on your SQL 2K server as on SQL 7?
> Thanks,
> Michael C.
> "Sidney Mark Croy" <smcroy@.hotmail.com> wrote in message
> news:eldKVP3aEHA.2812@.TK2MSFTNGP11.phx.gbl...
*[vbcol=seagreen]
recreated
>

Invalid Object Name

Greetings
I have recently migrated several databases from SQL 7 to SQL 2000. When
accessing one table in one database with a simple SQL statement (SELECT *
FROM tablename) I am receiving error -2147217865-Invalid object name
'tablename'.
Is this related to the migration to SQL 2000 from SQL 7? I have recreated
the table, but still receiving the same error.
Thank you for any insight and help you can provide.
Sidney Mark CroyTwo questions -
1) Does 'tablename' actually exist in the database?
2) Assuming you're using Query Analyzer, are you in the right database
(check the drop-down list up top)?
3) Is 'tablename' a standard ANSI name? If not, try putting brackets [
]
around it
4) Is the database marked suspect, offline, or any of that good stuff?
5) Are you using the same collation on your SQL 2K server as on SQL 7?
Thanks,
Michael C.
"Sidney Mark Croy" <smcroy@.hotmail.com> wrote in message
news:eldKVP3aEHA.2812@.TK2MSFTNGP11.phx.gbl...
> Greetings
> I have recently migrated several databases from SQL 7 to SQL 2000. When
> accessing one table in one database with a simple SQL statement (SELECT *
> FROM tablename) I am receiving error -2147217865-Invalid object name
> 'tablename'.
> Is this related to the migration to SQL 2000 from SQL 7? I have recreated
> the table, but still receiving the same error.
> Thank you for any insight and help you can provide.
> Sidney Mark Croy
>|||Greetings
The tablename actually exists, and the query is pointing to the correct
database. The database is not marked suspect, offline, etc.
"Michael C" <nospam@.lol.net> wrote in message
news:vJiKc.32650$Kz3.3168666@.news4.srv.hcvlny.cv.net...
> Two questions -
> 1) Does 'tablename' actually exist in the database?
> 2) Assuming you're using Query Analyzer, are you in the right database
> (check the drop-down list up top)?
> 3) Is 'tablename' a standard ANSI name? If not, try putting brackets [
; ]
> around it
> 4) Is the database marked suspect, offline, or any of that good stuff?
> 5) Are you using the same collation on your SQL 2K server as on SQL 7?
> Thanks,
> Michael C.
> "Sidney Mark Croy" <smcroy@.hotmail.com> wrote in message
> news:eldKVP3aEHA.2812@.TK2MSFTNGP11.phx.gbl...
*[vbcol=seagreen]
recreated[vbcol=seagreen]
>

Invalid Object

I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!DBNAME is a built-in function. If your table has a column of that name,
try:
Select TestSchema From TestDB where [DbName] = 'TEST'
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"sql" <sql@.discussions.microsoft.com> wrote in message
news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!|||Tom:
I tried with the square brackets, but I still get the same error message.
"Tom Moreau" wrote:
> DBNAME is a built-in function. If your table has a column of that name,
> try:
> Select TestSchema From TestDB where [DbName] = 'TEST'
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
> I'm running a simple select statement.
> Select TestSchema From TestDB where DbName = 'TEST'
> It runs fine when I run it in Enterprise Manager.
> But I get an error message when I run it in Query Analyser.
>
> Error: Invalid object name 'TestDB'
> Help!
>|||Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner qualify the table to see what you get.
Richard
--
Message posted via http://www.sqlmonster.com|||Oops, you're right. I'd be tempted to run the profiler and see what's being
sent when he runs it (successfully) through Enterprise Manager. That may
give a clue.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Ding via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d534a166a789418e873d7ab25b41a525@.SQLMonster.com...
Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner
qualify the table to see what you get.
Richard
--
Message posted via http://www.sqlmonster.com

Invalid Object

I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!
DBNAME is a built-in function. If your table has a column of that name,
try:
Select TestSchema From TestDB where [DbName] = 'TEST'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"sql" <sql@.discussions.microsoft.com> wrote in message
news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!
|||Tom:
I tried with the square brackets, but I still get the same error message.
"Tom Moreau" wrote:

> DBNAME is a built-in function. If your table has a column of that name,
> try:
> Select TestSchema From TestDB where [DbName] = 'TEST'
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
> I'm running a simple select statement.
> Select TestSchema From TestDB where DbName = 'TEST'
> It runs fine when I run it in Enterprise Manager.
> But I get an error message when I run it in Query Analyser.
>
> Error: Invalid object name 'TestDB'
> Help!
>
|||Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner qualify the table to see what you get.
Richard
Message posted via http://www.sqlmonster.com
|||Oops, you're right. I'd be tempted to run the profiler and see what's being
sent when he runs it (successfully) through Enterprise Manager. That may
give a clue.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Ding via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d534a166a789418e873d7ab25b41a525@.SQLMonster.c om...
Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner
qualify the table to see what you get.
Richard
Message posted via http://www.sqlmonster.com

invalid column error when updating a new column that was added by alter table

i am getting an error "Invalid Column name <column name>" message when runni
ng an update statement which updates a new column which was just added by an
alter table statement. If i run the alter statement and update statement se
perately, i don't get any e
rrors and it works. I only get this when they run together in a script or st
ored proc.
Is this a bug and what are the alternatives. Thanks.This is the way that the parser work. As the column doesn't (yet) exists at
parse time, the update will
generate the error. One way around it is to do the update with dynamic SQL.
OTOH, I'd re-consider why you have
to add a column dynamically in the first place (if possible).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jsin" <anonymous@.discussions.microsoft.com> wrote in message
news:6A977D73-49D1-4229-92B1-B90B8E054540@.microsoft.com...
> i am getting an error "Invalid Column name <column name>" message when running an
update statement which
updates a new column which was just added by an alter table statement. If i
run the alter statement and update
statement seperately, i don't get any errors and it works. I only get this w
hen they run together in a script
or stored proc.
> Is this a bug and what are the alternatives. Thanks.

invalid column error when updating a new column that was added by alter table

i am getting an error "Invalid Column name <column name>" message when running an update statement which updates a new column which was just added by an alter table statement. If i run the alter statement and update statement seperately, i don't get any e
rrors and it works. I only get this when they run together in a script or stored proc.
Is this a bug and what are the alternatives. Thanks.
This is the way that the parser work. As the column doesn't (yet) exists at parse time, the update will
generate the error. One way around it is to do the update with dynamic SQL. OTOH, I'd re-consider why you have
to add a column dynamically in the first place (if possible).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jsin" <anonymous@.discussions.microsoft.com> wrote in message
news:6A977D73-49D1-4229-92B1-B90B8E054540@.microsoft.com...
> i am getting an error "Invalid Column name <column name>" message when running an update statement which
updates a new column which was just added by an alter table statement. If i run the alter statement and update
statement seperately, i don't get any errors and it works. I only get this when they run together in a script
or stored proc.
> Is this a bug and what are the alternatives. Thanks.

Intricate SQL Statement

Hi there at the forum,
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--