|
OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Christian Graus wrote: To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?
When you do a GROUP BY only the columns specified in the GROUP BY clause may appear in as a column in the SELECT portion. Anything else must be put into an aggregate function. These don't supply a column name but you can add one by using AS [name] for example:
SELECT [Year], SUM(amount) AS [TotalAmount]
FROM SomeTable
GROUP BY [Year]
Does that help?
|
|
|
|
|
It's what I expected, I just felt that was a PITA 
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
Christian Graus wrote: It's what I expected, I just felt that was a PITA
You are not required to name the results of the aggregate functions. It is optional. If you are accessing the column by its ordinal position it probably isn't worth naming the column.
|
|
|
|
|
I have 2 tables, a galleries table and a photos table...
I'm trying to select all the data from the galleries table and then JUST ONE row from the photos table based on the gallery_id that is the FK.
If I do the following:
<br />
SELECT Galleries.Id, Galleries.Name, Galleries.Description, Photos.Id AS Expr1<br />
FROM Galleries INNER JOIN<br />
Photos ON Galleries.Id = Photos.Gallery_Id<br />
WHERE (Galleries.Visible = @Visible)<br />
I get rows returned with duplicate gallery information... So basically I want to know how i can get a single row of data for a each unique gallery.Id and add a single photo.id that matches that gallery_id...
I've messed around with distinct and still can't get it to work...
(Sql Server 2005 Express)
Thanks
|
|
|
|
|
We have a schema very similar to yours. I don't know what you're trying to accomplish, but I'm going to take a stab that you want to display a thumbnail for the gallery?
Based on my experience it would be best to add a column named ThumbnailID, then have a process (UI or automated doesn't matter) which selects a thumbnail for the gallery and updates the ThumbnailID column. Then you could do the following:
SELECT Galleries.Id, Galleries.Name, Galleries.Description, Photos.Id AS Expr1<br />
FROM Galleries INNER JOIN<br />
Photos ON Galleries.ThumbnailID = Photos.Id<br />
WHERE (Galleries.Visible = @Visible)
I know this isn't actually what you asked for, but I'll give you an alternative as well:
<br />
SELECT Galleries.Id, Galleries.Name, Galleries.Description, <br />
(SELECT TOP 1 Id, FROM Photos WHERE Galleries.Id = Photos.Gallery_Id) Id<br />
FROM Galleries<br />
WHERE (Galleries.Visible = @Visible)<br />
The problem is that over time, this will perform very poorly. For a little bit of extra work, the first option will scale much better.
I hope it wasn't too presumptuous - Good luck!
|
|
|
|
|
Ooops, small correction, with the first option you could actually drop the INNER JOIN and just use Galleryies.ThumbnailID
|
|
|
|
|
You hit it on the head... I'm trying to do exactly what you said, but was hoping to just solve the problem without changing my schema and using sql instead. But if it will perform poorly in the future I'll just change it now...
Thanks
|
|
|
|
|
Assume that I am working with a single database. I am talking about a single database file. Now, both access and sql server. I know the app.config file provides the database connection information. I wonder if I can have an option for a single database connection where user can decide either access or sql. It seems like each of them will require its own data adapter. I want to know if that is possible and how can I do that.
For example, assume that I do have a database name "nwnd", this database depends on the user's choice. For example, if the user wants to choose ms access, the application will use ms access. If the use chooses to use sql, the application will use sql. The problem is, I cannot find a way to choose the database connectivity for a single component. Assume that if the user choose ms access, the ms access table will display on the grid. Also if the user chooses ms sql, the ms sql table will display on the grid. I cannot find a way to do that from the database explorer or wizard. It seems like that can only be done hard coding or manually.
I will appreciate your comment.
|
|
|
|
|
Never trust the wizards. They are at best useful only for elementary cases and learning.
|
|
|
|
|
Can anyone comment on the performance pros/cons of using table-valued functions vs. Stored Procedures in SQLServer? Specifically:
1) are the execution plans for the functions stored as they are in stored procedures?
2) When using table variables inside the function, does the entire result set automatically wind up in memory, or can it be streamed using a reader?
Thanks for any input.
|
|
|
|
|
Does anyone know if there is an opposite to the SQL "IN" statement?
I have a string of values that I want to check the database for.
However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND"..
Is there anything out there?
Thanks and sorry for the trivial question.
Dave
|
|
|
|
|
The opposite of "IN" would be "NOT IN", but I don't think that is what you are after in this case.
I don't understand your question wrt. "AND". If I query a column called "NAME", I can have returned all names IN ("MIKE", "CHRIS", "DON"). But there is no way any data item will ever be equal to all three of those values, which is what you are asking for when you say you want an "AND" condition.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
It sounds like "IN" is what you want. If I have a list of values, i.e. ('Apple', 'Orange')
and I want to know if they are in the FRUIT table, I would write:
SELECT Name
FROM FRUIT
WHERE Name IN ('Apple, 'Orange').
This would pull back all values from the fruit table that are in my list.
However, there is no logical way to use this with an "AND", because a string can only match one value.
If you're looking for the value 'AppleOrange' you might use the LIKE operator:
SELECT Name
FROM FRUIT
WHERE Name LIKE '%Apple%'
This would pull back values "Apple" and "AppleOrange"
I don't think there's a way to use the LIKE operator with a list though.
Hope that helps a bit.
|
|
|
|
|
Hi Dave,
I think u want to select the rows if table contains all the values in your IN statement otherwise no row should be returned. If this is the case then unluckily there is no direct way to cope with this but fortunately you can accomplish this by SP.
for example (pseudoCode)
1. variable boolean allValues
2. loop for all value in your IN statment
3. Variable rowcount = select count(*) from table where column = 'your value'
4. if rowcount = 0 then allvalue = false and exit loop else continue with the loop
5. end loop
6. if allValues = false returned no row else returned allrows for the values in your IN statement.
Hope that works if this is what you are trying to explain
regards
Saud
saud
|
|
|
|
|
If someone could give me some guidance with the following problem. I have a table; tblItems; which fields fldItem(nvarchar 75), fldNSN(nvarchar 16), fldPartNum, fldVendor, fldPrice... I would like to have a stored procedure that can search and return a dataset based upon which parameter the user enters. I thought if I created a procedure like this, it would work but I keep getting some error in building the procedure Incorrect Syntax Near the Keyword 'Select' or Invalid operator for datatype. Operator equals modulo, type equals varchar;
Create Proc usp_SearchItems @pName nvarchar(75), @pNSN nvarchar(16), @pTPart nvarchar(20), @pVPart nvarchar(50) AS Declare @pQuery nvarchar(1024)
if @pName<>''
set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldItem Like '%' + @pName + '%'; Order By fldItem
if @pNSN<>''
Set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldNSN Like '%' + @pNSN + '%';
exec @pQuery
I don't have a problem if I use Create Proc usp_SearchItem @pName nvarchar(75) As Select * From tblItems Where (fldInactive=0) and fldItem Like '%' + @pName + '%'
I'm trying to use one procedure instead of four.
Any assistance would be greatly appreciated.
|
|
|
|
|
Try the following:
CREATE PROCEDURE usp_SearchItems
@pName NVARCHAR(75),
@pNSN NVARCHAR(16),
@pTPart NVARCHAR(20),
@pVPart NVARCHAR(50)
AS BEGIN
DECLARE @pQuery NVARCHAR(1024)
SET @pQuery = 'SELECT * FROM tblItems WHERE (fldInactive = 0)'
IF (@pName<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldItem LIKE ''%' + REPLACE(@pName, '''', '''''') + '%'')'
END
IF (@pNSN<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldNSN LIKE ''%' + REPLACE(@pNSN, '''', '''''') + '%'')'
END
SET @pQuery = @pQuery + ' ORDER BY fldItem'
--PRINT @pQuery
EXEC(@pQuery)
END
If you have any problems then uncomment the "PRINT" statement to find out what the final contructed query was.
The "REPLACE" function is being used to protect the stored procedure against quote characters. Do a search for "SQL Injection Attacks" to find out more about this problem.
Regards
Andy
|
|
|
|
|
You could use something like this. Default the parameters to null then coalesce or inull the parameter. The example proc below returns one result set but if you wanted to you could just as easily return one result set per parameter passed in.
Create Proc usp_SearchItems
@pName nvarchar(75) = null,
@pNSN nvarchar(16)= null,
@pTPart nvarchar(20)=null,
@pVPart nvarchar(50) =null
AS
Select *
From tblItems
Where (fldInactive=0)
and fldItem Like '%' + coalesce( @pName , fldItem) + '%'
and fldNSN Like '%' + coalesce( @pNSN , fldNSN)+ '%'
....
|
|
|
|
|
Can anyone help me why "N" is used before any property (eg. "IsUserTable")in OBJECTPROPERTY in Sql Server ?
|
|
|
|
|
It converts the string into a Unicode string.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Strictly speaking, th N' doesn't itself "convert" the string, but rather specifies that it should be converted (if needed) and stored/retrieved as a Unicode string by the server...
|
|
|
|
|
True. I should be more careful with my grammar.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
|
Hello,
I was hoping someone could give me a bit of information on the way I should be creating / handling my table adapters.
I have a persistant business object that is managing a number of database calls using a lot of different table adapters. The question I have is what the best practice is for creation of these table adapters. Is it better to persist a reference to each of my different table adapters over the life of my business object (i.e. create them all once, store them in class variables, then dispose of them all at the end) or is it better to create them as they are required (i.e a new table adapter created for each function call).
If someone could give a bit of an insight into what a table adapter is doing in terms of the connections created for it during instantiation that would be most helpful in helping me decide the most efficient approach.
Thanks for your help,
Adam
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)