Database

15-Jul-04 11:38

I am trying to take the result of a caculation of two fields in one table and place it into a field in another table? Is this possible?

I have two tables StoreItems and TicketItems. I need to get a unit cost from my StoreItems table by dividing the cost for an entire case (CaseCost) by the case count (CaseCount) and then placing that result into the Cost field of an item in the TicketItems table with a corresponding PLU (like an SKU). PLU and StoreID are the two fields in the tables that link them together. In a nutshell I need this:

StoreItems.Cost = TicketItems.CaseCost / TicketItems.CaseCount

Keep in mind that I need to run this on every record in the two tables so just passing in a StoreID and PLU is not enough. This has to be done across the board--that is, every TicketItems record needs to have its Cost column set when this has finished running.

Thank you.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 12:03

Colin Angus Mackay

15-Jul-04 12:03

Off the top of my head the following should do what you want.

[EDIT] Oops. I made a mistake the Cost field you are updating is in the StoreItems table, the original query was updating the TicketItems table Blush | :O

[/EDIT]

UPDATE StoreItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

You can get more information in your SQL Server books online, look up the index for UPDATE, UPDATE (described)

Does this help?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

perlmunger15-Jul-04 12:21

15-Jul-04 12:21

Actually, no you were right the first time. The cost field is in the TicketItems table not the StoreItems table. So what did that query look like?

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 12:29

Colin Angus Mackay

15-Jul-04 12:29

Almost the same actually:

UPDATE TicketItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

I'm now a bit confused, which table do the CaseCost and CaseCount items reside? If it is StoreItems then the query will look like this instead:

UPDATE TicketItems
SET Cost = StoreItems.CaseCost / StoreItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

The second query assumes:
TicketItems.PLU
TicketItems.Cost

StoreItems.PLU
StoreItems.CastCost
StoreItems.CaseCount

PLU is the common field that joins the two tables.

This update will update every row in the TicketItems table.

Does this help? Or have I confused you in return?

perlmunger15-Jul-04 12:39

15-Jul-04 12:39

I think the second one is right. Let me try it out.

Thanks a lot for your help.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

perlmunger15-Jul-04 13:02

15-Jul-04 13:02

It worked and I understand it. You're a life saver!! Thank you!!

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 13:07

Colin Angus Mackay

15-Jul-04 13:07

perlmunger wrote:
It worked and I understand it.

Excellent. Big Grin | :-D

dishanf19-Jul-04 1:57

dishanf

19-Jul-04 1:57

Yes . you can update from one or more table ., Try this sql. may be want to change some col names. I think you get the idea.. Wink | ;)

UPDATE StoreItems
SET Cost = T.CaseCost / T.CaseCount
FROM StoreItems S INNER JOIN TicketItems T
ON S.PLU = T.PLU

HTH

D!shan

What's wrong with this script from a Wrox Book

dishanf19-Jul-04 2:03

dishanf

19-Jul-04 2:03

Oh.. sorry . already answered! .. net is too slow Frown | :(

D!shan

mtbjr15-Jul-04 2:18

mtbjr

15-Jul-04 2:18

I've been reading the former Wrox book "Professional ADO.NET Programming", and the samples in Chapter 3 requies an SQL database named "WroxDB", the downloaded source code includes a SQL script file named "WroxDB.sql". I'm new in SQL scripts, and stored procedures. I have VS.NET and MSDE installed, I tried running this script from osql using this:

C:\MSSQL7\Binn>osql -U sa -P -i C:\WroxDB.sql

The WroxDB.sql file contains this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabAuthors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabAuthors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabPayments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabPayments]
GO

CREATE TABLE [dbo].[tabAuthors] (
[AuthorID] [int] NOT NULL ,
[Author_FirstName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Author_LastName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tabPayments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorID] [int] NOT NULL ,
[Payment] [money] NOT NULL ,
[Paid] [bit] NULL
) ON [PRIMARY]
GO

This execution always displays this message:

1> 2> 3> 1> 2> 3> 4> 1> 2> 3> 4> 5> 6> 7> 8> Msg 170, Level 15, State 1, Server
MANUELJR, Procedure , Line 4
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near
'COLLATE'.
1> 2> 3> 4> 5> 6> 7> 8> 1> 2>
C:\MSSQL7\Binn>

Please help, support in APRESS in this book is terrible.

Michael Potter15-Jul-04 5:23

Michael Potter

15-Jul-04 5:23

(1)I would run this in SQL Query Analyzer instead of osql. By default, sa is going to create these structures in the Master DB. That is never a wise method of operating. You could add a few statements at the top to handle the database creation also.

CREATE DATABASE TestingDb
GO

USE TestingDb
GO

(2) The script ran fine on my box. You can always drop the COLLATE statement and just use your systems default collation for strings. Your error may have something to do with your SQL version or SQL installation choices.

CREATE TABLE [dbo].[tabAuthors] (
[AuthorID] [int] NOT NULL ,
[Author_FirstName] [char] (50) NOT NULL ,
[Author_LastName] [char] (50) NOT NULL ,
[Photo] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Steven Campbell15-Jul-04 7:01

Steven Campbell

15-Jul-04 7:01

This is a SQL 2000 script that you are running on SQL 7. Not all of the SQL 2000 syntax is supported in SQL 7 -- specifically, it may run if you remove the COLLATE SQL_Latin1_General_CP1_CI_AS pieces. There may be other incompatibilities though.

my blog

mtbjr15-Jul-04 16:29

mtbjr

15-Jul-04 16:29

OK, I've uninstalled SQL 7 and installed MSDE SP3a. I'll try your suggestions when I get home. One problem is, I've deleted all entries of MSSQL in the registry but when I try to run the SQL Server Service Manager, there's a blank entry above the MANUELJR entry in the Server combo box, is this right? What's that blank entry? Does that mean that I've missed or did not properly removed SQL 7? Please help.

Mekong River17-Jul-04 4:58

Mekong River

17-Jul-04 4:58

Can you tell me what is windows you are using? (w2k, xp...) answer clearly about its version (if win2k tell me, server or pro.)

On the other hand, check on wrox website. They also have an errata site for user to post any question or any error on it to update to the correct one.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

OleDbConnection to MS Project 2000

mtbjr18-Jul-04 12:22

mtbjr

18-Jul-04 12:22

I'm using Windows XP Pro, almost all of old Wrox titles are in APRESS now, I've downloaded the sample files. There's no errata, even though I've found several errors in the book already. Forums on previous Wrox books in APRESS are not well organized, all questions regarding them are just in one forum topic, so it's very difficult to find out if anyone is having a question about my book.

frankvgb14-Jul-04 20:19

frankvgb

14-Jul-04 20:19

I am desperately trying to make a connection to an MS Project 2000 (or 2003)in order to extract some well defined data and to make some work on them and to put the results in SQL Server 2000. However I can't find the connection string in order to connect to MS Project.

Lastly I found the following OleDbConnection object :
' ... OleDbConnection conData = new OleDbConnection("Provider=Microsoft.Project.OLEDB.11.0; PROJECT NAME=h:/Plan Projet POC-Review1.mpp"); ... '

However it doesn't work.
More. Before connecting to MS Project, has the .mpp file to be "saved as type: Project Database" in the "save as" box which produces an .mpd file, before trying to connect.

I know I could handle the problem through the use of a DTS package but for some reason I have to do it through the use of ADO.NET and C#.

Please could somebody help me before getting grey hair (I already got some)
Confused | :confused:

and

Frank

SQL Select data flow

Antonius_r314-Jul-04 16:26

Antonius_r3

14-Jul-04 16:26

I have some questions regarding SQL select command, but first let me explain my condition first.
I am a newbie in C# and SQL, and developing some window application with database connected from SQL Server.

And the questions I have is when the select command only want to retreive some portions of data (one or two rows from hundreds of row from the same table).

I know there are two type or methods of having the select command: stored procedure and non-stored procedure.

For the stored procedure, the query is processed in the SQL Server, and return the result to the workstation.

But for non-stored procedure select command, is it doing the same thing? or the query return all row of data to the workstation, and then the workstation process it after receive all the data?

Since I have concern that my network will be overloaded if all the data are being transfered only to take one or some rows of data.

Thank you very much in advance.

Grimolfr15-Jul-04 1:37

Grimolfr

15-Jul-04 1:37

With an ad-hoc query (a query passed to the server, instead of using a stored proc) the SQL Server will still honor the where clause and only return the rows you want to the application.

There are a couple of advantages to stored procs over ad-hoc queries:
- ad-hoc queries have to be compiled every time you perform the query. Stored procedures are compiled, then the compiled version is re-used for some time, and re-evaluated/re-compiled only occasionally. This is a major benefit in high-volume systems.

- depending on the app, sometimes it's possible to tweak performance and/or operation of an application with modifications to the stored procedure, without having to recompile the entire application to implement the change.

- depending on the complexity of the query, sometimes it's simply easier to read the code when the SQL code is completely isolated from the C#/VB code, instead of the two being mixed together in the application.

There are more benefits to procs, of course. And there are some benefits that ad-hoc queries have over procedures (although IMHO few and far between.) But I much prefer using procs. Mainly for the reasons listed above.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Bill Dean15-Jul-04 1:39

Bill Dean

15-Jul-04 1:39

ADO.NET (your SqlDataAdapter, etc) is a disconnected system. Meaning, all the data is held and manipulate on the client. (Someone correct me if I am wrong here...) So, no matter how you structure your query, all the data goes to the client when you call the Fill method on your SqlDataAdapter.

If you are concerned about swamping your connection, I suggest you write a more restrictive query, so that it only returns the rows you actually need. If you cannot tighten up the logic, you can try something like:

<br />
select top 2 column1,column2, etc<br />
from YourTable<br />

Hope this helps,
Bill

Antonius_r315-Jul-04 1:57

Antonius_r3

15-Jul-04 1:57

Thank you very much for the reply.

I guess from the reply, I understand it much more than before, and I think I am in the right path.

In most of my query, I used ADO.NET, but restricted the select command to the columns and rows that I am interested in by using WHERE clause.

My concern was, the network load of the select command.
I was afraid that the ADO.NET select command retreive all the data and rows even I only selected one column or row. And then the client applied filter to the retreived data to get the data I wanted.
But I guess I'm wrong.

Again, thank you so much for the reply. It gives me very much information and knowledge about SQL.

Bill Dean15-Jul-04 1:59

Bill Dean

15-Jul-04 1:59

SqlServer does the "selecting" so only the results of the query travel from the server to your client application.

Bill

Antonius_r315-Jul-04 2:02

Antonius_r3

15-Jul-04 2:02

Yes, I understand it now.

Thank you very much for the reply. Smile | :)