Hi,
When I add the table into the report, the default column number is 3. Let's say I need 10 columns on the report, besides right click on the last column and click on "add column right" to add the other 7 columns, is there any easy way?
Thanks.
Sorry, there is no other way of doing this. You have to add additional columns through the popup menu.
-- Robert
sqli want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.
For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?
Thanks for any help...
You can try something like:
SELECT top 500
...
from TableName
another more archaic form is to execute
SET ROWCOUNT 500
However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:
SET ROWCOUNT 0
The TOP option is in general the better option. Look the two up in books online.
sqlCoeus:
Maybe something like:
|||select convert (numeric (9,2), 0.0588235456456)
Thanks for help. :)
I also can use round(variable, 2).
i have a field which is CHAR(20), and it is allowed to only containe number chars.
is there any collation_name can help ? or how can i set the check clause ?
Here is a couple of ways that I would do it (no promises, and there could be better ways :)
DECLARE @.test char(20)
SELECT @.test = '12345678901234567890'
--All 20 characters are numbers
SELECT CASE WHEN @.test LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'Yes' ELSE 'No' END
--allows trailing blanks (that is what the replace to an ampersand does.)
SELECT CASE WHEN len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(rtrim(@.test),' ','@.'),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')) = 0 then 1 else 0 end
You can also add a check constraint ot the table
CREATE TABLE TestNumbersOnly (SomeColumn char(20), CONSTRAINT NumericOnlyPlease CHECK (SomeColumn NOT LIKE '%[a-z]%' AND ISNUMERIC(SomeColumn) = 1))
Let's test
--Good
INSERT TestNumbersOnly VALUES ('12345678901234567890')
--Good
INSERT TestNumbersOnly VALUES ('1234567890123456789')
--Bad
INSERT TestNumbersOnly VALUES ('1234567890123456789A')
--Bad
INSERT TestNumbersOnly VALUES ('123(4567890123456789')
--Bad
INSERT TestNumbersOnly VALUES ('123-4567890123456789')
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Why don't you use one of the numeric or integer data types then for the column? It is much cleaner, easier and efficient if you use the correct data type for the data. Using character data types has several issues in that you need to enforce your own constraints, the query optimizer doesn't know that the values are only numeric for example, and if you need to perform arithmetic operations then you need to cast the value explicitly & so on. Is there any business reason to store numeric values in character column? This is a problem with the data model and it is best it to deal it at that level.|||well, the column is actually a one that is the IDs of customers, so i think it is conventionally to set it to CHAR. unfortunately for me, it is also conventionally that the IDs of customers containt only number characters, so the two convention contradict at this point !
after reading your suggestions, i think it is much wise to adopt only one of them, or to let the front-end do the check up
|||No, don't let the front end be the only check. Then it cannot be 100% trusted. Do something on the backend if you have the ability to add constraints.|||yes, that's always a problem! thx for the remainderI have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.
Here is the stored Proc:
SELECTDATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME),COUNT(*)
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)
ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)
It returns a table:
MONTH DATE TOTAL NUMBER OF CALLS
======= ===== ===========
1 1 10
1 2 15
My question is: is it possible to combine the Month and Date column into one column. e.g.
Date Total Number of Calls
==== ==============
1/1 10
1/2 15
Please Help, Thanks in advance :)
SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME)) AS [Date],COUNT(*)
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))
ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)
|||thx, I did some configuration. works like a charm
SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))AS[Date],COUNT(*)
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME) ,DatePart(dd, Callstarttime),CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))
ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)
