boolean value in column

I need to do an aggregate query against an integer data type, and want to
return three columns based on the value: 1, 2, or 3. If the field name is
"Action", I should be able to sum (action=1), (action=2), and (action=3).
First I tried the following:
SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2), SUM(SU.[Action]
= 3)
The result of each would be -1 or zero. That doesn't work, I guess because
no boolean data type exists. Using CAST or CONVERT doesn't work either for
the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I would
try IF/ELSE, but cannot get the syntax right. I've used it before but can't
remember how I did it. May have been with DB2. Should go something like:
SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] = 3
BEGIN 1 END ELSE BEGIN 0 END)
If someone would enlighten me I'd appreciate it. ThanksSELECT
SUM CASE WHEN foobar_action = 1 then 1 ELSE 0 END) AS total_1,
SUM CASE WHEN foobar_action = 2 then 1 ELSE 0 END) AS total_2,
SUM CASE WHEN foobar_action = 3 then 1 ELSE 0 END) AS total_3
FROM Foobar;|||David McDivitt wrote:
> I need to do an aggregate query against an integer data type, and
> want to return three columns based on the value: 1, 2, or 3. If the
> field name is "Action", I should be able to sum (action=1),
> (action=2), and (action=3). First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action] = 3)
> The result of each would be -1 or zero. That doesn't work, I guess
> because no boolean data type exists. Using CAST or CONVERT doesn't
> work either for the same reason: (SU.[Action] = 1) cannot be
> interpreted. So thought I would try IF/ELSE, but cannot get the
> syntax right. I've used it before but can't remember how I did it.
> May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF
> SU.[Action] = 3 BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
Try using a CASE statement:
create table #abc (action int)
insert into #abc values (1)
insert into #abc values (2)
insert into #abc values (2)
insert into #abc values (3)
insert into #abc values (3)
insert into #abc values (3)
Select
SUM( CASE action
WHEN 1 THEN 1
ELSE 0
END ) as "Action 1",
SUM( CASE action
WHEN 2 THEN 1
ELSE 0
END ) as "Action 2",
SUM( CASE action
WHEN 3 THEN 1
ELSE 0
END ) as "Action 3"
From #abc
Action 1 Action 2 Action 3
-- -- --
1 2 3
drop table #abc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Look up CASE WHEN in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David McDivitt" <x12code-del@.del-yahoo.com> wrote in message
news:ve9pg1dr6kvo45sc4s2mmmt003cisrhg6t@.
4ax.com...
>I need to do an aggregate query against an integer data type, and want to
> return three columns based on the value: 1, 2, or 3. If the field name is
> "Action", I should be able to sum (action=1), (action=2), and (action=3).
> First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action]
> = 3)
> The result of each would be -1 or zero. That doesn't work, I guess because
> no boolean data type exists. Using CAST or CONVERT doesn't work either for
> the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I
> would
> try IF/ELSE, but cannot get the syntax right. I've used it before but
> can't
> remember how I did it. May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] =
> 3
> BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
>|||>From: "David Gugick" <david.gugick-nospam@.quest.com>
>Date: Wed, 24 Aug 2005 13:17:27 -0400
>Lines: 62
>David McDivitt wrote:
>Try using a CASE statement:
>create table #abc (action int)
>insert into #abc values (1)
>insert into #abc values (2)
>insert into #abc values (2)
>insert into #abc values (3)
>insert into #abc values (3)
>insert into #abc values (3)
>Select
> SUM( CASE action
> WHEN 1 THEN 1
> ELSE 0
Thanks guys the CASE statement was what I was looking for.

Boolean value

How can I return boolean value to the client.
This doesn't work:
SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
If I return :
SELECT column1=case when exists (select ...) then 1 else 0 end,...
then client gets the int32 value
regards,S> How can I return boolean value to the client.
You can't. There is no Boolean datatype in TSQL. There is a bit datatype, wh
ich is a integer-class
datatype restricted to the values 0, 1 and NULL. Some libraries (ADO etc) wi
ll interpret 1 as TRUE
and 0 as FALSE. If you want to return but, you have to CAST. for example
SELECT case when exists (select ...) then CAST(1 AS bit) else CAST(0 AS bit)
end AS column1
Or you can do the CAST outside the CASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:O3fYef9oFHA.3256@.tk2msftngp13.p
hx.gbl...
> How can I return boolean value to the client.
> This doesn't work:
> SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
> If I return :
> SELECT column1=case when exists (select ...) then 1 else 0 end,...
> then client gets the int32 value
> regards,S
>|||There's no boolean data type in SQL. I'd suggest using the bit data type.
Like this:
ELECT column1=case when exists (select ...) then cast(1 as bit) else cast(0
as bit) end
ML

Boolean parameters

Hello,
I'm creating a boolean parameter in Report Designer and set {Label = "Yes",
Value = True} and {Label = "No", Value = False}. Then on Preview Page I see
to radio buttons, the first one is labeled "true" and the other one is
"false". Is this a bug? Can I set the labels I want to this radio buttons?This is a known limitation and scheduled to be addressed in a future
release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>|||Changing this behavior, for Booleans, is on our wish list for inclusion in a
future release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>

boolean function

It is possible to create a function that returns a boolean value in t-sql?

A "boolean" function in t-sql would use the "bit" datatype; an example might be something like:

create function dbo.reflectBit (@.prm_bit bit)
returns bit
begin

return (@.prm_bit)

end

go

select dbo.reflectBit (1) as aBit

-- aBit
-- -
-- 1

Give a look at the "bit datatype" article in books online.

Boolean formatting in report

I have a data source with a column that is a boolean data type. When
I render this data on the report the value of 0 displays as "False"
and 1 displays as "True". I would like 0 to display as "No" and 1 to
display as "Yes". I am assuming I have to set up an IIF statement
that evaluates the value and changes the display based on that. I was
wondering if anyone knew of a better way, such as a Custom Format
value that would be a better route?
Thanks in advance for any assistance!On Sep 20, 2:03 pm, midas <mcneeley1...@.yahoo.com> wrote:
> I have a data source with a column that is a boolean data type. When
> I render this data on the report the value of 0 displays as "False"
> and 1 displays as "True". I would like 0 to display as "No" and 1 to
> display as "Yes". I am assuming I have to set up an IIF statement
> that evaluates the value and changes the display based on that. I was
> wondering if anyone knew of a better way, such as a Custom Format
> value that would be a better route?
> Thanks in advance for any assistance!
The way you mentioned is quick and the effort is negligible.
=iif(Fields!SomeField.Value = True, "Yes", "No") -or- =iif(Fields!
SomeField.Value = 1, "Yes", "No")
You could also try using a case statement in the query/stored
procedure that is sourcing the report.
select case when SomeField = 'True' then 'Yes' else 'No' end as
SomeField, ... from table_x ...
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, I went with the case. I was just trying to be lazy. ;)|||On Sep 21, 2:57 pm, midas <mcneeley1...@.yahoo.com> wrote:
> Thanks, I went with the case. I was just trying to be lazy. ;)
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Boolean expression in SQL

Hello,
In VB/VB.NET, I can use an expression as such to evaluate true/false:
Dim blnValue As Boolean = (SomeObject.Property = "Some value")
Can I do this in T-SQL?
declare @.var bit
set @.var = ?
Thanks.

T-SQL uses ANSI SQL CASE statement for Boolean expression, try the links below for more info. Hope this helps.
http://builder.com.com/5100-6388-5078041.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

|||

bmains wrote:

Hello,
In VB/VB.NET, I can use an expression as such to evaluate true/false:
Dim blnValue As Boolean = (SomeObject.Property = "Some value")
Can I do this in T-SQL?
declare @.var bit
set @.var = ?
Thanks.

Bit type data can hold either a 1 or 0. So you have to set it to either 1 or 0. You might use it like boolean. One bit value will occupy 1 bytes of space.
Hope this helps.sql

Bookmark in Code not working.

I have a report in reporting services that has a bookmark value asigned to each row. In the following Page_Load event for the page, I'm getting the following error on the ReportViewer1.JumpToBookmark("6") statement:

"An exception of type 'System.InvalidOperationException' occurred in Microsoft.ReportViewer.WebForms.dll but was not handled in user code

Additional information: Some parameters or credentials have not been specified"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim strReport1 As String = "/ReportDirectory/CurrentWeekSales"

ReportViewer1.ServerReport.ReportServerUrl = New System.Uri(CStr(Session.Item("ReportServer")))
ReportViewer1.ServerReport.ReportPath = strReport1
ReportViewer1.ServerReport.Timeout = 500000

Do While ReportViewer1.ServerReport.IsDrillthroughReport
ReportViewer1.PerformBack()
Loop

Dim RptParameters1 As ReportParameter() = New ReportParameter(0) {}
RptParameters1(0) = New ReportParameter("EBO_EmpKey", CStr(Session.Item("UserKey")))
ReportViewer1.ServerReport.SetParameters(RptParameters1)

ReportViewer1.ServerReport.Refresh()

ReportViewer1.JumpToBookmark("6")

End If
End Sub

What am I missing?

ThanksI too am having a similar problem. I find that JumpToBookmark() simply does not work no matter what I try. Interestingly, setting the 'action' property of a TextBox within the report to jump to the target bookmark seems to work. Unfortunately, I need to be able to do this in code without having to force the user to click on the report.

Is this a known problem, or is there something else on the viewer and/or report that needs to be set?

Thanks,

Ian.

Body values in Header and header values in body

Hi,

I want to display a value from db in the header section. I have read a couple of information that I should place the value in a in-visible text field an reference it in the header with the ReportItems. This works great with the first page but on the second page the header information are empty (I think because the Textfield is on Page 1 not Page 2)... So is there a way to accomplish that for all pages? Not only the first page....

My second one, hope you don't mind that I post it in the same thread, is:

My Report needs to display the total size of pages in the body. I did not realy found anything useful where I can retrieve from the total size of pages of my reports....

thanks for any help

f.

I have run into issues with wanting to display data in the report headers. I have pretty much moved on from wanting to do it since it is such a pain.

Also to show the total pages I put this code in the page footer, ="Page " & Globals!PageNumber & " of " & Globals!TotalPage. This will show the current page out of how many pages at the bottom of each page.

|||I have tried Globals!TotalPage but it cannot be placed in the body... I got an error message|||The Globals!TotalPages can only be used in a page header or footer, not in the body. You will have to add a page header or footer and then put it in a textbox there.

Blocking updates of a table

Hi,
I've been trying to design a way for me to issue a transaction that:

Block all inserts on a table when row X has a certain value (call it A)
Add a row to the table with row X containing A Add rows to another table Unblock inserts Commit transaction

Is this possible? Can anyone give me some pointers as to what to do?
Thanks in advance!

For (1) and (2)
While inserting the row that has a certain value (A), you can request a TABLOCKX. This will acquire X lock on the table and there by block other inserts by other concurrent transactions.

For (3): Is it like any other insert to another table? In that case, nothing special needs to be done

Thanks
Sunil Agarwal

|||For 3 that is what I meant.
For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly? Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?
Thanks for your help!
|||For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly

sunil> yes

Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?

sunila> if you only want to block inserts by other transactions under the condition you have mentioned but allow updates/selects, then there is no way.
thanks,|||I assume you only want to block Inserts but not Updates, Deletes and SELECTS.

You could try using an Insert trigger that would rollback the other transactions until a condition has been achieved.

Blocking Problems

We have a stored proc that is causing alot of blocking
problems. Basically, it selects a field based on the PK
and returns the value (0 or 1) as an output parameter:
SELECT @.Anonymous_Bool = Anonymous_Bool
FROM Member_Properties
WHERE GUID = @.GUID
The estimated execution plan shows that it does a
clustered index seek. When we tested it out it was fine,
but when it's in production, it looks like it starts
causing massive blocking problems. I don't see how the
proc itself could be a problem since it is very
efficient.
Has anyone else encountered a similar problem? could it
possibly be something in the front end code? or maybe
connection pooling? any ideas on a direction to look
would be much appreciated...Is @.Guid a UniqueIdentifier datatype? If not and the GUID column is it may
have an issue actually using the index properly. But I suspect you are
seeing the results of the connection changing the isolation level to
serializable. Or you can be seeing the effects of page splitting due to the
Guid is clustered. Check to see what is going on with profile on the
connections doing the blocking. In my opinion Guid's are a horrible PK and
especially if you cluster on them. There are few if any alternatives that
can perform worse than a Guid in a clustered index on a table with new rows
being inserted.
--
Andrew J. Kelly
SQL Server MVP
"A. Sugrue" <sugruea@.hotmail.com> wrote in message
news:058701c356ec$ec57e640$a401280a@.phx.gbl...
> We have a stored proc that is causing alot of blocking
> problems. Basically, it selects a field based on the PK
> and returns the value (0 or 1) as an output parameter:
> SELECT @.Anonymous_Bool = Anonymous_Bool
> FROM Member_Properties
> WHERE GUID = @.GUID
> The estimated execution plan shows that it does a
> clustered index seek. When we tested it out it was fine,
> but when it's in production, it looks like it starts
> causing massive blocking problems. I don't see how the
> proc itself could be a problem since it is very
> efficient.
> Has anyone else encountered a similar problem? could it
> possibly be something in the front end code? or maybe
> connection pooling? any ideas on a direction to look
> would be much appreciated...|||Yes it is a uniqueidentifier and is the PK with a
clustered index and it is also the rowguidcol for merge
replication.
>--Original Message--
>Is @.Guid a UniqueIdentifier datatype? If not and the
GUID column is it may
>have an issue actually using the index properly. But I
suspect you are
>seeing the results of the connection changing the
isolation level to
>serializable. Or you can be seeing the effects of page
splitting due to the
>Guid is clustered. Check to see what is going on with
profile on the
>connections doing the blocking. In my opinion Guid's are
a horrible PK and
>especially if you cluster on them. There are few if any
alternatives that
>can perform worse than a Guid in a clustered index on a
table with new rows
>being inserted.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> We have a stored proc that is causing alot of blocking
>> problems. Basically, it selects a field based on the PK
>> and returns the value (0 or 1) as an output parameter:
>> SELECT @.Anonymous_Bool = Anonymous_Bool
>> FROM Member_Properties
>> WHERE GUID = @.GUID
>> The estimated execution plan shows that it does a
>> clustered index seek. When we tested it out it was
fine,
>> but when it's in production, it looks like it starts
>> causing massive blocking problems. I don't see how the
>> proc itself could be a problem since it is very
>> efficient.
>> Has anyone else encountered a similar problem? could it
>> possibly be something in the front end code? or maybe
>> connection pooling? any ideas on a direction to look
>> would be much appreciated...
>
>.
>|||No, but since we already had a uniqueidentifier there as
the PK, there was no need to add another column to be the
rowguidcol.
>--Original Message--
>Having a Guid for merge replication purposes is fine but
that does not mean
>it has to be the PK.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:053f01c357ae$41b2aaa0$a301280a@.phx.gbl...
>> Yes it is a uniqueidentifier and is the PK with a
>> clustered index and it is also the rowguidcol for merge
>> replication.
>> >--Original Message--
>> >Is @.Guid a UniqueIdentifier datatype? If not and the
>> GUID column is it may
>> >have an issue actually using the index properly. But I
>> suspect you are
>> >seeing the results of the connection changing the
>> isolation level to
>> >serializable. Or you can be seeing the effects of page
>> splitting due to the
>> >Guid is clustered. Check to see what is going on with
>> profile on the
>> >connections doing the blocking. In my opinion Guid's
are
>> a horrible PK and
>> >especially if you cluster on them. There are few if
any
>> alternatives that
>> >can perform worse than a Guid in a clustered index on a
>> table with new rows
>> >being inserted.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>> >news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> >> We have a stored proc that is causing alot of
blocking
>> >> problems. Basically, it selects a field based on
the PK
>> >> and returns the value (0 or 1) as an output
parameter:
>> >>
>> >> SELECT @.Anonymous_Bool = Anonymous_Bool
>> >> FROM Member_Properties
>> >> WHERE GUID = @.GUID
>> >>
>> >> The estimated execution plan shows that it does a
>> >> clustered index seek. When we tested it out it was
>> fine,
>> >> but when it's in production, it looks like it starts
>> >> causing massive blocking problems. I don't see how
the
>> >> proc itself could be a problem since it is very
>> >> efficient.
>> >>
>> >> Has anyone else encountered a similar problem?
could it
>> >> possibly be something in the front end code? or
maybe
>> >> connection pooling? any ideas on a direction to look
>> >> would be much appreciated...
>> >
>> >
>> >.
>> >
>
>.
>

Blank value when displaying parameter label

Hi,
I've got the following problem in a report:
I've got a parameter that i'm manually giving available values and labels.
When i'm trying to display this parameter's label into a tabcell
(Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
ReportManager).
Is there a way to fix this issue ?
Best Regards,
Grégory.Greg,
Are you trying to do this in report designer? Have you set a prompt string
for the report parameter on the report parameters dialog?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
news:3B6D9848-7DBE-4B4D-B9EE-3B23B969ABAD@.microsoft.com...
> Hi,
> I've got the following problem in a report:
> I've got a parameter that i'm manually giving available values and labels.
> When i'm trying to display this parameter's label into a tabcell
> (Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
> ReportManager).
> Is there a way to fix this issue ?
> Best Regards,
> Grégory.|||Hi Lukasz,
Yes i'm trying to do this in the report designer. The result is identitical
once the report is deployed. And there is a prompt string for this particular
parameter.
Any idea ?
Thanks in advance.
Grégory.
"Lukasz Pawlowski [MSFT]" wrote:
> Greg,
> Are you trying to do this in report designer? Have you set a prompt string
> for the report parameter on the report parameters dialog?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
> news:3B6D9848-7DBE-4B4D-B9EE-3B23B969ABAD@.microsoft.com...
> > Hi,
> >
> > I've got the following problem in a report:
> >
> > I've got a parameter that i'm manually giving available values and labels.
> > When i'm trying to display this parameter's label into a tabcell
> > (Parameters!paramNAME.Label), i've got a blank value (in VS.NET and in the
> > ReportManager).
> >
> > Is there a way to fix this issue ?
> >
> > Best Regards,
> > Grégory.
>
>

blank textbox is NOT null ..

This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode = @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode => @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>

Blank spaces

How can I pass blank spaces for Required fields into database from Forms as
Initial Value , On insert , Post insert options does not work.
Thanks- HAVE YOU TRIED SETTING THE SETTING THE DEFAULT VALUES ON THE FIELDS?

-DO THEY HAVE TO BE SPACES? WOULD 'NULL' VALUES WORK?|||Originally posted by edwinjames
- HAVE YOU TRIED SETTING THE SETTING THE DEFAULT VALUES ON THE FIELDS?

-DO THEY HAVE TO BE SPACES? WOULD 'NULL' VALUES WORK?

1. Forms will not take blank spaces as it will truncate it for varchar2 fiedls .

2. All fields are required.

3. Assigning it from triggers have different problems of record locking.

blank parameter

what's the diff between 'allow null value' and 'allow blank value'?
if I have a parameter with 'allow null value' and 'allow blank value'
selected, does that mean that I can run the report without having to
enter a value for that parameter?
I have a report that has 3 fields:
1) region
2) store
3) choice
A ) If a user selects a region from region paratmeter drop down then
selects 'by region' in the choice parameter and leaves the store
parameter blank...it's supposed to pull a report that lists all trans
grouped by region
B) If a user selects a store location from store paratmeter drop down
then selects 'by store' in the choice parameter and leaves the region
parameter blank...it's supposed to pull a report that lists all trans
grouped by the stor
I am able to do this when I run the query in the data tab. But when I
try to do the same using the report parameters...the report will only
run if I have something selected in both the region and store
parameters. I have selected both 'allow null value' and 'allow blank
value' for all the parameters.
But I also noticed that even though I have to select both region and
store parameters, the report will display the data by region if I
select 'by region' in the choice parameter or by store if I select 'by
store' in the choice parameter.
So, the data is displayed properly but I keep to have the select a
value for all fields.
I hope someone can help me out.Hi,
Allow Null and Blank depends on your query to handle. yes reporting server
should not allow, "Allow blank" to get checked when the data type is string
and the values are coming from drop down.
To work around this problem.
you can possibily give a union in your data tab something like this.
select ' ' as abc from table1
union
select distinct abc from table2
so the blank wont be visible instead a (null) is inserted for you to select.
This can be a possible solution.
Amarnath
"bevarg" wrote:
> what's the diff between 'allow null value' and 'allow blank value'?
> if I have a parameter with 'allow null value' and 'allow blank value'
> selected, does that mean that I can run the report without having to
> enter a value for that parameter?
> I have a report that has 3 fields:
> 1) region
> 2) store
> 3) choice
>
> A ) If a user selects a region from region paratmeter drop down then
> selects 'by region' in the choice parameter and leaves the store
> parameter blank...it's supposed to pull a report that lists all trans
> grouped by region
>
> B) If a user selects a store location from store paratmeter drop down
> then selects 'by store' in the choice parameter and leaves the region
> parameter blank...it's supposed to pull a report that lists all trans
> grouped by the stor
>
> I am able to do this when I run the query in the data tab. But when I
> try to do the same using the report parameters...the report will only
> run if I have something selected in both the region and store
> parameters. I have selected both 'allow null value' and 'allow blank
> value' for all the parameters.
>
> But I also noticed that even though I have to select both region and
> store parameters, the report will display the data by region if I
> select 'by region' in the choice parameter or by store if I select 'by
> store' in the choice parameter.
>
> So, the data is displayed properly but I keep to have the select a
> value for all fields.
>
> I hope someone can help me out.
>

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

bit value as parameter

Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>

bit value as parameter

Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>

Bit Value

Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.

Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.

Question: Is this a good idea? Or could I somehow tally up all these
bit values and store it in one field in the customer table?

The application is a website and it is built using ASP.NET (VB.NET)

Any opinions would be great.

Cheers,
JackOn 18 Jan 2005 17:06:28 -0800, Jack wrote:

>Hi there, I'm not sure if this the appropriate group so apologies if
>it lies outside the boundary.
>Senario: I have a customer table with contains a bunch of different
>bit values that represent true/false values pertaining to the
>customer. I decided for the purpose of clarity I would move these
>values into another table. This way I would keep the customer details
>(name, age, etc) separate from these values.
>Question: Is this a good idea? Or could I somehow tally up all these
>bit values and store it in one field in the customer table?
>The application is a website and it is built using ASP.NET (VB.NET)
>Any opinions would be great.

Hi Jack,

I'd say: replace the bit columns with somewhat more descriptive columns,
like
ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))

I don't see any advantage in moving these columns to a seperate table,
unless you are approaching the max number of columns per table (quite
unlikely) or unless you need to store these yes/no values only for a
limited subset of your customers.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:r85su0peh2op7lhf1i7eocfdm2shms59vb@.4ax.com...
> On 18 Jan 2005 17:06:28 -0800, Jack wrote:
>>Hi there, I'm not sure if this the appropriate group so apologies if
>>it lies outside the boundary.
>>
>>Senario: I have a customer table with contains a bunch of different
>>bit values that represent true/false values pertaining to the
>>customer. I decided for the purpose of clarity I would move these
>>values into another table. This way I would keep the customer details
>>(name, age, etc) separate from these values.
>>
>>Question: Is this a good idea? Or could I somehow tally up all these
>>bit values and store it in one field in the customer table?
>>
>>The application is a website and it is built using ASP.NET (VB.NET)
>>
>>Any opinions would be great.
> Hi Jack,
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
> I don't see any advantage in moving these columns to a seperate table,
> unless you are approaching the max number of columns per table (quite
> unlikely) or unless you need to store these yes/no values only for a
> limited subset of your customers.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Another possible beneficial thing you can do with a 'side-car' table like
this.
Save only one row per distinct combonation of flags and store the integer
key to that bitmap pattern in the main table.
With an indexed column you would be able to more quickly select on those
values.

FamilyType Table
-----
FamilyType Married BothWork HaveKids Descripiotn
--------------------
1 Y Y N DINK
2 Y N Y That Type
3 N Y N Whatever
etc...

customer Table
----

CustomerKey Name FamilyType
----------
1 joe and linda 1
2 bob and rob 3
3 karl and cindy 2

You can use a view, a proc, or an instead of trigger to make sure that
instead of updating the flags, you simply select the correct family type
code.|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))

What can be more descriptive that 0 and 1?

Using character values when there are perfectly usable numeric values
as there are for binary value is asking for trouble. Let's see was
Y/N, T/F, J/N or something else?

We once had a lot of such columns in our database, but almost all are
bit columns these days. And, no, while the name of the type is ab_yesno,
the values are not Y/N, but J/N.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jack (jack-b@.humlog.com) writes:
> Hi there, I'm not sure if this the appropriate group so apologies if
> it lies outside the boundary.
> Senario: I have a customer table with contains a bunch of different
> bit values that represent true/false values pertaining to the
> customer. I decided for the purpose of clarity I would move these
> values into another table. This way I would keep the customer details
> (name, age, etc) separate from these values.
> Question: Is this a good idea? Or could I somehow tally up all these
> bit values and store it in one field in the customer table?

The last thing would be a bad idea. That's more cumbersome to use, and
more prone to errors.

As for shuffling the bit columns to another table, well, it depends. We
actually did this with our accounts table, since we found that we were
costantly adding bit columns to it, of which some were for tiny marginal
features that were only referred to in one or two places.

Therefore we move these less used bit columns to this table:

CREATE TABLE accountflags (accountno int NOT NULL,
flag varchar(15) NOT NULL,
CONSTRAINT pk_flags PRIMARY KEY (accountno, flag))

That is, the bit column became rows. And a flag is set for an account
if there is a row in the table, else not. The flag themselves are
defined in another table, one that is loaded with data from scripts
when the database is created.

But if all you want to do is move bit columns to a side table, but
keep them as columns, I'm not sure that it's worth the effort.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.

SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.

Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)

What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!

There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.

In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?

Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:

1) Ignore the problem. This is actually what <i>most<i> newbies do.

2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.

Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?

You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!|||--CELKO-- (jcelko212@.earthlink.net) writes:
> machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.

Completely wrong. In some language you can say "String" and the language
will allocate for you. In SQL Server you have to specify the max length
of any character column you want to use, and you cannot have more than
8000 bytes on a page. (There is varchar(MAX) in SQL 2005, but practice
will remain to specify an upper bound.)

As a further example, consider the whole range of tinyint, smallint,
int and bigint.

Since you store data on disk, the actual storage format matters a whole
lot to database programmers. Too much storage has bad effect on performance.

And of course, as datatype, BIT has nothing machine-level at all. It is
just a name for something that can have two values. While it could have
been called Boolean, Celko, Pamela or Urban, the name bit is quite good,
because most people have an understanding of how much information you
can cram into a bit.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Surely, no good programmer would ever write non-portable
> code by getting to such a low level as bit fiddling!!
There's nothing wrong with fiddling with your bits is there ? :-)