|
As always my SQL skills are limited and rusty. How does one accomplish this?
I have a 'Components' table. A 'Component' may be an assembly of other components. This is defined in the 'Assemblies' table.
Assemblies has columns : AssemblyKey / ItemKey / Quantity.
AssemblyKey identifies with Components.ComponentKey and is the ID of the assembly component.
ItemKey is the ComponentKey ID of the constituent component of the assembly
Quantity is the amount of ItemKey items in the assembly.
I want to list all the assemblies in the database, with all the components in each assembly.
Can I do that in one query?
(So
SELECT Components.Description, Assemblies.ItemKey FROM Assemblies, Components
WHERE Assemblies.`Assembly Key` = Components.`Component Key`
Gives me the assembly description and the IDs of the constituent components. I want to turn that resulting list of ItemKeys into descriptions from the Component table).
|
|
|
|
|
My understanding is that you have only 2 levels, Assembly and child items called components.
It does seem weird that you would call the component foreign key to the assembly table ComponentKey the Component table should have a primary key ComponentKey and a foreign key AssemblyKey.
Select *
From Assembly A
left join Components C on C.ComponentKey = A.AssemblyKey
This will give you all the assemblies even if there are no components.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Maybe I haven't described it well! (note I didn't design this schema - its awful, and evolved from a Fortran flat file format)
The components table is the 'main' table. An assembly is type of component, consisting of a number of other components. ComponentKey is the primary key of the component table. Thus I can easily extract the list of components which are assemblies, by marrying that with the assembly table in the manner you describe. That gives me result in the form:
Description ItemKey Quantity
Assembly A 4721 2.0
Assembly A 4854 0.5
Assembly A 4719 2.0
Assembly A 4854 0.5
Assembly B 4712 1.0
Assembly B 4713 1.0
The ItemKey values incestuously refer to other components in the Components table. So I essentially want to do something like:
For Each ItemKey In QueryResult1
SELECT Description From Components WHERE ComponentKey = ItemKey
Next ItemKey
to get something like:
Description ItemKey Desc Quantity
Assembly A Component M 2.0
Assembly A Component N 0.5
Assembly A Component O 2.0
Assembly A Component N 0.5
Assembly B Component P 1.0
Assembly B Component Q 1.0
Ugh - only without the duplicates. Clear as mud?
|
|
|
|
|
I got there with:
SELECT DISTINCT Assemblies.ItemKey, Components.Description INTO Temp
FROM Assemblies, Components
WHERE Assemblies.ItemKey = Components.ComponentKey
then
SELECT DISTINCT Components.Description, Temp.Description
FROM Assemblies, Components, Temp
WHERE Components.ComponentKey = Assemblies.AssemblyKey AND Assemblies.ItemKey = Temp.ItemKey AND ((Components.Flags=1))
(Components.Flags=1 = Indicates an assembly)
But can this be condensed / done without a temp table?
|
|
|
|
|
Sorry I'm not get email notifications at the moment!
My eyes cross when I look at your query trying to figure out the joins. I have not used that style of join in over a decade.
You can use a sub select but it makes no real difference, why do you not want to used a table variable?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can use a sub select but it makes no real difference, why do you not want to
used a table variable?
Crappy reasons. Our database front end program has an advanced option to execute user entered queries - it essentially only allows you one SQL statement, and I don't want to encourage users who only think they know what they are doing to start creating their own tables. And I'm especially not fgoing to let them DROP tables! 
So I was wondering if I could get this done in one SELECT statement.
|
|
|
|
|
Kyudos wrote: Crappy reasons
No worse than some I have heard!
Try this, uses join and a sub select.
SELECT DISTINCT
Components.Description,
Temp.Description
FROM Assemblies
INNER JOIN Components ON Components.ComponentKey = Assemblies.AssemblyKey
AND Components.Flags = 1
INNER JOIN (SELECT DISTINCT Assemblies.ItemKey,Components.Description
FROM Assemblies
INNER JOIN Components on Assemblies.ItemKey = Components.ComponentKey) T
ON Assemblies.ItemKey = T.ItemKey
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<?xml version="1.0" encoding="utf-8"?>
<LicenceManager xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://visionontech.com/vbp/LicenceConfiguration">
<ApplicationName>ovE1c/0l6IoKP/KF6bpqv22zqoYuT3Wv</ApplicationName>
<LicenseType>7Ax330npgHE=</LicenseType>
<VersionType>bbUl/DISjDzJkqERNmGqY8eWlTKz+Er3qSWdcEGFDUdsWo9DJrviULY/entknwWw2Z67rm0RnqeB3ENiAa1blfEkGPtfeELVxVG44JG4J5w=</VersionType>
<CompanyName>KWZwGJLdl+pI240K/PURWg==</CompanyName>
<SerialNumber>7515b594-6afb-4f0f-bf4c-13351339191c</SerialNumber>
<MachineID>I50NMYu1RZix+7AuMOhIDyhyFkiepjdK1Sy93luDdi4nCxRLW+Yy259FgXAvKvX9</MachineID>
<Signature>9XvKvAXgF952yY+WLRxCn4idDul39yS1KdjpeikFyhyDIhOMuA7+xiZR1uYMN05I</Signature>
<City>Q6H8NNOnO80=</City>
<CreatedBy>TZdhgIlUIBo2BIm8yvL3ww==</CreatedBy>
<LicenseCreationDate>1xtf7PZmvZQ5e+ohObsHFHokaELQyvIq</LicenseCreationDate>
</LicenceManager>
my laptop licence is
i m using other computer d'not work plz help me brack my code
|
|
|
|
|
What does brack mean? Do you mean break?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey guys,
I have a problem starting my local SQL server, when I try to start it, then I get this error: Windows could not star the SQL Server (MSSQLSERVER) on local Computer....
I am using SQL server 2008.
Thanks in advance.
|
|
|
|
|
Blikkies wrote: Windows could not star the SQL Server (MSSQLSERVER) on local Computer....
Being telepathic I would say that your server installation does not like you. That is NOT a helpful error message.
Try checking your event logs, you should get some more info from there.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Looked in the log and found this error:
"2011-08-18 12:34:51.23 spid6s Starting up database 'master'.
2011-08-18 12:34:51.29 spid6s Error: 9003, Severity: 20, State: 1.
2011-08-18 12:34:51.29 spid6s The log scan number (352:448:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2011-08-18 12:34:51.29 spid6s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online."
I tried rebuilding the master database but it failed to rebuild.
|
|
|
|
|
Uninstall. Re-install. Then restore from the back up.
|
|
|
|
|
Has it ever worked? If this is a new install I would suggest uninstalling then reinstall.
|
|
|
|
|
It did work before, about a month back i started having this problem.
Looked in the log and found this error:
"2011-08-18 12:34:51.23 spid6s Starting up database 'master'.
2011-08-18 12:34:51.29 spid6s Error: 9003, Severity: 20, State: 1.
2011-08-18 12:34:51.29 spid6s The log scan number (352:448:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2011-08-18 12:34:51.29 spid6s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online."
I tried rebuilding the master database but it failed to rebuild.
|
|
|
|
|
What error message are you getting when you try to rebuild the master?
|
|
|
|
|
Hey,
Here is the link to the log with failure : [log]
Can not make sense of it.
Thanks in advance.
|
|
|
|
|
Whenever we create either permanent table or temporary tables it requires modification of the database schema.
Is the same applicable for Collection objects?
ie when we create collection objects does it modify the database schema/metatdata?
|
|
|
|
|
This depends on the situation. For example if a collection is used in a procedure, information about the collection is stored in the system tables.
Could you explain the situation a bit more, what kind of collection are you thinking of and what usage?
|
|
|
|
|
Hi,
I am using a SSRS report and the report is generated by executing a stored procedure.
The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values.
I am executing a complex SQL statement.
For this i am using nested loop:
A as INPUT
B as INPUT
C as INPUT (String input seperated by ,)
D as input (String input seperated by ,)
Cursor Cur as OUTPUT
Loop for c
Loop for d
.
.
execute the above SQL Statement(select query)
.
.
END loop
END Loop
problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed to use permanent/temporary tables as they require definition at schema level and modificaton of schema is not allowed.
I need to save the value in the cursor at each runs, but in that case the previous results get overwritten.
So i wanted to know collection objects and wanted to know if that too requires definition at schema level.
|
|
|
|
|
Ok, well basically when you create a procedure (of any kind) and store it in the database, you modify the schema. So in this sense the requirement of leaving the schema intact doesn't make sense...
Anyhow, you can use collections and for example if you're using a package, you can define the collection in your procedure or at package level. When you loop through the cursor you can store the results in the collection and later on modify them or add more results etc. If you're using package level variables, you can store the results in them even between procedure calls.
|
|
|
|
|
when i store the results in the collection while i run the loop. can i save the results into the cursor.
|
|
|
|
|
Not directly. If you're using output cursors, you pass the cursor to the caller at the end but the cursor is based on a query from an object.
I know that you can create a cursor based on a nested table but I have never tried that with associative arrays or varrays.
|
|
|
|
|
Hi All,
Can anybody plz. answer what is the maximum number of concurrent user in PostGre database (with current version or any previous)?
Your reply is highly awaiting.
Thanks in advance!
With Regards,
Navin Pandit
Mail:navin2k6@gmail.com
Gurgaon, (India)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
