Database

18-Aug-11 14:36

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

SQL

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).

Mycroft Holmes18-Aug-11 15:04

18-Aug-11 15:04

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

Kyudos18-Aug-11 15:22

18-Aug-11 15:22

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?

Kyudos18-Aug-11 18:34

18-Aug-11 18:34

I got there with:

SQL

SELECT DISTINCT Assemblies.ItemKey, Components.Description INTO Temp
FROM Assemblies, Components
WHERE Assemblies.ItemKey = Components.ComponentKey

then

SQL

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?

Mycroft Holmes18-Aug-11 20:38

18-Aug-11 20:38

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

Kyudos18-Aug-11 22:07

18-Aug-11 22:07

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! Big Grin | :-D

So I was wondering if I could get this done in one SELECT statement.

Mycroft Holmes18-Aug-11 22:19

18-Aug-11 22:19

Kyudos wrote:
Crappy reasons

No worse than some I have heard!

Try this, uses join and a sub select.

SQL

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

plz brack my licence

saqibali211918-Aug-11 8:08

saqibali2119

18-Aug-11 8:08

<?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

Re: plz brack my licence

Mycroft Holmes18-Aug-11 20:41

18-Aug-11 20:41

What does brack mean? Do you mean break?

Never underestimate the power of human stupidity
RAH

Cannot start MSSQLSERVER

Blikkies17-Aug-11 22:02

17-Aug-11 22:02

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.

Mycroft Holmes17-Aug-11 23:02

17-Aug-11 23:02

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

Blikkies18-Aug-11 3:59

18-Aug-11 3:59

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.

jschell18-Aug-11 12:56

jschell

18-Aug-11 12:56

Uninstall. Re-install. Then restore from the back up.

Corporal Agarn18-Aug-11 2:02

Corporal Agarn

18-Aug-11 2:02

Has it ever worked? If this is a new install I would suggest uninstalling then reinstall.

Blikkies18-Aug-11 3:59

18-Aug-11 3:59

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.

Wendelius18-Aug-11 6:26

18-Aug-11 6:26

What error message are you getting when you try to rebuild the master?

The need to optimize rises from a bad design.My articles[^]

Blikkies18-Aug-11 21:38

Oracle Collection Objects

18-Aug-11 21:38

Hey,

Here is the link to the log with failure : [log]

Can not make sense of it.

Thanks in advance.

lionelcyril17-Aug-11 21:22

lionelcyril

17-Aug-11 21:22

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?

Wendelius18-Aug-11 8:10

18-Aug-11 8:10

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?

The need to optimize rises from a bad design.My articles[^]

lionelcyril18-Aug-11 8:21

lionelcyril

18-Aug-11 8:21

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.

Wendelius18-Aug-11 8:32

18-Aug-11 8:32

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.

The need to optimize rises from a bad design.My articles[^]

lionelcyril18-Aug-11 8:37

lionelcyril

18-Aug-11 8:37

when i store the results in the collection while i run the loop. can i save the results into the cursor.

Wendelius18-Aug-11 8:56