Database

29-Nov-04 9:23

Hello,

I'm writing a stored procedure to copy a "quote" (unfinished order) to a finished order (two tables similar to each other, but not identical). I just don't know how to copy the items (each quote or order can have multiple items.)

My QuoteItems table has an ID_quote column, and I want to copy all the QuoteItem records with a specific ID_quote to my Items table (again, similar but not identical, so I'd need to additional fields and change another.)

It should be that difficult, it's just that I'm starting with "advanced" T-SQL. Any ideas?? Thanks in advance,

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Edbert P29-Nov-04 10:56

29-Nov-04 10:56

The T-SQL will depend on what you want to insert into the new table exactly. If you can provide more information I may be able to help you create a stored procedure you need.

Depending on your needs, you might need to loop using cursors, or you only need to do this:

INSERT INTO tblX (IDQuote, FieldA, FieldB, ...)
(
SELECT ID_Quote, FieldA, FieldB, ...
FROM QuoteItems [JOIN tableX ON...]
WHERE ID_Quote = @idQuote
)

Edbert P.
Sydney, Australia.

Luis Alonso Ramos29-Nov-04 11:22

29-Nov-04 11:22

Thanks for your reply.

I use SELECT to get the quote info (customer information and employee) into local variables and then I insert those into my Orders table.

My QuoteItems table has several fields (ID_item (identity PK), ID_quote, Description) and my Items table has those fields plus others (ID_item (identity PK, different from QuoteItems'), ID_order (newly created order), Description, Produced (must be set to false on record insertion)).

I can create the new Orders record based on my Quotes record. Then I need to select all records from QuoteItems with a specific ID_quote and insert corresponing records into the Items table, copying some fields, and setting others to an arbitrary value (like the ID_order field).

I remember seeing a query like this:

UPDATE Items SET Field=value WHERE id_item IN (SELECT id_item FROM Orders WHERE ID_order=1);

I don't know the exact syntax, and whether I could apply it here, but I would believe my answer would somewhere close to that.

I hope I have explained myself better. Thanks for your help,

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Luis Alonso Ramos30-Nov-04 8:14

30-Nov-04 8:14

Maybe if I could call another stored procedure for each record in QuoteItems that has the ID_quote set to a specific value??

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Edbert P30-Nov-04 10:49

30-Nov-04 10:49

I didn't get a clear idea from your previous reply.
Do you actually need to insert new rows to the QuoteItems or do you need to update specific values?
If you can give me an example of what data you need to put in it'll be great Smile | :)

Edbert P.
Sydney, Australia.

Luis Alonso Ramos30-Nov-04 13:25

30-Nov-04 13:25

Ok, let's try. I have two sets of tables. When the salesman is working up an order, he captures it in the Quotes table, and its items in the QuoteItems. When the order is accepted, it is copied to the Orders and OrdersItems tables. Most fields are copied the same, but some change (for example the foreign key in QuoteItems is different than that in the OrderItems.)

For the example, the salesman is working with three potential customers, each buying 3 items. The Quotes table has 3 records (for each potential sale) and the QuotesItems has 9 records, 3 for each customer.) The potential sales are saved in the database because it takes some days, and can be edited later if a new requirement is found. Once sent for production, no changes can be made (well, maybe with a phone call.)

Now customer A decides on buying and gives an advance deposit. A new record must be created in Orders and the corresponging three in OrderItems, representing the new real sale, and effectively sending the order for production. Then the quote is marked as been really sold.

I would like to do this in a stored procedure with only one parameter, the PK (ID_quote), and it automatically create the corresponding records in the two analogous tables.

I can already copy the main record in Quotes to Orders (a SELECT into local variables and then an INSERT, and then a SELECT @@IDENTITY to get the new ID_order.) What I cannot do is copy all the records in QuoteItems with ID_quote = @ID_quote (the stored procedure's parameter) and insert them in OrderItems, changing some values (ID_order for example).

I hope its clearer now. Thanks for your help!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Edbert P30-Nov-04 15:11

30-Nov-04 15:11

Let's see.
You can already:
1. Insert into Orders.
By this it means you copy record in quotes to orders and give it a new PK (ID_order).
2. Get the ID_order (by using SELECT @@IDENTITY)

According to you,
QuoteItems has :
1. ID_item (PK)
2. ID_quote
3. Description

If you have ID_quote field in the Orders table the sp may look like this:

CREATE PROCEDURE dbo.sp_InsertOrdersItems @ID_quote int  AS

INSERT INTO OrdersItems
(ID_order, Description, Produced)
(
SELECT Orders.ID_order, QuoteItems.Description, false
FROM Orders 
INNER JOIN QuoteItems ON Orders.ID_quote = QuoteItems.ID_Quote
WHERE Orders.ID_Quote = @ID_quote
)

If you don't have ID_quote field in the Orders table then you need to pass ID_order into the sp's parameters.

Hope that helps!

Edbert P.
Sydney, Australia.

Luis Alonso Ramos30-Nov-04 15:23

DataSet 2 Xml - element order problem

30-Nov-04 15:23

You've just earned a big Fosters!!!

Thanks! Actually my SELECT query was simpler than that (no JOIN required), but what I wanted was the SELECT inside the INSERT! It now works flawlessly!! thank you very very much!!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

SQL statement help

ruifernandes2429-Nov-04 7:29

ruifernandes24

29-Nov-04 7:29

Hi
I need help on a SQL statement (I'm on first steps in the world of SQL)

I have 3 tables

LG (id_lg, name)
LineLG (id_linelg, id_lg, job, type_of_hour, monday, tuesday, ..., sunday)
Type_of_hour(type_of_hour)

I need to build an sql statement that retrives:

the id_lg, name, normal, Outdoor, after_hours

where normal, Outdoor, after_hours are the type of hours and I need for each LG the total of normal, outdoor and after_hours hours wich is listed in Table lineLG

Thanks

Re: SQL statement help

Gerald Schwab29-Nov-04 7:58

Gerald Schwab

29-Nov-04 7:58

Is this what your looking for?

SELECT LG.id_lg, 
       LG.name, 
       SUM(LGL.type_of_hour) AS sum_of_hourtypes
  FROM LG AS LG 
  JOIN LineLG AS LGL
    ON LGL.id_lg = LG.id_lg
 GROUP BY LG.id_lg, 
       LG.name
 ORDER BY LG.name

or this:

SELECT LG.id_lg, 
       LG.name,
       COUNT(LGL.type_of_hour) AS count_of_hourtypes
  FROM LG AS LG 
  JOIN LineLG AS LGL
    ON LGL.id_lg = LG.id_lg
 GROUP BY LG.id_lg, 
       LG.name
 ORDER BY LG.name

Xabatcha29-Nov-04 3:18

Xabatcha

29-Nov-04 3:18

I use dataset to export data into xml.
I have problem to hold order in xml doc. I am able to put child nodes, but they are put during saving to xml in the end.
I need:
<main>
<name>aa</name>
<adrress>
<street>aaaa</street>
</adrress>
<phone>151561</phone>
</main>
but it gives me:
<main>
<name>aa</name>
<phone>151561</phone>
<adrress>
<street>aaaa</street>
</adrress>
</main>

did somebody solve this before? thanx for any response....

--------------------------
Xabatcha ... test the best
--------------------------

OREIGN KEY REFERENCES?

HahnTech28-Nov-04 12:42

28-Nov-04 12:42

Can you make a Foreign key reference to a Compound primary key?
Eg.

Create table ThisTable (
Field_A varchar (25) not null,
Field_B Varchar (25) not null,
Field_C varchar (25) not null,
Fieldszzzz varchar (25),
...
constraint pk_thistable primary key ( Field_A, Field_B, Field_C )
)

Create table thatTable {
Field_1 varchar (25) Foreign key references (pk_thistable), -- this donsn't work but it is the efect i want how would i code it?
MoreFields varchar (25),
...,
)

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net

Christian Graus28-Nov-04 13:46

Christian Graus

28-Nov-04 13:46

No, I doubt you can turn three varchar primary keys into one. Especially as the one has the same length as each of the three. I reckon you can add three foreign keys, one for each, but I'm not sure how effective this primary key is going to be anyhow, three variable length strings ? That's not a quick thing to look up, or an easy thing to index.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

HahnTech28-Nov-04 14:54

28-Nov-04 14:54

You can take 3 fields (who cares what they are) and put them into one PK constraint. Thats not the problem. You can't make a FK to a non Unique field and non of the fields in the PK are unique; however the combination of the 3 fields is unique. I wan't to know if i can make a FK to a PK Constraint and not just a single field.

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net

Christian Graus28-Nov-04 18:13

Christian Graus

28-Nov-04 18:13

HahnTech wrote:
You can take 3 fields (who cares what they are) and put them into one PK constraint.

I know. I'm just saying that you're making poor choices. Not that they are invalid.

HahnTech wrote:
You can't make a FK to a non Unique field and non of the fields in the PK are unique; however the combination of the 3 fields is unique. I wan't to know if i can make a FK to a PK Constraint and not just a single field.

My point was just that you can't cram 75 characters of field into 25 chars, and the fact that they are varchars makes it even more complex. I dunno if you can have a foreign reference to a multiple primary key, but if you can, it won't work the way your code showed. Personally, I think your table is crying out for an identity field.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Edbert P28-Nov-04 18:44

28-Nov-04 18:44

I agree with Chris.
It's better if you set an identity field and make it your FK.

Edbert P.
Sydney, Australia.

HahnTech28-Nov-04 23:23

28-Nov-04 23:23

I think an identity field would be the cats ass as well; however i have a boss that dons't aggree with the rest of the world on this one. Will SQL2000 allow a FK refernce to a either a Primary Key or to a group of tables that are constraind unique?

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net

Mike Dimmick29-Nov-04 1:00

Mike Dimmick

29-Nov-04 1:00

Instead of stating FOREIGN KEY after the column, you must state it as a separate constraint at the end. You need to say:

CREATE TABLE ThatTable
(
   Field_A varchar(25) not null,
   Field_B varchar(25) not null,
   Field_C varchar(25) not null,
   MoreFields varchar(25)
   CONSTRAINT FK_ThatTable_ThisTable
   FOREIGN KEY (Field_A, Field_B, Field_C) 
     REFERENCES ThisTable (Field_A, Field_B, Field_C)
   /* any ON UPDATE, ON DELETE clauses here */
)

In the parentheses after FOREIGN KEY, state the columns in this table that make up the foreign key. After REFERENCES, put the table containing the corresponding key data and inside the parentheses the fields in that table that make up the referenced key data.

Stability. What an interesting concept. -- Chris Maunder

HahnTech29-Nov-04 12:00

29-Nov-04 12:00

Sweet. I havn't had a chance to test it but it looks like it should work. Thanks

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net