|
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)
|
|
|
|
|
|
Many thanks Mika for your quick reply, but this determines the max. number of connection currently established with the server.
Actually I want to know the max. number of possible concurrent users, beyond that number database may not listen about the user/request.
I am not sure but in some forum it was mentioned around 12000. Is there any such number of possible users?
Regards,
Navin
|
|
|
|
|
Sorry, but I don't quite follow. From the settings documentation:
"Determines the maximum number of concurrent connections to the database server...This parameter can only be set at server start"
So my understanding is that with this startup parameter you define the maximum number of concurrent connections that can be taken to the server (well actually max_connections-superuser_reserved_connections). As far as I know, the actual maximum you can define is operating system dependent.
The setting itself is defined in postgresql.conf.
|
|
|
|
|
Navin C. Pandit wrote: Actually I want to know the max. number of possible concurrent users, beyond
that number database may not listen about the user/request.
You are mixing terms.
User != connections.
A connection represents a TCP connection to the database.
A "user", say for example a real person running a GUI can have 1 or more connections to a database.
|
|
|
|