|
The example is simply a home sales table set up this way
ID
DateSale
SaleAmount
The table tracks all the sales that takes place in a community for the homes. A single home can be sold numerous times over its lifespan. So the challenges is to compare one sale amount to the next by date of sale. I need to calculate the difference between each sale and determine the % increase or decrease.
I hope this helps.
Dale A. Young
|
|
|
|
|
Hello,
I have two tables, A like number, value1 value2, and B like number, aktiv, value2, ...
As I want to have all lines of table A, even number does not exist in table B I made a command 'select from A left outer join B on A.number = B.number'
Works fine, but if I try "where B.aktiv != '-1'", the NULL-Values also disapears. I also tried "(where B.aktiv = '1') or (B.aktiv = NULL)" - it doesn't work.
What am I doing wrong?
Thanks, Gerhard
|
|
|
|
|
NULL cannot be compared to any value, so it is neither = -1 of != -1 or even =Null.
If you need to find NULL values you must use IS NULL (Where B.aktiv IS NULL)
NOTE: If you turn the ANSI_NULLS option off, Transact SQL will return comparisons for null,
SEE Books Online for more info.
|
|
|
|
|
It seems that columns having NULL values in B.aktiv is getting eliminated. You can try the following: -
WHERE ISNULL(B.aktiv,1)!='-1'
Let me know if its working for you.
|
|
|
|
|
How Can we prevent SELECT statements from being returned in the dataset , because we may need to execute some SELECT just for other purposes than reporting.
|
|
|
|
|
Just collect the columns being included in the select list in come variables. See below: -
DECLARE @v1 int,@v2 int
SELECT @v1=number1,@v2=number2 FROM tblA
|
|
|
|
|
Hi 
im trying to Limit the number of results retrived by a Query so for Example i want to retrive the first 30 and then navigate throw the results 30 by 30.
the problem in my Query that i cant used the TOP 30 command because im Ordering my results with date then status plus alot of ordering.
and this ordering will cause the retrived results to be retrived in a random ID_no order as one of my friends suggested that i use the TOP 30 along with
Where Id no > X Order BY Id_no as in the example below.
but i think if there is no Command in MS Access or that i dont know it because of my lack of knowledge i will have to order by my Where Criteria along with the TOP 30 command.
Another Sol. just popped to my head is to save the results to a Dataset and then using a Table in the Dataset i can just retrived Index based.
i guess i will try the Dataset sollution also if there is any better one plz Help.
By the way im using ODBC COnnection, OdbcCommand and ODBCDatareader.
Thanks in Advance.
Example for Retriving by the TOP and Where command:
1)
Select TOP 30 A.ID_no , .......etc<br />
From A , ......etc<br />
Where A.ID_no > <big>1</big><br />
Order by ID_no ;
2)
Select TOP 30 A.ID_no , .......etc<br />
From A , ......etc<br />
Where A.ID_no > <big>30</big><br />
Order by ID_no ;
and i continue like that.
----------------------------------
Have fun and Stay good
-- modified at 4:14 Saturday 25th November, 2006
|
|
|
|
|
Can you please clarify what is your criteria of retriving the TOP 30 records? Is it that, you want top 30 records as they appear in the table in the order of you primary key column? Is A>ID_no your primary key column? Can you please try the following and let me know: -
SELECT A.ID_no , .......etc
From A , ......etc
Where 30>=(SELECT COUNT(*) FROM A a
WHERE A.ID_no>=a.ID_no)
Order by ID_no,........etc
|
|
|
|
|
Hello,
In the administration console, I would like to use some specific performance counters :
those link with the .NET Framework Data Provider for SQL Server
But when I select it in the drop down list, I can select counters but the add button is disabled.
Is anybody can explain me.
Thanks
|
|
|
|
|
Do You have a running instance of a program using the provider?
Did you select that program instance first?
|
|
|
|
|
Hi,
I am setting up a view. One of the fields that I am selecting for the view can ONLY return the values: NULL or POSITIVE Integers.
What I want is when that particular field, in this case (inQuoteID), is NULL, the view should return a value of 0. If the value is NOT NULL, then leave it as it is.
Here's the code for the view so far :
<br />
SELECT dbo.vw_ProjectStage.vchProjectStage, dbo.tblRequest.inRequestID, dbo.tblRequest.vchRequestSummary, dbo.tblRequest.inTimeEstimate, <br />
dbo.tblRequest.inOrder, dbo.tblRequest.dtCompletionDate, dbo.tlkpProgress.vchProgressName, dbo.tlkpImportance.vchImportanceName, <br />
dbo.tblRequest.vchRequestNumber, dbo.tlkpRequestType.vchRequestTypeName, dbo.tlkpRequestType.inRequestTypeID, <br />
dbo.tblRequest.inQuoteID,<br />
<br />
CASE WHEN dbo.tblRequest.inQuoteID IS NULL THEN<br />
dbo.tblRequest.inQuoteID = 0<br />
ELSE dbo.tblRequest.inQuoteID = ''<br />
END AS dbo.tblRequest.inQuoteID<br />
<br />
FROM dbo.tlkpProgress INNER JOIN<br />
dbo.tblRequest ON dbo.tlkpProgress.inProgressID = dbo.tblRequest.inProgressID INNER JOIN<br />
dbo.tlkpImportance ON dbo.tblRequest.inImportanceID = dbo.tlkpImportance.inImportanceID INNER JOIN<br />
dbo.vw_ProjectStage ON dbo.tblRequest.inStageID = dbo.vw_ProjectStage.inStageID INNER JOIN<br />
dbo.tlkpRequestType ON dbo.tblRequest.inRequestTypeID = dbo.tlkpRequestType.inRequestTypeID<br />
Thank you!
|
|
|
|
|
You could always replace the CASE statement with a COALESCE so that it becomes:
COALESCE (dbo.tblRequest.inQuoteId, 0)
Coalesce simply searches the items from left to right, looking for the first none NULL items. So, if inQuoteId is null, it uses 0 else it uses inQuoteId.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks man!
The View works. I modified the code so that it looks like this :
<br />
SELECT dbo.vw_ProjectStage.vchProjectStage, dbo.tblRequest.inRequestID, dbo.tblRequest.vchRequestSummary, dbo.tblRequest.inTimeEstimate, <br />
dbo.tblRequest.inOrder, dbo.tblRequest.dtCompletionDate, dbo.tlkpProgress.vchProgressName, dbo.tlkpImportance.vchImportanceName, <br />
dbo.tblRequest.vchRequestNumber, dbo.tlkpRequestType.vchRequestTypeName, dbo.tlkpRequestType.inRequestTypeID,<br />
COALESCE(dbo.tblRequest.inQuoteID, 0) AS "inQuoteID" <br />
<br />
FROM dbo.tlkpProgress INNER JOIN<br />
dbo.tblRequest ON dbo.tlkpProgress.inProgressID = dbo.tblRequest.inProgressID INNER JOIN<br />
dbo.tlkpImportance ON dbo.tblRequest.inImportanceID = dbo.tlkpImportance.inImportanceID INNER JOIN<br />
dbo.vw_ProjectStage ON dbo.tblRequest.inStageID = dbo.vw_ProjectStage.inStageID INNER JOIN<br />
dbo.tlkpRequestType ON dbo.tblRequest.inRequestTypeID = dbo.tlkpRequestType.inRequestTypeID<br />
|
|
|
|
|
Cool
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
See the following page
http://www.ataland.com/pages/tour/tours.aspx[^]
In this page I have four combo boxes. I fill these combo boxes one by one ,I mean for filling each one I open and close a connection. And then fill the datagrid. I want to know if I am doing right.??? How much can this degrade performance of the site ,is it worth modifing the code or it will not make much difference ???
|
|
|
|
|
If your design allows it and filling these 4 combos at the same time will happen frequently enough then you can always call a stored procedure with 4 SELECTs in it to get the data for the combos.
If you are using a DataReader you can move between the results from the SELECTs with NextResult().
If you are using a DataAdapter the tables in the DataSet will be populated in the order of the SELECTs MyDataSet.Tables[0] --> MyDataSet.Tables[3]
|
|
|
|
|
How the results are returned in dataset if I have a stored procedure that calls other stored procedures containing a select statement. does it make any difference ???
|
|
|
|
|
The results will be returned in the order in which they were encountered.
|
|
|
|
|
Hi,
Whenever I add a new VS auto-generated class they are created as Public by default. Is it possible to make some of the classes internal by default?
If I edit the code manually it will get auto-generated when I change the dataset, which I don't want to happen.
Any help much appreciated.
Andrew
|
|
|
|
|
Hi all
I have a very general query. Can anyone throw some light on the variious reasons for a failure in a database transaction and what shud b the steps for averting it and recovering for it..
Any help is appreciated..
Thanx
|
|
|
|
|
amu_sapra wrote: Can anyone throw some light on the variious reasons for a failure in a database transaction and what shud b the steps for averting it and recovering for it..
There are 152 unique reasons for a transaction to fail fatally. Which would you like to discuss?
|
|
|
|
|
i hv to make a study of the various reports so can u tell me the most obvious and common reasons..maybe the around 20 reasons....
|
|
|
|
|
amu_sapra wrote: i hv to make a study of the various reports so can u tell me the most obvious and common reasons
If they are obvious then surely you should have notice them already. If they are common surely you should have received a message already.
This is obviously a homework question. And the most common response is that you should attempt to do the work yourself and if you have any specific problems then come back and we'll help you out. You have to show you are at least willing to study on your own.
|
|
|
|
|
Hi all,
I got a problem with running distributed transactions between two servers that is connected via VPN connection (using RRAS).
after the VPN connection is connected I can connect to the remote server with Microsoft SQL server management Studio and I can run query againts server.
The problem occures when one of my store procedures start a distributed transaction and wants to insert at the remote server. (with just one insert command I can insert rows in the remote server but I can not wrap in a transaction) I got this error :
"OLE DB provider "SQLNCLI" for linked server "172.26.0.11\CENTER" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure p_InsertPointOnline, Line 54
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "172.26.0.11\CENTER" was unable to begin a distributed transaction."
I tried the following things :
1. turn off firewall in both sides.
2. Checking MS DTC security to allow outbound an inbound transactions on both sides
3. SET XACT_ABORT ON
4. SET REMOTE_PROC_TRANSACTIONS ON
the strange thing is when I switched the firewall off and reconnect the VPN I got some transactions through. But the day after that the system did not work, after a lot of time I again turn the firewall back on with some added port as exceptions. Again it works until I disconnect the VPN and reconnect. I again turn the firewall off and reconnect the VPN it works again, you see the loooop  !!!
if any one could help me with this issue I would be gratefull.
Hope you never stuck in such situations.
|
|
|
|
|
Check which ports your firewall are blocking.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
