|
There's no difference between using aggregate functions on a single-table query to a multiple-table query.
The problem with your query is that it doesn't have a GROUP BY clause. You use a GROUP BY clause to group similar items of data together. For each group of distinct data items, the COUNT function will return the count of items within this group. Without a GROUP BY clause, no grouping of data will occur and the COUNT function will always return 1.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
You need to put a GROUP BY statement in containing the other fields.
|
|
|
|
|
Hi there .
please help me about this error :
The header for file '%ls' is not a valid database file header. The %ls property is incorrect.
I don't have any backup.
help me to restore my db
DMASTER
|
|
|
|
|
|
I'm working on a project using SSRS and have a query that may accept a multiple valued integer as one of its parameters. I want to do something like this:
<br />
AND p.ID in (<br />
case @pList<br />
when '' then p.ID<br />
else (select id from dbo.fn_split_id_integer(@pList))<br />
end)<br />
which is fine when @Plist is empty but when it has multiple values in it the engine throws an error because multiple values are returned from the select statement, even though I'm using in rather than =.
Has anyone got any suggestions about how get this to work?
It definitely isn't definatley
|
|
|
|
|
AND p.ID in (<br />
case @pList<br />
when '' then p.ID<br />
else (select id from dbo.fn_split_id_integer(@pList))<br />
end)
The highlighted portion of the query is incorrect. Each branch of the CASE statement can only return a scalar value. In the ELSE clause, you are trying to return a table value.
You should change your query so that you JOIN p to the result of fn_split_id_integer().
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Can you use the following?
select ...
where p.ID in (
select id from dbo.fn_split_id_integer(@pList)
union all
select ID from MyTable where @pList = ''
)
...
order by ...If @pList contains values then your fn_split_id_integer function will return a list of integer ids. If @pList is empty then the second part of the union will return all of the possible ids. I am assuming that ID is the primary key.
If your database is large then you might want to do a bunch of performance tests.
Regards
Andy
|
|
|
|
|
Hi all ,
i would like to replace a column data in my table ---- (Memeber_Id)
all the data in this field starts like '073444%'
However i would like them to start like '73444%'
only replace the first digit from starting with zero to the actual number.
Can you give me a script please?
Memberid
073367777
073868364
0757257457
I want them changed to
Memeberid
73367777
73868364
757257457
Thanks
|
|
|
|
|
Assuming you use varchar as the data type:
Update tablename Set Memeber_Id = substring(Memeber_Id,2,len(Memeber_Id-1))
Why don't you store the Memeber_Id in int column?
|
|
|
|
|
Thank you very much for your response.
Memeberid is Char(30) data taype.
When i run your script it comes with the following error
""""The conversion of the varchar value '7207513006 ' overflowed an int column. Maximum integer value exceeded.
Please advice
Thanks
|
|
|
|
|
What is UP?
Is it not possible to do?
thanks
|
|
|
|
|
Can you plz provied the structure of your table??
RK..
|
|
|
|
|
Thank for your reply.
Its just a single table with no relationship to other tabel.
Having
Id int
message nvarchar(150)
Memberid Nvarchar(50).
I have to store memberid as nvarchar for some reason.
hope this helps to understand my table structure.
regards
kibrom
|
|
|
|
|
kibromg wrote: Memberid Nvarchar(50).
did not you say that it's char(30)?
|
|
|
|
|
|
can you post the exact query that you run and the exact error?
What database and version do you run?
This query should not give this kind of errors, no conversion to int occurs.
|
|
|
|
|
Yes it's true... the querry which was wriiten will not give such type of error.
RK..
|
|
|
|
|
Thanks man,
I am using SQL 2005. The table is not related to any other table.
I am just to change the data in memeberid and run the script you gave me.
Update tablename set memberid=substr(.........
Thanks
|
|
|
|
|
I think the problem is memberid is nvarchar(50) datataype not int.
is it?
|
|
|
|
|
no
and the function is substring not substr
Please post the complete query.
|
|
|
|
|
Update DATA Set Memeber_Id = substring(Memeber_Id,2,len(Memeber_Id-1))
|
|
|
|
|
Change it to:
Update tablename Set Memeber_Id = substring(Memeber_Id,2,len(Memeber_Id)-1)
len(Memeber_Id)-1 not len(Memeber_Id-1)
|
|
|
|
|
Thank you vety much man .Its working now.
How erver its trancating all the numbers.Its not removing 0 only.
It removes someother digits on the numbers
thanks
|
|
|
|
|
Yes
this query removes the 1st character whatever it is.
If you want to remove the first character only if it starts with '0' then add this where clause to the query:
Where Memeber_Id like '0%'
|
|
|
|
|
Thanks very much for your help.You know what i wanted to check who run a script on my database and at what time.
Where will i check that on SQL2005 database using athe admin login?
Thank you very very much.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
