|
Hi, I have to show a filesystem on a page. The information is stored in a table where I have ID(PK), Name, Parent, Order
001, Node1, NULL, 1
002, Node2, NULL, 2
003, Node3, 001, 1
004, Node4, 001, 2
005, Node5, 002, 2
I need to display the results as it should be seen:
+Node1
Node3
Node4
+Node2
Node5
The query I have is like this:
SELECT A.*
FROM TableFS A
LEFT OUTER JOIN TableFS B
ON A.ParentID = B.ItemID
ORDER BY A.ParentID, B.Order
The result is similar to what I should get, but similar is not enough... at least it's what they tell me... 
If anyone can help... 
Thanks...!
|
|
|
|
|
SELECT A.*
FROM TableFS A
INNER JOIN TableFS B ON A.ItemID = B.ParentID
ORDER BY A.ParentID, B.Order
Regards
KP
|
|
|
|
|
Sorry, but this is not even close. Items without parents have value in NULL, so your querry dosen't take those nodes in charge, and gives me duplicated values anyway.
Thanks for your time 
|
|
|
|
|
I have a database column that is variable character 1500, If I try to read a record with a value whose length is over 200 characters I get an empty string and the debugger returns the following error:
This expression causes side effects and will not be evaluated
|
|
|
|
|
Hi,
I've been using SQL Server 2005 for almost 2 years now. I want to know if there is anything to look at for the new SQL Server?
Regards
ma se
|
|
|
|
|
|
I haven't heard of SQL Server 2008
|
|
|
|
|
zhousz wrote: I haven't heard of SQL Server 2008
Codenamed Katmai - The CTP was released earlier this month.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I have a central database in a server computer(In sql express 2005).
Two or more computers can connect to the central database.When a terminal computer is doing some operation on some data, how can i ensure that the other terminal
in the same network doesnot change the data at the very same moment?
X
|
|
|
|
|
Please don't crosspost. It's not very polite.
|
|
|
|
|
SQL Server automatically ensures that data modification statements (INSERT, UPDATE, DELETE) are atomic - a statement that affects multiple rows will either modify all of them, or none (if some error occurs, the partial changes will be rolled back).
If you need to execute multiple statements in the same logical operation, and have the results consistent, you can use a transaction. If submitting all the statements in a single batch, or in a stored procedure, you can use the BEGIN TRANSACTION/COMMIT TRANSACTION/ROLLBACK TRANSACTION statements. If you can't execute it all in one batch, or need to call multiple stored procedures, use a client-side transaction (e.g. SqlConnection.CreateTransaction in .NET languages)
There are a number of transaction isolation levels which control which updates from other users can appear between two SELECT statements in the same transaction. Basically, READ COMMITTED (the default) allows changes to be made between two SELECTs - the second statement may see results that another, completed, transaction has made. REPEATABLE READ prevents updates and deletes until your transaction is committed. SERIALIZABLE also prevents inserts that would change your resultset.
Transactions using the built-in transaction features should be kept short. Holding a transaction open for too long prevents other users getting work done. If you need your application to allow users to read a record, make changes to it over a period of time, then eventually commit the change, you need to either come up with a manual record-locking scheme, or somehow check to see if changes have been made since the data was read and either automatically merge the changes made by each user or simply alert the user that the database has been changed.
One of the simplest ways to detect that the data has changed since being read is to use a rowversion column, which changes every time the row is updated.
|
|
|
|
|
Hi
I want to insert records as CustID & Custname in Customer table.
custid Interger (autoincrement)
Custname varchar2
& want to display last added record .
Thnx
|
|
|
|
|
Care Career wrote: I want to insert records as CustID & Custname in Customer table
Insert into Customer (Custname) values ('Value')
Care Career wrote: & want to display last added record
select top 1 * from Customer order by CustID desc
I Love SQL
|
|
|
|
|
Blue_Boy wrote: select top 1 * from Customer order by CustID desc
if you use a simple select like this, you could get the wrong record when by many users at the same time, instead you can use:
select * from Customer where CustID = SCOPE_IDENTITY()
Alexei Rodriguez
|
|
|
|
|
@@IDENTITY returns last generated auto increment number
with which you can get the last custid inserted into the table.
Regards
KP
|
|
|
|
|
Krish - KP wrote: @@IDENTITY returns last generated auto increment number
Yes, but for what table? If you have a large multi user database handling many transactions at once there is no guarantee that it will return the identity of the last row inserted in the scope your running in. It might return the identity for some other code running on some other table that just happens to be running at the same time as your code.
You should use SCOPE_IDENTITY() to ensure that you get the identity of the last insert in your scope (and no one else's scope)
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi,
I want to create an user in sql server.
My program create a database, then it create a user for that database .
The problem is that I can't see the tables from the database with the user.
please help.
thanks.
|
|
|
|
|
Sounds like you need to give database reader role to your new user.
Ben
|
|
|
|
|
SQL Server has security. In order to be able to SELECT from tables, the user account in the database has to be granted permissions. Use the GRANT statement to set the permissions.
I normally make ordinary users members of the public role and limit that role to only being allowed to do the minimal set of operations that the application requires. By default, public has no permissions to do anything.
|
|
|
|
|
|
Hi all!
There is a question. i am facing difficulting in writting a query for selecting record in database.i am explaining whole scenerio of current problem:
i have a table StockDelta having attributes as follows:
stockDeltaID(primary key),
productID(forighn key from product table(productID, productName, totalQuantity etc) ),
Delta(the change in quantity of stock of a product, if the quantity increases the value of delta will be positive and if the quantity decreases value of delta will be negative),
isStockAdded(bool value indicating if the changed amount was basically the added stock), isSecondarySales(bool value indicating if the changed amount was basically the sencondary sales),
isDamages(bool value indicating if the changed amount was basically the damaged amount),
isFreeGifts(bool value indicating if the changed amount was basically the amount of freeGift)
now i have to generate a Stock report(using crystal reports), the report will take a parameter in case of daily report and two parameters in case of monthly report. the information that we want to show in report is as follows:
The columns of report should be
"Opening Stock"(representing quantity at start of month/day). we will get this column by Sum(delta) where date < pdate-1 //pdate is parameter date
"Stock Added"(representing quantity purchased during month/day), we will get this column by Sum( Delta) where date = pdate and isStockAdded = true
"Secondry Sales"(representing the sale out of a produt on a specific month/day), we will get this column by Sum(delta) where date = pdate and isSecondarySales = true
"Closing Stock"(representing the net quantity of a product in stock on a specific month/day), we'll get this column by Sum (delta) where date < pdate
"Damages"(from 1st of a month till a specific day or in a whole month), we get this column by quering Sum(delta) where date between 1st of month till pdate and isDamages = true
"FreeGifts"(representing the quantity of freegifts from 1st till that day of a month or for whole month). we will get this column by quering Sum(quantity of Freegifts) where date between 1st of month till pdate and isFreeGift = true
hope you people have grasped what i said.. i have tried my best to figure out query for this report but maybe at this student level i am unable to come with the right solution. i am stuck on it and request you people to please consider the problem if you get some time.
Thanks in advance
shaz jazz
|
|
|
|
|
Hi Shaz
I have done the SQL for the opening-balance and stock-added results:
SELECT SD.productId,
SUM(CASE
WHEN SD.deltaDate < @StartDate THEN SD.Delta
ELSE 0
END) AS OpeningStock,
SUM(CASE
WHEN SD.deltaDate >= @StartDate AND SD.isStockAdded = 1 THEN SD.Delta
ELSE 0
END) AS StockAdded
FROM StockDelta SD
WHERE deltaDate < @EndDate
GROUP BY productId
ORDER BY productIdI'll let you figure out how to do the remaining columns.
The @StartDate and @EndDate bits refer to the parameters that you need to supply. The above should work for SQL-Server and Oracle. If you are using MS-Access then you will need to use the IIF function. An alternative approach would be to use sub-queries.
Regards
Andy
|
|
|
|
|
Hi,
I have a problem with a user.
I have the connection string:
MsSQL_conn1.ConnectionString = "Server = " & frmMain.txtMyServer.Text<br />
MsSQL_conn1.ConnectionString += "; User=RWIN" & "; Password=AVM" & "; Database =" & frmMain.txtMyDatabase.Text & ";Connect Timeout=30; Integrated Security = SSPI"
When I try to connect from my computer where I have installed the Sql Server it works.I can log on Sql Server with my user.
But when I try to connect with my program to another computer where is installed Sql Server I have the error:
Login failed for user 'LEXI-DEVELOP4\Guest'.
Please help.
Thanks
|
|
|
|
|
|
Can you please tell me about a query which I could use for the purpose of removing all the repetition(Repeated Records) in a table. I am trying with this one but its not working properly.
delete from EXCHANGE_STOCKS
where STOCK_ID not in (select distinct SYMBOL,EXCHANGE,(select top 1 STOCK_ID from EXCHANGE_STOCKS where SYMBOL = es.SYMBOL and EXCHANGE = es.EXCHANGE) as STOCK_ID
from EXCHANGE_STOCKS as es)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
