Friday, March 23, 2012
How to alter column in Micorsoft SQL
I have the following sample code to alter two table columns. But it
did not work. Please help me out. Thanks!
drop table one ;
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a (NULL) ,
alter column b (NULL)
;
Thanks,
MikeOn Feb 1, 2:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following sample code to alter two table columns. But it
> did not work. Please help me out. Thanks!
> drop table one ;
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a (NULL) ,
> alter column b (NULL)
> ;
> Thanks,
> Mike
Hi Mike,
Perhaps you are not using Books Online properly.
The following code does what u wann.
ALTER TABLE ONE
ALTER COLUMN A FLOAT NULL
ALTER TABLE ONE
ALTER COLUMN B VARCHAR NULL
Thanks
Dutt...
How to Alter / Drop Table after publication
have a look at sp_repladdcolumn and sp_repldropcolumn.
There is no sp_replaltercolumn, although the 2 procedures
above can be used to achieve the same effect.
If your question is referring to changing or dropping a
table after a publication has been removed, then have a
look at sp_removedbreplication and sp_MSunmarkreplinfo.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Dear Paul,
Thanks for your help, it can be done now, thanks alot
Kail
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:181901c4bb30$ddef2c40$a301280a@.phx.gbl...
> Kail,
> have a look at sp_repladdcolumn and sp_repldropcolumn.
> There is no sp_replaltercolumn, although the 2 procedures
> above can be used to achieve the same effect.
> If your question is referring to changing or dropping a
> table after a publication has been removed, then have a
> look at sp_removedbreplication and sp_MSunmarkreplinfo.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
Wednesday, March 7, 2012
How to add 'ALL' as report parameter in drop down with other parameters
I would like to add 'ALL' to other report parameters.The other report parameters are counties.I would like to add 'all' so that user can select all counties from drop down.
I added union select 'ALL' to sub query.
in main query @.county='all' is it right or wrong.After I did this report is running very very slow.performance problem begins.
what exactly is the procedure to do.
thanks
r sankar
Hi -
You should be able to add an "All Counties" option to your drop down list without a much of a performance hit.
For the dataset that is the basis for the parameter list, have something like this:
SELECT
County,
CountyId
FROM
Counties
UNION
SELECT
'(ALL)', -1
Now in the dataset that the report is based on, include something like this:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR CountyId = -1)
Of course you'll see performance benefits from wrapping these two statements in stored procedures.
HTH....
--
Joe Webb
SQL Server MVP
http://www.sqlns.com
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
Thank you very much.Explained in very good way.I will try this way.If any problems I will let you know.Thanks once again.
r sankar|||
hi Joe Webbon ,
I've faced this problem , and I'm searching for a better solution for it.
I think this solution is not right . I try it . but it didn't work.
Why?
so it could be :
WHERE (Country.CTRYIsn =98) ====== country no 98
or
WHERE (Country.CTRYIsn = -1) ====== ((((NO DATA))))
because if the report user choose 'ALL' in the drop down menu , the value willl be -1, so, anyone knows that the query results only bring the row which match the value. and because there is not -1 in the country field in the fact table, the results will be 0 ROWS
SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn = - 1)
NO DATA, u see?
-
my idea is :
SELECT
County,
cast (CountyId as varchar(9))
FROM
Counties
UNION
SELECT
'(ALL)', '%'
SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn LIKE @.CTRYIsn)
so it could be :
WHERE (Country.CTRYIsn LIKE 98) ====== country no 98
or
WHERE (Country.CTRYIsn LIKE %) ====== ALL
anyone can find another solution that can high the performance than my sol. , please say it !!!!!!
Rather than comparing the data field to -1 it should have compared the parameter. ie
WHERE (Country.CTRYIsn LIKE @.CTRYIsn OR @.CTRYIsn = -1)
I think this would be be a better solution rather than passing a %.
Cheers
|||Hi,I use dynamic SQL in the stored procedure to manage the performance issues. I have got multiple drop down with the "All" option. example:
if @.CTRYIsn != '%' begin
set @.vWhere = @.vWhere + ' and s.CTRYIsn = ' + char(39) + @.CTRYIsn + char(39)
end
If you use the new "Multi-Value" property, change this to handle cases where user selected multiple items. I yet have to work on that. I guess that the parameter list will have to be compared to the distinct count of counties to see if the count is 1 or = to the count of counties or in between. if = to 1, then use = if in between then use in(@.CTRYsn) else do not set any limit on the item. What I do not know yet is how to count the items in the parameter list. May be you could count the "," from the parameter list?
Regards,
Philippe
thank u again,
I still can't understand , I think agian your statment are WRONG !!!!
did u try it , or u just propose a solution
dont say an example , My solution before is NOW on use.
Karim
|||Hi Karim -You're absolutely right. The code I posted will not work as is. There was a typo in it. The code should read:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR @.CountyId = -1)
The typo was in the second part of the OR clause. It should have the parameter (@.CountyId) as shown above rather than the fieldname (CountyId) as shown in the original post.
When a user selects a specific CountyId in the dropdown, the first part of the OR condition will be satisified. When the user selects the ALL item in the dropdown, the second part of the OR condition will be satisfied for every row in the table.
The solution you posted will certainly return the appropriate resultset, but it will also incurr the performance hit associated with using LIKE. It's not nearly as efficient as comparing two integers.
HTH....and thanks for pointing out the typo!
--
Joe Webb
SQL Server MVP
http://www.sqlns.com
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
||| (CountyId = @.CountyId OR @.CountyId = -1)
Actually, you may get better performance if you reverse the order, like this:
(@.CountyId = -1 OR CountyId = @.CountyId)
to take advantage of short-circuit evaluation of the OR operator. Because the expression "@.CountyId = -1" can be evaluated both ahead of time and just once, it may be faster.
For example, in SQL Server 2000, this:
declare @.value varchar(10)
set @.value='All'
select distinct patid from tx_history_all
where
@.value = 'All' or
cost_center_code = @.value
-- or @.value = 'All' is three times faster than this:
declare @.value varchar(10)
set @.value='All'
select distinct patid from tx_history_all
where
-- @.value = 'All' or
cost_center_code = @.value
or @.value = 'All'
|||Hey There --
I think you've gotten your "2000" answer already, so the following is just an FYI...You get multi-parameter select ("All", among other combinations) in 2005, making all the workarounds unnecessary.|||
Very interesting thread...Sounds like 2005 has this issue resolved!
But just for fun I would like to continue the "2000" workarounds...
You see, I was trying to get "(ALL)" or single item capabilities added to existing item range parameters. The user could indicate one item, all items, or a range of items. Here is what I came up with (it's kind of hokey looking but it works):
="select PONUMBER,ITEMNMBR,ITEMDESC,LOCNCODE,VENDORID,UOFM,QTYORDER,REQDATE from POP10110 (NOLOCK) WHERE QTYORDER > 0 " & IIf(Parameters!ItemStart.Value = "(ALL)","AND ITEMNMBR NOT LIKE "," AND ITEMNMBR BETWEEN '" & trim(Parameters!ItemStart.Value) & "' AND ") &
IIf(Parameters!ItemEnd.Value = "(ALL)","'" & trim(Parameters!ItemStart.Value) & "'","'" & trim(Parameters!ItemEnd.Value) & "'")
How to add 'ALL' as report parameter in drop down with other parameters
I would like to add 'ALL' to other report parameters.The other report parameters are counties.I would like to add 'all' so that user can select all counties from drop down.
I added union select 'ALL' to sub query.
in main query @.county='all' is it right or wrong.After I did this report is running very very slow.performance problem begins.
what exactly is the procedure to do.
thanks
r sankar
Hi -
You should be able to add an "All Counties" option to your drop down list without a much of a performance hit.
For the dataset that is the basis for the parameter list, have something like this:
SELECT
County,
CountyId
FROM
Counties
UNION
SELECT
'(ALL)', -1
Now in the dataset that the report is based on, include something like this:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR CountyId = -1)
Of course you'll see performance benefits from wrapping these two statements in stored procedures.
HTH....
--
Joe Webb
SQL Server MVP
http://www.sqlns.com
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
Thank you very much.Explained in very good way.I will try this way.If any problems I will let you know.Thanks once again.
r sankar|||
hi Joe Webbon ,
I've faced this problem , and I'm searching for a better solution for it.
I think this solution is not right . I try it . but it didn't work.
Why?
so it could be :
WHERE (Country.CTRYIsn =98) ====== country no 98
or
WHERE (Country.CTRYIsn = -1) ====== ((((NO DATA))))
because if the report user choose 'ALL' in the drop down menu , the value willl be -1, so, anyone knows that the query results only bring the row which match the value. and because there is not -1 in the country field in the fact table, the results will be 0 ROWS
SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn = - 1)
NO DATA, u see?
-
my idea is :
SELECT
County,
cast (CountyId as varchar(9))
FROM
Counties
UNION
SELECT
'(ALL)', '%'
SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn LIKE @.CTRYIsn)
so it could be :
WHERE (Country.CTRYIsn LIKE 98) ====== country no 98
or
WHERE (Country.CTRYIsn LIKE %) ====== ALL
anyone can find another solution that can high the performance than my sol. , please say it !!!!!!
Rather than comparing the data field to -1 it should have compared the parameter. ie
WHERE (Country.CTRYIsn LIKE @.CTRYIsn OR @.CTRYIsn = -1)
I think this would be be a better solution rather than passing a %.
Cheers
|||Hi,I use dynamic SQL in the stored procedure to manage the performance issues. I have got multiple drop down with the "All" option. example:
if @.CTRYIsn != '%' begin
set @.vWhere = @.vWhere + ' and s.CTRYIsn = ' + char(39) + @.CTRYIsn + char(39)
end
If you use the new "Multi-Value" property, change this to handle cases where user selected multiple items. I yet have to work on that. I guess that the parameter list will have to be compared to the distinct count of counties to see if the count is 1 or = to the count of counties or in between. if = to 1, then use = if in between then use in(@.CTRYsn) else do not set any limit on the item. What I do not know yet is how to count the items in the parameter list. May be you could count the "," from the parameter list?
Regards,
Philippe
thank u again,
I still can't understand , I think agian your statment are WRONG !!!!
did u try it , or u just propose a solution
dont say an example , My solution before is NOW on use.
Karim
|||Hi Karim -You're absolutely right. The code I posted will not work as is. There was a typo in it. The code should read:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR @.CountyId = -1)
The typo was in the second part of the OR clause. It should have the parameter (@.CountyId) as shown above rather than the fieldname (CountyId) as shown in the original post.
When a user selects a specific CountyId in the dropdown, the first part of the OR condition will be satisified. When the user selects the ALL item in the dropdown, the second part of the OR condition will be satisfied for every row in the table.
The solution you posted will certainly return the appropriate resultset, but it will also incurr the performance hit associated with using LIKE. It's not nearly as efficient as comparing two integers.
HTH....and thanks for pointing out the typo!
--
Joe Webb
SQL Server MVP
http://www.sqlns.com
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)||| (CountyId = @.CountyId OR @.CountyId = -1)
Actually, you may get better performance if you reverse the order, like this:
(@.CountyId = -1 OR CountyId = @.CountyId)
to take advantage of short-circuit evaluation of the OR operator. Because the expression "@.CountyId = -1" can be evaluated both ahead of time and just once, it may be faster.
For example, in SQL Server 2000, this:
declare @.value varchar(10)
set @.value='All'
select distinct patid from tx_history_all
where
@.value = 'All' or
cost_center_code = @.value
-- or @.value = 'All' is three times faster than this:
declare @.value varchar(10)
set @.value='All'
select distinct patid from tx_history_all
where
-- @.value = 'All' or
cost_center_code = @.value
or @.value = 'All'
|||Hey There --
I think you've gotten your "2000" answer already, so the following is just an FYI...You get multi-parameter select ("All", among other combinations) in 2005, making all the workarounds unnecessary.|||
Very interesting thread...Sounds like 2005 has this issue resolved!
But just for fun I would like to continue the "2000" workarounds...
You see, I was trying to get "(ALL)" or single item capabilities added to existing item range parameters. The user could indicate one item, all items, or a range of items. Here is what I came up with (it's kind of hokey looking but it works):
="select PONUMBER,ITEMNMBR,ITEMDESC,LOCNCODE,VENDORID,UOFM,QTYORDER,REQDATE from POP10110 (NOLOCK) WHERE QTYORDER > 0 " & IIf(Parameters!ItemStart.Value = "(ALL)","AND ITEMNMBR NOT LIKE "," AND ITEMNMBR BETWEEN '" & trim(Parameters!ItemStart.Value) & "' AND ") &
IIf(Parameters!ItemEnd.Value = "(ALL)","'" & trim(Parameters!ItemStart.Value) & "'","'" & trim(Parameters!ItemEnd.Value) & "'")
How to add ALL as report parameter in drop down
Hi ALL,
I would like to add 'ALL' to other report parameters.The other report parameters are counties.I would like to add 'all' so that user can select all counties from drop down.
I added union select 'ALL' to sub query.
in main query @.county='all' is it right or wrong.After I did this report is running very very slow.performance problem begins.
what exactly is the procedure to do.
thanks
r sankar
Select 'All Counties' as nm_county, 0 as id_county
UNION
Select nm_county, id_county from dbo.county
The report still renders just as quickly, and I just check "If@.County = 0" in the stored procedure I use. Hope this helps.
|||Hi,
thank you very much for reply.I did not use @.county =0 in stored procedure .that is why it became slow.Now It is working fine.Thank you once again.If I get any more problems I will let you know.
thanks
sankar|||
You can also reference it directly in a query::
SELECT *
FROM Businesses
WHERE (county = @.county) or (@.county = 'ALL')
This way, you either get Businesses for a specific county or you get all businesses.
Friday, February 24, 2012
How to add a date range of drop down list for a SQL query?
I'm trying to create a report that let the user select the date range (such
as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
SQL query like "select * from MyTable where selectdate between @.FromDate and
@.ToDate", but there are 2 parameters, but the date range drop down list just
have one parameter (For example, @.SelectDate), how could I pass the
@.SelectDate value to the @.FromDate and @.Todate?
I tried to search the MSDN but could not get the answer? Anyone could help?
Sample are appreciated if has.
Thanks a lot.
BillUse 2 comboboxes, one for the @.From another for the @.To date each with their
proper dataset:
- For @.From use something like "select mydates from dimTime ... "
- For @.To use something like "select mydates from dimTime where dates>=@.From
..."
(pseudo-code, you get the idea)
hth,
Tom
"Bill" wrote:
> Hi All,
> I'm trying to create a report that let the user select the date range (such
> as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
> SQL query like "select * from MyTable where selectdate between @.FromDate and
> @.ToDate", but there are 2 parameters, but the date range drop down list just
> have one parameter (For example, @.SelectDate), how could I pass the
> @.SelectDate value to the @.FromDate and @.Todate?
> I tried to search the MSDN but could not get the answer? Anyone could help?
> Sample are appreciated if has.
> Thanks a lot.
> Bill
>