|
CAST ('123.234' AS Decimal(10, 2))
CONVERT(Decimal(10, 2), '123.234')
Regards
KP
|
|
|
|
|
OK Krish you great !!!
I try this code, and it's work.
But after few days, my database is update with more than one record in the same name,
I not tell you that I have another column name 'date',
Regarding I just want to display data depend on last date, like below:
name type value date
-----------------------------------------------------
a A 1 06/10/2007
a B 1 06/10/2007
a C 1 06/10/2007
a A 2 06/13/2007
a B 2 06/13/2007
a C 2 06/13/2007
the code is:
select name,date=max(date),
sum(case when type='A' then value else 0 end) as 'A',
sum(case when type='B' then value else 0 end) as 'B',
sum(case when type='C' then value else 0 end) as 'C'
from table1
group by name
the result is:
name date A B C
-----------------------------------------
a 06/13/2007 3 3 3
I'm expecting like this:
name date A B C
-----------------------------------------
a 06/13/2007 2 2 2
Thank you.
B.regards
|
|
|
|
|
Hi
I created two tables in my database as
topcs(topicid,topic,createdate,authorname)
posting(postid,content,postcreatename,postdate,topicid)
now i want to display all records in topics and( number of posts ,last post name, last post date) of each topic
how to write query for this plz help me
-- modified at 1:38 Thursday 5th July, 2007
Haritha
|
|
|
|
|
can you pls provide sample data for these tables
Regards
KP
|
|
|
|
|
Hi
In my database i created two tables . One table contains topicid as primary key and another table contains same field as foreign key . Now i want to count the records of each topic in second table and i have to display first table information in grid along with count of corresponding topic .
how to write query for this .....plz help me
Thanks in advance
Haritha
Haritha
|
|
|
|
|
SELECT topicid, count(*)
FROM tblA a
INNER JOIN tblB b ON a.topicid = b.topicid
GROUP BY topicid
this query works in SQL Server
-- modified at 3:31 Thursday 5th July, 2007
Regards
KP
|
|
|
|
|
Try this.. u may get some ideas.. (I haven't tested this code yet.)
<br />
SELECT t1.Field1, t1.Field1, COUNT(t2.Field2) <br />
FROM table1 t1 <br />
INNER JOIN<br />
table2 t2<br />
ON t1.Field1 = t2.Field1<br />
GROUP BY t1.Field1, t1.Field1<br />
<code> <br />
<br />
<br />
<br />
<div class="ForumSig">Thanks and Regards,<br />
Michael Sync ( Blog: <a href="http://michaelsync.net">http:
<br />
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read <a href="http://www.codeproject.com/info/supporter.asp#supporters">it</a> here. Thank you. :) </div>
|
|
|
|
|
When I call the DB2 procedure GET_DBSIZE_INFO by the code following, the application always catch error as : ERROR [55032] [IBM] SQL1224N, and then the db2 server automated shutdown.
string connstr = String.Format("DATABASE ={0};UID=db2admin;PWD=db2admin", DBName.Trim());
DB2Connection conn = new DB2Connection(connstr);
DB2Parameter[] paras = new DB2Parameter[]{new DB2Parameter("@SNAPSHOTTIMESTAMP",DB2Type.Date),
new DB2Parameter("@DATABASESIZE",DB2Type.BigInt),
new DB2Parameter("@DATABASECAPACITY",DB2Type.BigInt)
};
paras[0].Value = DBNull.Value;
paras[1].Value = DBNull.Value;
paras[2].Value = DBNull.Value;
paras[0].Direction = ParameterDirection.Output;
paras[1].Direction = ParameterDirection.Output;
paras[2].Direction = ParameterDirection.Output;
conn.Open();
DB2Command com = new DB2Command("GET_DBSIZE_INFO", conn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddRange(paras);
com.ExecuteNonQuery();
DBSizeInfo sizeinf = new DBSizeInfo();
sizeinf.Size = (int)paras[1].Value;
sizeinf.Capacity = (int)paras[2].Value;
conn.Close();
Nothing is impossible
|
|
|
|
|
I have a problem with the GetChanges method. Here's what happens:
I modify a row in my typed dataset, I do NOT call AcceptChanges nor Update.
Afterwards, I call GetChanges() on my dataset and it returns null!
I've made a Quick Watch on my dataset and the new value is effectively
there, but the state of the modified row is Unchanged.
What am I missing?
Thanks!
-- modified at 15:54 Wednesday 4th July, 2007
Update: the value I modify is binded to a Windows Form control (NumericUpDown).
I've noticed that if I modify the value programmatically, the RowState is
Modified, which is what I want, obviously.
So does anybody know why modifiying a value with a GUI Control does not
mark the row as Modified whereas modifying it programmatically does?
Thank you!
|
|
|
|
|
This seems to be a bug. Here's how to solve it:
BindingContext[dataset, datatable].EndCurrentEdit();
|
|
|
|
|
I am trying to insert data in a table, using a sproc. I want to do this because the current code is doing this through a foreach loop. I want to get rid of it. Here is the code I made (this is test code, not the real one):
using System;
using System.Data;
using System.Data.SqlClient;
namespace DataSetTest
{
class Program
{
static void Main(string[] args)
{
DataSet ds = GetDataSet();
SqlConnection conn = new SqlConnection("Server=DEVRDABTS01\\MSDEVPARA;database=sandbox; Integrated Security=SSPI");
conn.Open();
SqlCommand comm = new SqlCommand("usp_DatasetTest_Insert", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = comm;
da.Update(ds);
}
public static DataSet GetDataSet()
{
DataSet returnDS = new DataSet();
DataTable dt = returnDS.Tables.Add("DatasetTest");
dt.Columns.Add("dateValue", typeof(DateTime));
dt.Columns.Add("clientId", typeof(string));
dt.Columns.Add("stringValue", typeof(string));
dt.Columns.Add("dontInsertIfNull", typeof(string));
DataRow dr;
for (int i = 0; i < 10; ++i)
{
dr = dt.NewRow();
dr[0] = DateTime.Now;
dr[1] = i.ToString();
dr[2] = "some string";
dr[3] = (i % 2) == 0 ? "" : "this is not null";
dt.Rows.Add(dr);
}
return returnDS;
}
}
}
I get the following exception at the line marked above:
System.InvalidOperationException was unhandled
Message="Update unable to find TableMapping['Table'] or DataTable 'Table'."
Source="System.Data"
StackTrace:
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at DataSetTest.Program.Main(String[] args) in c:\projets\Visual Studio 2005\Projects\DataSetTest\DataSetTest\Program.cs:line 21
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Is it possible to make this work?
-----
If atheism is a religion, then not collecting stamps is a hobby. -- Unknown
|
|
|
|
|
|
Hey guys,
I'm trying to figure something out with this query I'm writing up.
The schema of the research DB is as follows:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(*PaNum, *AcNum)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)
Basically the question I'm trying to answer is:
Which academics have published a paper with at least one academic from Tasmania? Return their academic number, given name, family name, and department number.
(Hints: Use LIKE '%Tas%' to build the query in stages. If a paper is published by one academic and he (she) is from Tasmanian, do not output the academic for this reason. If a paper is published by two or more academics and at least one of them are from Tas, then output them.)
And so far after one week of doing my head in I have this:
select acnum,deptnum,famname,givename
from academic
where deptnum = any (select DeptNum
from department
where State LIKE '%Tas%'
or State LIKE '%TAS%');
I'm trying to select all the books with authors > 1 then elminiate all that have no tasmanians but I'm not sure how to write it up in sql. I was hoping someone can help me.
I also wanted to know which word or sign to use for "same" like if i want to search for authors with the same family name.
Thanks in advance
|
|
|
|
|
Hi Diana
Sounds like homework - so I'll try to walk you through the solution, rather than just give it to you on a plate:
The way that I would approach this would be to build a select statement that returns all of the "Authors" who reside in Tasmania (you will need to join between 3 tables).
I would then substitute that select statement into the from-clause of the following:
select distinct Academic.AcNum, Academic.GiveName, Academic.FamName,
Academic.DeptNum
from (
--Your tasmanian-authors select statement goes here.
select TasmanianAuthor.PaNum, TasmanianAuthor.AcNum
from Author as TasmanianAuthor
inner join ...
where ...
) X
inner join Author
on Author.PaNum = X.PaNum
and Author.AcNum <> X.AcNum
inner join Academic
on Academic.AcNum = Author.AcNum
order by Academic.AcNum I know that embedding a select-statement within a from-clause is supported by at-least SQL-Server, Oracle, and DB2. If your database doesn't support this technique then try looking-up "correlated sub-queries" to find a possible alternative way of achieving the same result.
The "distinct" clause will be required where an academic has authored several papers, or if a paper has been authored by more than 2 academics.
If you want to learn more about SQL programming (and you should because SQL allows you to do some reeeeally powerful stuff) then there are plenty of good articles on the web or books in the bookshops. My own favorite is Joe Celko's SQL for Smarties.
Hope that helps.
Andy
|
|
|
|
|
Thank you so much Andy
It worked great. Now I'm having issues with displaying people who have the same family name. I tried both ways but my query just times out.
Is there something wrong with this query:
Select famname,givename
From academic
Where famname=famname;
I think I'm gonna go track down that book. I really do want to learn SQL.
Thanks so much for your help
Diana
|
|
|
|
|
Satips wrote: Then how will you program and create query Diana.
I guess because she said she wanted to learn SQL.
|
|
|
|
|
Hi Diana
Academics with the same family name would be:
select Academic.AcNum, Academic.FamName, Academic.GivenName, Academic.DeptNum
from Academic
where Academic.FamName in (
select FamName
from Academic
group by FamName
having count(*) > 1)
order by Academic.AcNum The sub-query gives you a list of all of the family names where there are two-or-more researchers.
This type of query is often used to identify duplicate records in tables.
Regards
Andy
|
|
|
|
|
Thanks Andy
You're a Star...
|
|
|
|
|
I created a little query to assist our support guy in a workaround. The task was to create a backup table for all vendors not flagged for VAT, then flag all vendors for VAT, run an export to Pastel, then use the backup table to reset all the originally non-VAT vendors.
My manager called me over because step 2 wouldn't execute, it complained that the table VendorBak doesn't exist. When he added the create table statement, step 1 and 2 worked. How can the select into work when the table exists? Is there some strange condition that allows this?
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbak
|
|
|
|
|
The "select .... into" statement creates its own table.
You should either remove your explicit "create table" statement, or replace your "select .... into" statement with an "insert .... select" statement.
|
|
|
|
|
IF statements only control the next statement. They do not act as a block, despite your indentation. To control more than one statement, use BEGIN/END.
As a result, your code really looks like this:
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbak That means that when @stepid is 1, it will create the table, then do the SELECT INTO, then update, then drop the table.
|
|
|
|
|
Hi,
I have a T-SQL script file which i want to run using C#. Can any body please tell me how can i do it?
Regards,
Wasif.
|
|
|
|
|
Running SQL Scripts with a .NET Application[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
|
That wasn't on the page I linked to. Which page did that link come from?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|