May 2, 2012 at 6:43 am
Hi guy i have a problem when i update a data may one of can help me.
since this morning i try to found a solution by myself without success. I was already ask something here it helps me but for a few moment i think all thing were ok but i'm still a biginner.
Please forgive me because they are some variable in German because i study here.
here is the code
/****
*create a different table
****/
CREATE TABLE AbhängigkeitTabelle(
AB_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
Vorgang_ID_Netzplan2 int NOT NULL,
ABhängigkeiten_ID_Netzplan int NULL,
)
CREATE TABLE Netzplan_2(
Vorgang_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
Abhängigkeiten varchar(50) NOT NULL,
Dauer int NOT NULL,
F_Anfang int NULL,
F_Ende int NULL,
S_Anfang int NULL,
S_Ende int NULL,
Puffer int NULL,
)
CREATE TABLE Netzplan(
V_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
V_Name varchar(50) NOT NULL,
Dauer int NOT NULL,
Frühester_Anfang int NULL,
Frühestere_Ende int NULL,
Späterer_Anfang int NULL,
Späterere_Ende int NULL,
Puffer int NULL,
)
/*---------------------------------------------
*Create a view
*----------------------------------------------*/
CREATE VIEWvSicht_Abhängigkeit AS
SELECTA.V_NameAS VorgangName,
A.DauerAS Dauer,
AD.AbhängigkeitenAS Abhängigkeiten
FROMAbhängigkeitTabelleAS ATLEFT OUTER JOINNetzplanAS AON AT.Vorgang_ID = A.V_ID
LEFT OUTER JOIN Netzplan_2AS ADON AT.ABhängigkeiten_ID = AD.Vorgang_ID
/*************
*Now i insert a Value in table natzplan and natzplan_2
*************/
insert into Netzplan
(V_Name,Dauer)
values
('A',25),('B',20),('C',40),('D',50),('E',30),('F',30)
-------------------------------
insert into Netzplan_2
(Abhängigkeiten,Dauer)
values
('A',25),('B',20),('C',40),('D',50),('E',30),('F',30)
/******
*Now i made a connection between Netzplan and Natzplan_2 with hel of AbhängigkeitTabelle
AbhängigkeitTabelle is my classification table oder assignement table
******/
INSERT INTO AbhängigkeitTabelle
(Vorgang_ID ,ABhängigkeiten_ID)
VALUES
(1,2),(1,3),(2,6), (3,Null), (4,6),(5,1), (6,Null)
/**********
*I try to do a Update
this first and second UPDATE walk but the third don't walk
***********/
------It run ok-----
UPDATE Netzplan SET Frühester_Anfang = 0
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL)
-----It Run ok----
UPDATE Netzplan SET Frühestere_Ende = (Frühester_Anfang + Dauer)
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL)
------THIS DON'T RUN ----
UPDATE Netzplan SET Frühester_Anfang IN (SELECT Frühestere_Ende FROM Netzplan WHERE
V_ID IN (SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
WHERE V_ID = (SELECT ABhängigkeiten_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IN
(SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
------OR Like this it not run too---
UPDATE Netzplan SET Frühester_Anfang = (SELECT Frühestere_Ende FROM Netzplan WHERE
V_ID IN (SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
WHERE V_ID = (SELECT ABhängigkeiten_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IN
(SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
-------I try to simplify like this but it not run-------
---PLEASE has somebody an idea how i can do???---
UPDATE Netzplan SET Frühester_Anfang = (SELECT Frühestere_Ende FROM Netzplan WHERE
V_Name IN (SELECT Abhängigkeiten FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NOT NULL))
-----------
/*****
*Failure: Msg 512, Level 16, State 1, Line 1
*Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <,
* <= , >, >= or when the subquery is used as an expression.
*The statement has been terminated.
************/
-----What can i do please??
May 2, 2012 at 6:58 am
Please don't start multiple threads on the same topic. It fragments the replies.
Please post in the original thread: http://www.sqlservercentral.com/Forums/Topic1293567-338-1.aspx.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 2, 2012 at 7:06 am
I'm sorry how can i do to see for example my old post without check my email
May 2, 2012 at 7:10 am
If you go to the My Account page, and then the Forum Control Panel (shortcut here: http://www.sqlservercentral.com/Forums/ControlPanel.aspx), you can view your Forum and Topic subscriptions. It's a little ways down the page, left column of the main window. Shortcut here: http://www.sqlservercentral.com/Forums/TopicSubscriptions1.aspx
That will give you a list of the forum threads you are subscribed to. By default, you subscribe to any topic you either start or reply to.
Personally, I use that page instead of e-mail notifications. I turn off the e-mails because I find them annoying.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply