|
Hi everyone!.
I have 2 columns in a table say col1 and col2.
Sample Data:
===========
col1 col2
60 1000
20 0
30 0
40 1500
20 0
70 0
80 0
I need a query that will display the following output:
col1 col2 col3
===================
60 1000 1000
20 0 980
30 0 950
40 1500 1500
20 0 1480
70 0 1410
80 0 1330
Description:
============
if col2 not equal to ZERO then
col3=col2.
else
col3=previous col3 - current col1
I don't need cursors.
Thanks in advance.
Regards,
Arun Kumar.A
|
|
|
|
|
Arun.Immanuel wrote: I don't need cursors.
You mean you don't "want" cursors. "Need" and "want" are different concepts.
The only way I can see to do this without cursors is in the client side code. i.e. in the application that calls the SQL.
Now, two importance concepts about databases here:
1. The data is essentially unordered. The implementation of the database may appear to give the data an order, but that is just incidental. You should not rely on that order. If you want guaranteed order then you have to define something in the table that you can ORDER BY
2. When retrieving data, an individual row has no concept of the rows around it - or even if there are any rows around it - so it will not be able to subtract anything from a column on the "previous" row. (There is no order, remember, so the concept of "previous" or "next" does not apply)
|
|
|
|
|
Thank U very much.
I have done that with the .NET code.
I just want to know , if there exists any query.
Regards,
Arun Kumar.A
|
|
|
|
|
Hi,
Thank U very much for Ur reply.
Sorry for not explaining the entire scenario.
I know that the rows in SQL are not stored in order.
But, this is what I am doing:
I am querying the above 2 columns using "ORDER BY Date".
Here "Date" is another column based on which I am retrieving the records.
So, the output will definitely be in order.
Thus we can refer the value of row(n) from row(n+1) since I am having
the column Date with all the dates(i.e.no date missing).
And the query would be:
select A.col1,A.col2,B.col2
from tblName A,
( select Date-1 "Dte",col2 from tblName )B
where A.Date=B.Dte
Here B.col2 will be shifted 1 level with respect to A.col1.
I have not included the order by clause here.
So, I will be able to refer previous row from the current row.
But, I do not know how to implement the required output as mentioned above.
Any help would be appreciated.
Regards,
Arun Kumar.A
-- modified at 6:41 Sunday 22nd April, 2007
|
|
|
|
|
Have you been successful yet? I'd like to see your solution if you have.
I toyed with this for a couple of minutes, but quickly realized that your algorithm can best be stated in pseudo-code like this:
<code>getCol3( rownum )
{
if( 0 = col2(rownum) ) Then
return getCol3(rownum-1) - col1(rownum);
else
return col2(rownum);
}</code>
which is a *recursive* procedure. Some DBMs support recursive queries (Oracle and DB2 for instance), but I'm not sure even they would work here as they tend to recurse on *existing* field values, not on generated values like you want here. Check out the CONNECT BY clause if you're interested.
I'm inclined to think Colin is correct here; I SERIOUSLY doubt this can be done without procedures. I have similar requirements in my tables, but have resigned myself to putting the table updates into loops within code.
Let us know if you do figure out a clever way.
Good luck.
David
|
|
|
|
|
Hi All,
I have another problem with calling manage code from dll.
I do the following:
1. Create dll project in the VS2005, add /clr option under project property->C++->general
2. Add header file to the project with interface:
<br />
#define Base_API __declspec(dllexport)<br />
<br />
class BaseI_SqlDbConnection;<br />
class Base_API Base_SqlDbConnection<br />
{<br />
public:<br />
Base_SqlDbConnection(); <br />
BaseI_SqlDbConnection *m_pi;<br />
};<br />
3. Add define of class to the .cpp file. It is the simple wrapper on the SqlClient.
<br />
using namespace System;<br />
using namespace Data;<br />
using namespace SqlClient;<br />
<br />
#ifdef _MANAGED<br />
#pragma managed(push, off)<br />
#endif<br />
BOOL APIENTRY DllMain( HMODULE hModule,<br />
DWORD ul_reason_for_call,<br />
LPVOID lpReserved<br />
)<br />
{<br />
return TRUE;<br />
}<br />
#ifdef _MANAGED<br />
#pragma managed(pop)<br />
#endif<br />
class BaseI_SqlDbConnection<br />
{<br />
public:<br />
BaseI_SqlDbConnection()<br />
: m_SqlDbConnection(gcnew SqlConnection){}<br />
<br />
gcroot<SqlConnection ^> m_SqlDbConnection;<br />
};<br />
Base_SqlDbConnection::Base_SqlDbConnection() : m_pi(new BaseI_SqlDbConnection)<br />
{<br />
}<br />
4. Call the class from MFC application looking like this:
Base_SqlDbConnection test;
With help of debugger, I found that the BaseI_SqlDbConnection()
constructor doesn't want to call and object m_SqlDbConnection could not be created.
I guess that the problem is in the managed code, but I am newbie for this. Please help
me to resolve the problem or point me to the article about this.
|
|
|
|
|
I want to declare a datatype like this:
<br />
create table abc<br />
(<br />
days enum('sat','sun','mon')<br />
)<br />
How can i do that? I know that mysql, oracle supports this type of database but what about sql server 2005? Also can i use user defined datatype for this kind of problem? if so then please tell me how.
Thanks.
TFR
|
|
|
|
|
Hi,
there is no enum datatype in MS SQL. You may use a check
constraint. Something like that:
CREATE TABLE tblTest (<br />
ID INT PRIMARY KEY IDENTITY,<br />
ANormalColumn VARCHAR(10) DEFAULT ('') NOT NULL,<br />
RestrictedColumn INT<br />
CHECK (RestrictedColumn IN (1,2,3)<br />
)
The first of the following inserts is OK, while the latter
throws an error:
INSERT INTO tblTest (ANormalColumn, RestrictedColumn) VALUES ('A', 1)<br />
INSERT INTO tblTest (ANormalColumn, RestrictedColumn) VALUES ('B', 4)
-sa
--
http://www.livingit.de
http://www.not2long.net
|
|
|
|
|
Hi, i have a DB table which has just only one column, i have an integer column field in it, i want to know the query for retriving not the highes number (i mean as select max of the column) i want the select the number comes before the max number, 
Thanks alot
Hamody
|
|
|
|
|
Here you go...
SELECT Max(myIDColumn) FROM myTable
This will return the highest number in a column from a table.
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
hello, thanks i know that
SELECT Max(myIDColumn) FROM myTable
will return the highest or max number, i want to get the number comes before the max number not the max itself 
Thanks alot
Hamody
|
|
|
|
|
Sorry didn't read you post very well.
SELECT Max(myIDColumn - 1) FROM myTable
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
thanks a lot there, merci beacoup ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
Thanks alot
Hamody
|
|
|
|
|
I think this will work if the number u require is always
1 less than the max number.
Here is the command for the column which have random values.
select max(colName) From tblName where
colName not in( select Max(colName)From tblName)
Regards,
Arun Kumar.A
|
|
|
|
|
I am writing two select statements in stored procedure. In front end i am calling this stored procedure and filling the dataset. what will will i have in the dataset.Output of both the select statements or any one. If any one then which select statement will give output to the dataset.
Mohinder Singh
|
|
|
|
|
Both result sets from the stored procedure will be present in the dataset - That is why it has a collection of Tables.
|
|
|
|
|
Hi All
I am writing an app that is a time tracking systray utility but the scope has grown. The Version 1 release used a local Access DB to store Data but now management want to sync it centrally to an SQL server so group wide reports can be pulled.
In short I am looking for direction on the best way to tackle this.
SQL Sample Table
CustomerID (int) Identity = Y (i.e. Auto incrementing)
CustomerName (Text 50)
DeleteFlag (int)
RecordTimeStamp (timestamp)
MS Access
CustomerID (int) I Get the SQL ID for the record and put here)
CustomerName (Text 50)
DeleteFlag (int)
My first thought (well within the first 10) was to check the SQL timestamp and only add records that were newer that the last stored timestamp.
Thoughts
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
japel wrote: The Version 1 release used a local Access DB to store Data but now management want to sync it centrally to an SQL server so group wide reports can be pulled.
At that point why even use the Access database at all?
japel wrote: SQL Sample Table
CustomerID (int) Identity = Y (i.e. Auto incrementing)
CustomerName (Text 50)
DeleteFlag (int)
RecordTimeStamp (timestamp)
Don’t forget to add a developerID to that list, wont be long and management will want to know who’s working on which projects.
|
|
|
|
|
hi all,
i want to call the import external data window using this Microsoft.Office.Interop.Access library. does any one know this?
my application has a facility to get external data to a access database. for that i need this window to called and opened.
please help!!!!
Asanka
|
|
|
|
|
Hi All,
I am newbie for .net. I have created the simple console application and trying to create connection to SQL database.
Here is my code for console app:
<br />
#include "stdafx.h"<br />
#include "vcclr.h"<br />
#include "atlstr.h"<br />
#using "mscorlib.dll"<br />
#using "System.dll"<br />
#using "System.Data.dll"<br />
<br />
using namespace System;<br />
using namespace System::Data;<br />
using namespace System::Data::SqlClient;<br />
<br />
int _tmain(int argc, _TCHAR* argv[])<br />
{<br />
CString hello("Hello");<br />
gcroot<SqlConnection ^> m_SqlDbConnection = gcnew SqlConnection(gcnew String(hello)); <br />
return 0;<br />
}<br />
I have the following exeption
An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll
Additional information: Format of the initialization string does not conform to specification starting at index 0.
on the string
gcroot<SqlConnection ^> m_SqlDbConnection = gcnew SqlConnection(gcnew String(hello));
Please help me to resolve the problem or point me to the article.
P.S. I found that CString defined as in the atlstr.h
typedef CStringT< TCHAR, StrTraitATL< TCHAR, ChTraitsCRT< TCHAR > > > CAtlString;<br />
typedef CAtlString CString; Maybe it helps.
-- modified at 14:32 Friday 20th April, 2007
|
|
|
|
|
It failed because the connection string you provided it, "Hello", isn't a valid description of what kind and which database you want to connect to. You might want to have a look at ConnectionStrings.com[^] for many examples of what connection strings for various databases are supposed to look like.
My C++ is really rusty, but it loks like you need to back away from the database stuff for now and start by learning the basics of CLI before you jump into the deeper end of the .NET Pool.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
We can Build our DATABASE, I just wanted to know a brief about this ,Though not to create but to have general Idea about it.
Develop2Program & Program2Develop
|
|
|
|
|
I don't understand what you mean by "Build" a database as opposed to "create" a database?
|
|
|
|
|
Ok , Sorry for ambiguity.
what I mean here is: we can develop our own datsbase same like that of MS Access
Develop2Program & Program2Develop
|
|
|
|
|
Navneet Hegde wrote: we can develop our own datsbase same like that of MS Access
If you want. I don't, personally, see the need as many highly skilled people who are well versed in the theory and implementation of database systems created things like MS Access, SQL Server and the like. Microsoft spends billions of dollars on creating those pieces of software and I'm sufficiently aware of my own abilities to realise that I could no where near match what they do.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
