|
Actually, a recursive CTE is probably your best bet.
|
|
|
|
|
Here's an example that probably does exactly what you want:
Recursive CTE[^]
|
|
|
|
|
I tried below stored procedure. I am getting the result what I want but its print statement. How can I convert this into table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Insert it into a temp table and then select * from that table at the end.
|
|
|
|
|
I did that ... As everytime sp excutes output displays like multiple tables but not in one table
|
|
|
|
|
Have you tried using a CTE or a cursor?
I reckon those are your best bet and probably what your teacher is looking for if this is a homework question.
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Have a look to this code and see whether it solves your problem,
CREATE TABLE TempTree (Id int IDENTITY, Id_Project VARCHAR(100), Id_Parent VARCHAR(100))
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Root','Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 1', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 2', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 3', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 1', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 2', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 3', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 4', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 5', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 6', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 1', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 2', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 3', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 4', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 5', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 6', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 7', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 8', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 9', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 10', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 11', 'Level - 2 6')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 12', 'Level - 2 6')
CREATE PROC Dbo.Proc_TheTree (@parent VARCHAR(100))
AS
CREATE TABLE #TheList (RootId int, RootName VARCHAR(100), ChildName VARCHAR(100))
CREATE TABLE #TheSearch (SLNO INT IDENTITY, ParentName VARCHAR(100), IsSearchCompleted BIT)
IF NOT EXISTS (SELECT * FROM TempTree WHERE id_parent = @parent)
BEGIN
SELECT * FROM TempTree WHERE id_project = @parent
END
ELSE
BEGIN
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @parent
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @parent), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @parent
DECLARE @MINSLNO INT
DECLARE @PARENTNAME VARCHAR(100)
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch
WHILE ISNULL(@MINSLNO,0) > 0
BEGIN
SELECT @PARENTNAME = ParentName FROM #TheSearch
WHERE SLNO = @MINSLNO AND ParentName <> @parent
IF EXISTS (SELECT * FROM TempTree WHERE id_parent = @PARENTNAME)
BEGIN
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @PARENTNAME), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @PARENTNAME
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @PARENTNAME
END
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch WHERE SLNO > @MINSLNO
END
SELECT * FROM #TheList
END
EXEC Proc_TheTree 'Level - 1 1'
|
|
|
|
|
I'm only a very occasional SQL user, so I'm not sure if this is even possible - but if it is, can someone help me with the SQL?
The situation:
In Table 1, I have a list of components, each with an ID and a type.
In Table 2, I have a mapping between Outlet components and Nozzle components (two columns with component IDs in both, lets call them Out_IDs and Nozz_IDs, an Outlet may be linked to many nozzles).
I'm trying to find all the Nozzle components that are not linked to any outlets.
So in pseudo-SQL:
SELECT * FROM Components WHERE (Component.[Type] = Nozzle)
AND (Component.[ID] Is Not One Of Mapping.[Nozz_IDs])
It's the AND... bit I can't figure out, probably some sort of JOIN I can't fathom.
Thanks all,
Dan
|
|
|
|
|
Bah!  Of course I figure it out as soon as I ask for help!
SELECT * FROM Components LEFT JOIN Nozzles ON Components.[Component Key] = Nozzles.[Nozzle Key]
WHERE (((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null));
|
|
|
|
|
Ain't is the way.
Just some advice.
Notice that you need [] around column names with spaces and special characters, eliminate these so [Component Key] becomes ComponentKey, life will be much more pleasant.
Also NEVER use key words in a column name (Key and Database) I know these will not be key words when concatenated but it still applies.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the advice, I'll bear it in mind for the future. Unfortunately, I've inherited this MDB structure based on an old Fortran record structure so changing the names is not so easy!
|
|
|
|
|
Can you see anything wrong with this?
CDaoDatabase* pDB = static_cast<CMainFrame *>(AfxGetMainWnd())->GetDatabase();
CMyRecords Records(pDB);
CString sSQL = "SELECT * FROM Components LEFT JOIN Nozzles ON
Components.[Component Key] = Nozzles.[Nozzle Key] WHERE
(((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null))";
try
{
Records.Open(dbOpenDynaset, sSQL, 0);
if (Records.IsBOF() || Records.IsEOF())
{
return;
}
Records.MoveFirst();
while (!Records.IsEOF())
{
if (Records.CanUpdate())
{
Records.Delete();
}
Records.MoveNext();
}
Records.Close();
}
catch(CDaoException *e)
{
e->ReportError();
e->Delete();
if (Records.IsOpen())
{
Records.Close();
}
}
I can see it gets the right records, it doesn't give anY errors or exceptions, but it also doesn't delete the records!
Must be missing something...
|
|
|
|
|
Without going into the code the first point I would make is that deleting a record while inside a collection changes the collection. So you move to the first record and delete it, you collection hanged and where am I supposed to move from.
Try starting from the last record in the collection, delete that and move forward 1 record, or go to the last record in the collection. Basically change the way your loop works.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
DaoRecordsets don't work like that - presumably for the very reason you describe! 'Delete' simply marks a record as deleted and doesn't alter the record order. this also allows you to rollback changes if necessary.
It's all working now though, so thanks for your input!
|
|
|
|
|
The MessageBoard fairies strike again!
If I change the SQL to the much simpler:
"SELECT * FROM Components WHERE (Components.[Database Number])=15 AND Components.[Component Key] NOT IN (SELECT [Nozzle Key] FROM Nozzles)"
it all works.
|
|
|
|
|
Hi!
I've to read each name in a field from a table and do some string mangling and update each name into another field. I've to query the database from C++. I'm using SQLite. I used the following code to do this:
SQLManager SQLdb;
SQLdb.init("../../data/config/playerDatabase.db");
array<stringc> ctyLst = SQLdb.Query("Select DISTINCT Team from Test ORDER BY Team DESC");
array<stringc> playerLst;
core::stringc queryStr,updateTest;
for(u32 i = 0; i<ctyLst.size();i++)
{
queryStr = L"Select Name From Test WHERE Team ='";
queryStr += ctyLst[i].trim();
queryStr += L"'";
array<stringc> playerTmpLst;
playerTmpLst = SQLdb.Query(queryStr);
for(u32 j = 0; j < playerTmpLst.size(); j++)
{
playerLst.push_back(playerTmpLst[j].c_str());
}
}
I'm doing string mangiling an updating as follows:
for(int j = 0; j<playerLst.size(); j++)
{
updateTest = L"UPDATE Test SET MangledName ='";
unsigned int t = time(NULL);
srand(t);
playerLst[j] = playerLst[j].trim();
int index = playerLst[j].findLast(' ');
stringc subStr1 = playerLst[j].subString(0,index);
stringc subStr2 = playerLst[j].subString(index+1,15);
subStr1.append(" ",1);
subStr1.append(swapChars(subStr2).c_str(),subStr2.size());
updateTest += subStr1.trim();
updateTest += L"'";
SQLdb.Query(updateTest);
Sleep(10);
cout<<subStr1.c_str()<<endl;
t++;
}
While printing the result to a file, it prints correctly. But only the last name in the array is updated to all the records in the field. How to Select a single name at a time and do the string mangling and update the name to the database?
|
|
|
|
|
One normally uses a WHERE clause to specify which record(s) get updated.
Remarks:
- what is the purpose of Sleep(10)?
- what would happen if a player's name contained a quote?
- why don't you use SqlParameters instead of string concatenation?
|
|
|
|
|
plzz give me the code for inserting the data in the database through msAccess DataBase.
|
|
|
|
|
No problem[^].
You can skip all the CodeProject articles, since I trust you've read them before.
|
|
|
|
|
On a side note... I couldn't remember a particular .net class this week so I went on a Binge and found not only a CP article, but one of mine! 
|
|
|
|
|
and was it satisfactory?
|
|
|
|
|
Ohhh yeahhh. Most times I need to use the particular class I simply pull up that article anyway. This time I didn't because I was at work. (I try not to access CP from my new job.)
|
|
|
|
|
I hate when that happens.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
