|
It should be telling you that bizdate isn't contained in an aggregate or in a GROUP BY clause. As soon as you roll up results in an aggregate function, everything in the SELECT list must be aggregated or one of the fields used to group.
You'll have to select the value separately, unless you want to show figures grouped for each distinct value in that column, in which case you should use GROUP BY. If you want the latest date/time that this occurred, you could use MAX(bizdate) instead.
|
|
|
|
|
I have create a SQL which calculate the daily sale but it will only show 1 day when i run it
<br />
<br />
select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL <br />
from (select (select sum(totalsales+returnamt)as TotalSoft from tot_item <br />
where bizdate='2006-10-1'and (deptcode= 11 or deptcode=12 or <br />
deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17 ))as softline,<br />
<br />
(select sum (totalsales+returnamt)as TotalHard from tot_item <br />
where bizdate='2006-10-1'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode=<br />
24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline,<br />
<br />
(select sum(totalsales+returnamt)as TotalSupermarket from tot_item <br />
where bizdate='2006-10-1' and (deptcode= 30 or deptcode=40 or deptcode=50 or deptcode=<br />
60 or deptcode= 70 or deptcode=80 or deptcode= 90)) as supermarket <br />
)as t1 <br />
<br />
but i want to show like the daily result between day 1- day 15 so i create this SQL but the result not wat i expected, the RESULT should the TOTAL sum of this 15 days.... i want to show 1 by 1 for the daily sale... how?
select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL <br />
from (select (select sum(totalsales+returnamt)as sototal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 11 or deptcode=12 or <br />
deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17 ))as softline,<br />
<br />
(select sum(totalsales+returnamt)as htotal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode=<br />
24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline,<br />
<br />
(select sum(totalsales+returnamt)as sutotal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 30 or deptcode=40 or deptcode=50 or <br />
deptcode=60 or deptcode= 70 or deptcode=80 or deptcode= 90 )) as supermarket <br />
)as t1 <br />
|
|
|
|
|
Hmm...it's a bit hard to understand your query as it's too complicated.
You might want to simplify it a bit.
First, use IN instead of deptcode = 14 or deptcode = 15 and so on.
So let's format it first into:
SELECT t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline) AS TOTAL
FROM
(SELECT (SELECT SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE bizdate='2006-10-1' AND deptcode IN (11, 12, 13, 14, 15, 16, 17)) AS softline,
(SELECT SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHEREbizdate='2006-10-1' AND deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)) AS hardline,
(SELECT sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE bizdate='2006-10-1' AND deptcode IN (30, 40, 50, 60, 70, 80, 90)) AS supermarket
) AS t1
Now that it's more simplified, I found that you can actually separate softline, hardline, and supermarket into 3 separate queries to save the confusion, and we can use GROUP BY to show the sum for each bizdate.
Query1:
SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate
Query2:
SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline
GROUP BY bizdate
Query3:
SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) AS supermarket
GROUP BY bizdate
Now in your final query you can link all the total using the following:
SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM Query1
LEFT JOIN Query2 ON Query1.bizdate = Query2.bizdate
LEFT JOIN Query3 ON Query1.bizdate = Query3.bizdate
This will return your daily result of the sale.
From there you can specify the 15 days total sum.
If you want to put them all as one big query, here's the query:
SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline
GROUP BY bizdate) AS Query2
ON Query1.bizdate = Query2.bizdate
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) AS supermarket
GROUP BY bizdate) AS Query3
ON Query1.bizdate = Query3.bizdate
I hope the explanation above helps in your problem.
Note: I'm blind coding, so please check the syntax for me, ok 
Cheers,
Edbert
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
i got this error:
Server: Msg 156, Level 15, State 1, Line 8<br />
Incorrect syntax near the keyword 'AS'.<br />
Server: Msg 156, Level 15, State 1, Line 12<br />
Incorrect syntax near the keyword 'AS'.<br />
actaully where do i put the date that i want them to show on??
like
where bizdate<='2006-10-15' and bizdate>='2006-10-01which it will show between '2006-10-01' and'2006-10-15'
|
|
|
|
|
Which query did you run? Does each query run?
You can put the WHERE condition at the end of the query, before the GROUP BY if there is any.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Oh. Scratch that. I forgot to delete the AS hardline and AS supermarket.
The code below should (hopefully) work 
(Please delete the AS hardline and AS supermarket in Query2 and Query3 respectively)
SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)
GROUP BY bizdate) AS Query2
ON Query1.bizdate = Query2.bizdate
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90)
GROUP BY bizdate) AS Query3
ON Query1.bizdate = Query3.bizdate
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
yes is working!! Thank you so much ^.^v
|
|
|
|
|
please help me !!!?
I have a problem with sqlconnection definition
when I add the sqlconnection from toolbox :
like this:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Private Sub InitializeComponent()
Me.sqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa"
Me.sqlConnection1.FireInfoMessageEventOnUserErrors = False
End Sub
Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim cmduniversity As SqlCommand
Dim dtruniversity As SqlDataReader
cmduniversity = New SqlCommand("select * from university", sqlConnection1)
sqlConnection1.Open()
dtruniversity = cmduniversity.ExecuteReader
dtruniversity.Close()
sqlConnection1.Close()
End If
End Sub
End Class
in the sqlconnection.open the nullrefrence exception occures but
when i define myself this exception dosn't occure.
like this:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Private Sub InitializeComponent()
End Sub
Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
sqlConnection1 = New SqlConnection
sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa"
sqlConnection1.FireInfoMessageEventOnUserErrors = False
Dim cmduniversity As SqlCommand
Dim dtruniversity As SqlDataReader
cmduniversity = New SqlCommand("select * from university", sqlConnection1)
sqlConnection1.Open()
dtruniversity = cmduniversity.ExecuteReader
dtruniversity.Close()
sqlConnection1.Close()
End If
End Sub
ava_h
|
|
|
|
|
This is the exact error I keep getting:
The Microsoft Jet database engine could not find the object 'MailingLabelsTemp'. Make sure the object exists and that you spell its name and the path name correctly. (The other tables for some reason get this error too, not just mailinglabelstemp)
The concept is at the beginning the program uses already existing tables in the db. These are temp tables. I tried deleting them and recreating them, but what kept happening is basically the same thing. When it tries to open the recordset the table doesn't exist.
So the program fills each of these temp tables (which have the same fields as in the datareports) and then ties the datareport to that datasource and voila. However, if I dont' clear the table every time I display the report again it includes all the data from the old report, in other words I must clear the table before I write to it. But for some reason, after I clear it this error keeps coming up.
But this error is preventing all of that from happening.
.. this is my code for doing that:
Public Function clearTable(ByVal tableName As String) As Boolean
On Error GoTo uhhuh
'myConnection.BeginTrans
'Debug.Print myConnection.state
myConnection.Execute "DELETE * FROM [" & tableName & "]" ' TRUNCATE TABLE [tableName]
'need to somehow delete the table's content here
'myConnection.CommitTrans
clearTable = True
Exit Function
uhhuh:
'Stop
myConnection.RollbackTrans
Err.Clear
End Function
These are all the places (3) where it comes up with the same error:
1)
Public Sub fillMailingTableFromRS()
clearTable cMailingTableName
myLabelsSet.Open "SELECT * FROM " & cMailingTableName, myConnection, adOpenStatic, adLockOptimistic, adCmdText
2)
Public Function clearTable(ByVal tableName As String) As Boolean
On Error GoTo uhhuh
'myConnection.BeginTrans
'Debug.Print myConnection.state
myConnection.Execute "DELETE * FROM [" & tableName & "]" ' TRUNCATE TABLE [tableName]
3)
myHomeSalesSet.Open "SELECT * FROM " & cHomeSalesTableName, myConnection, adOpenStatic, adLockOptimistic, adCmdText
Also these are other subs that may or may not make a difference..
Public Sub initMyDatabase()
initMyDBGlobals
'init the db
Dim strConnect As String, strQuery As String, cmd As String
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConnect = strConnect & "Persist Security Info=False;"
strConnect = strConnect & ";Data Source=" & myDBPath
myConnection.ConnectionString = strConnect
myConnection.ConnectionTimeout = 10
myConnection.CursorLocation = adOpenStatic
myConnection.Open
Public Sub closeMyDatabase()
On Error GoTo closeMyDBErr
Set myRecordSet = Nothing
Set myLabelsSet = Nothing
Set myHomeSalesSet = Nothing
clearTempTables
'close recordset first, then connection.
If myConnection.state = adStateOpen Then
myConnection.Close
End If
Set myConnection = Nothing
Set mydbobj = Nothing
Exit Sub
closeMyDBErr:
Resume Next
End Sub
Private Sub clearTempTables()
clearTable cMailingTableName
clearTable cHomeSalesTableName
'levels: myRecordSet, myrsSelection, myRSCriterion
'do not delete main table!
clearTable c2ndTable
clearTable c3rdTable
End Sub
|
|
|
|
|
My suggestion is instead of deleting and recreating temporary table, you can create a permanent table, and give it a PK (e.g. using GUID) that you can use for the particular function.
Then after you've finished with the data you can delete all records with the particular PK.
What was the error returned anyway? Oops. Saw the error message you posted.
Perhaps there are multiple users accessing the database and one user dropped the table while it is being used?
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Actually, I did figure out what was going on. In a totally different portion of my program I was deleting and recreating the table right before the report creation. Then in my db mod file I was just trying to clear the table. Basically.. I messed up, and once I pulled out those lines the tables stopped vanishing on me.
|
|
|
|
|
I was trying to replace a very old program with a .NET version. It queries an MS Access database. The old program takes less than a millisecond to do the query (using MFC/ODBC), but my new .NET version takes at least 40 milliseconds. I tried both .NET ODBC and .NET OLEDB.
The old application uses a shared database connection. The new version is in .NET 1.1. According to the documentation, .NET does connection pooling automatically. What could be wrong?
|
|
|
|
|
Ask yourself these questions:
1 - Do you notice the difference between <1ms and 40ms?
2 - Do you do a lot of transferring of data between database and application, if yes, can you refactor it to limit the time spent talking to the database, or can you batch up the processes.
3 - Considered using SQL Server 2005: Express Edition? Works much better than Access and it's free.
Sig cops got me...
|
|
|
|
|
Sorry, I wasn't looking for the answers you gave. My question is, why should .NET be so much slower than the old app with MFC/ODBC? Even if the performance does not matter or there are better solutions, I still would like to know what makes .NET slow in this case.
|
|
|
|
|
Probably the abstraction provided by .NET, if you take a look at the code in reflector for the System.Data namespace then a simple call could actually make several calls. Reason behind is that you'll gain greater flexibility at the cost of performance however the gain in flexibility is far greater than the loss of performance for the majority of cases.
MFC/C++ may provide this flexibility as well but they tend to (I will stand corrected though) do most of the evaluation at compile-time rather than at JIT/runtime like .NET.
Sig cops got me...
|
|
|
|
|
Hi
How to increase size of database in SQL Server 2000.
Using Enterprise Manager.
Thanking u.
prakash sawant
|
|
|
|
|
Open the database Properties. From the Data Files page you can set File size for your database.
Regards.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Don't know why someone gave you a 1 as your direction, though cryptic, is correct.
To accomplish with T-SQL one would execute the following:
<br />
ALTER DATABASE dbFoo<br />
MODIFY FILE<br />
{Name= dbFoo,<br />
SIZE=25MB,<br />
MAXSIZE=2GB,<br />
FILEGROWTH=0)<br />
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Hi all,
i want to develop a client -server application wich have a frequently access to the database..Using dataset, is it the best way to do it? can i have any problems when many users connect in the same time?if yes, how can i resolve those confilcts?
Thanks for all..
|
|
|
|
|
baxter_dev wrote: Using dataset, is it the best way to do it?
It depends on what you want to do.
baxter_dev wrote: can i have any problems when many users connect in the same time?if yes, how can i resolve those confilcts?
Yes. You can resolve these problems by:
* Restructuring your database.
* Writing more efficient queries
* Upgrading the hardware on the server
* Upgrading the server software to Enterprise Edition.
* etc.
It really depends on what you are doin. There is no one catch-all answer to your question.
|
|
|
|
|
thanks for your answer..
i will try to explain more..i'm developping a financial application (quotes, markets, charts,financial transaction,portfolio...)this application is no a webApp, but server-client..many users will use it at the same time..
data are very importants and can change at any moment ( prices for example)..for getting or setting them from the database, i'm thinking about using datasets, but i know that they work in disconnected mode, so perhaps it will be some problems when updating data for example...so,datasets are they the best way? can i use flags in database to check if the data has been changed before?or doing an other thing?
i hope that it's more clear know..
thanks again and sorry for my poor english
-- modified at 5:49 Wednesday 18th October, 2006
|
|
|
|
|
The datasets use an optimistic concurrency scheme which means that it checks for changes to the tables before updating the database, I seem to remember that some event is fired off so that you can decide what to do in those cases.
It might be worth thinking if you can restructure the way data is handled so that it works in a more disconnected mode, it will save resources on the server for one thing, it fits with the .NET way of doing things and will likely to be more future proof (whatever that means).
Sig cops got me...
|
|
|
|
|
How to make DataSet transparently remoted, or proxied, or called by ref?
I have some DataSets that work in main AppDomain of application,
(it is huge, so I want to connect to it, not load each time)
but I want to move it to separate server, so in one mode I want to place
DataSet in separate app and in second mode- to use it in main AppDomain.
Usual way for it to have object inherited from Marshal-by-ref,
but DataSet already inherited from by Value,
so simple approach is not working,
so question is-
can I create transparent proxy for my Dataset, custom or not - by else way?
Thanks you
|
|
|
|
|
Hello. Is it possible to use SQL Server 2000 Notification Services together with .NET 2.0 client. Thanks in advance.
|
|
|
|
|
Hi
How can I save a DateTime from my C# program into a SQL Server (datetime)
database column.
Shahzad
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
