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 data type

Is there a boolean data type in SQL? I'm trying to bind a number of check
boxes and radio buttons in a .net app to some columns in a SQL table and I
don't see any boolean data type. What should I do if I want to assign a data
type "boolean" to a table's column in SQL?
--
TSThere is not... Many developers use CHAR(1), with a check constraint
restricting the values to 'Y' or 'N'. Some people like to use BIT instead,
but I personally prefer the former approach...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> Is there a boolean data type in SQL?
No, you probably want BIT, though it can take 0, 1, or false. Many people
use CHAR(1) and set a constraint to be either T/F or Y/N.
A|||Well, there is the BIT type. You could also just use a numeric field (of
some sort) and treat it as 0=FALSE, !0=TRUE.
I _think_ bit is preferable, because being a bit, it is either 0 or 1
(or -1, im not sure) and is therefore a logical boolean, but a plain numeric
is more portable if that is a concern.
HTH.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> No, you probably want BIT, though it can take 0, 1, or false.
Of course, I meant 0, 1, or NULL.|||Uh, oh. Now you're starting to think like SSMS :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u$vi6Yt7FHA.2608@.TK2MSFTNGP10.phx.gbl...
> Of course, I meant 0, 1, or NULL.
>|||> Uh, oh. Now you're starting to think like SSMS :)
Nah, I would have put NULL in italics and spelled out 0 as "false" and 1 as
"true"... :-)|||There isn't a boolean type in SQL but you can use bit type.
If you want to assign a data type "boolean" to table column ( I assume that
column's name is Male), you can do
if chkMale.Checked =true then
insert into ... (PersonID, Name, Male) values ('P001','Richard',1)
else
insert into ... (PersonID, Name, Male) values ('P001','Richard',0)
end if
That's it.
"TS" wrote:

> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a da
ta
> type "boolean" to a table's column in SQL?
> --
> TS

Boolean - Expanded/Collapsed

I have created a matrix where I have booleans on the rows and columns. They are initially in the collapsed state. For reporting purposes, I would like the initial state to to be expanded or at list a quick fix to quickly convert the collapsed booleans to expanded booleans.

I have navigated to the Layout tab and right-clicked the fields that are tied to the boolean and then clicked on the properties. This bring sup the Textbox Properties. From here, I click on the Visibility tab. At the bottom of the textbox is a section "Initial appearance of the toggle image for this report item:". The default choice is marked as collapsed (+). One would logically think that you you would have to do then is select the expanded (-). choice. Well, when I do this, all it simply does is chance the icon from a '+' to a '-' and the fields are still shown as collapsed.

Any ideas on what I need to do?

ttt|||

If I am understand it right, what you will have to do is

1. Select the Entire row and go to the properties

2. Go to "Visibility " --> "Hidden" set this to False if you want this to be expanded or to true.

|||

Expand/Collapse

1) Create a parameter and name it something like "Expand" and name the Prompt "Expand All" or whatever you choose

2) Set the parameter to Boolean

3) Set the default value Non-queried to "True"

4) Click on OK

5) Select properties for the control box that is supporting the +- like you said below

a)"I have navigated to the Layout tab and right-clicked the fields that are tied to the boolean"

6) Click on Visibility

7)Select Expression: and insert this "=Iif(Parameters!Expand.Value = True, True, False)"

8)Click OK

9)Select properties for the matrix and select "Groups"

10) Select the group where you have the "Visibility can be toggled by another report item"

11) Select Expression: and insert this "=Iif(Parameters!Expand.Value = True, False, True)"

12)Click on OK

13)Click on OK

Give it a shot. . .

When you change the parameter, you will need to reselect the view report button.

|||

This post is in response to Techquest ...

If I go to the Layout View, select the row, then right-click and select Properties, I do not see an option for "Visibility". However, if I select just one field, then right-click and select Properties, I do see an option for "Visibility". I have tried to change every single field by doing this (and if I remember correctly -- because it was last week) and it still does not do what I am looking for.

|||

Hi JCU1343,

Your right, you do not want to select the entire row, but instead you want to select just one field in the table. That's where you will find the Visibility control. It should be the field where the "+" and "-" sign will appear. When you insert the code: "=Iif(Parameters!Expand.Value = True, True, False)" in the Expression at the bottom of the "Visibility" tab, this gives control at the parameter level to change the "+" to "-" and back again. Then having selected the properties of the group in your table and selecting the "Visibility" tab for the group you should insert "=Iif(Parameters!Expand.Value = True, False, True)" which will expand/collapse the entire group depending on which choice you selected and you should also see the "checked" field here indicating the "Visibilty can be toggled by another report item". Every time you select the parameter you have to re-run the report.

Hope this helps. . .

Don't forget this feature depends on a parameter, and I know it works because I've used it on at least 8 reports for my company.

|||

FLHTCUI - Arizona Harley Rider:

Thank you so much for your posts (especially the first post). That is exactly what I was looking for/wanted. I truly appreciate it. You are a big help!

Can I direct you over to another post that I created that no one has been able to provide an answer to? It can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1852697&SiteID=1

Blanks in columns

Quick Question I hope.

Does having a lot of blanks in a column cause errors ?

In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank

Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.

This is the error description

The data value cannot be converted for reasons other than sign mismatch or data overflow.

Anyone know of a solution / reason why this keeps happening

Thanks

Without seeing the package, no. It would apear that there is an implicit conversion going on somewhere which is failing. You have the error files so it shouldn't be difficult to work out what is happening. Do you know which column is failing?

-Jamie

|||It would also help for us to know what type of source you are using. I would guess that your using a Flat File Source.

Please give us a little more info about the structure of the package and we'll be in a better position to offer suggestions.

Larry Pope|||

Will do, Thanks for the help

Package Details are as follows

Import data from a flat file source in csv format with semicolons as the delimiters

Change the date's using a derived column with an expression like
(DT_DBTIMESTAMP)[Column 21]

Then to a SQL Server Destination where that column wher e the table for that column would read [Column 2] DATETIME

I have also thrown in a ErrorDescription Package (Which is great Thanks Jamie :D) and also a package that tells me which column is failing (Which I got from Simon)

From these I can work out which column seems to be failing although when I switch that to Ignore errors another date column usually fails.

Is it REALLY bad practice to have all columns on "Ignore errors" ....when the data seems to be fine when I look over it after in the table I cant find anything wrong with it.

If there was an charactor such as '# 'or a random / ,\ surely that would just fail one row and not all the rows

|||Are you trying to cast nulls to a DT_DBTIMESTAMP. I think there

may be problems with this. Jamie might be able to confirm this.

You might want to change your derived column to

ISNULL([Column 21] ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)([Column

21]). And be aware that trying to cast invalid dates could also

raise errors (example '0000-00-00').

Have you put a data viewer between the source and the derived column to see how the Flat File Source is interpreting file?

Larry Pope|||

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Thanks guys ill try that,

Just realised aswell that 1 column is in the format MM/DD/YYYY while the other is DD/MM/YYYY

Should be fun trying to sort them out

|||

Can someone please elaborate on this occurrence. I believe we are experiencing a flavor of this.

We have a package that checks an amount field for values greater than 0.

When the value is greater than 0 then we populate with the processing date, else we place a Null Timestamp value.

We are having failed inserts because our target column is SmallDateTime and somehow the time value of "0000-00-00 00:00:00" is being created.

According to the logic, there should only be a valid date or a null date....no zeros. Does anyone have any information on this issue?

ChargeOffAmount > 0 ? (DT_DBTIMESTAMP)ReportingDate : NULL(DT_DBTIMESTAMP)

Thanks in advance...

Jamie Thomson wrote:

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Romeo,

Is there a default value specified in the column definition in the database?

|||

Phil, no, there is no default value specified for this column.

I also failed to mention that these values are not always consistently produced...meaning that we can try to recreate the occurrence and then the date will be generated correctly and load. Makes debugging not so easy and causes me to doubt my sanity.....well, that's never been in doubt, I get opinions on both sides....

Blank values Replicating to Oracle

Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.

I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx

Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @.creation_script parameter of sp_addarticle

Here is the segment of the Publication Script referencing sp_addarticle:

exec sp_addarticle
@.publication = N'mike_hayes_test_replication2',
@.article = N'mike_hayes_test_replication',
@.source_owner = N'bmssa',
@.source_object = N'mike_hayes_test_replication',
@.type = N'logbased',
@.description = N'',
@.creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00,
@.identityrangemanagementoption = N'manual',
@.destination_table = N'mike_hayes_test_replication2',
@.destination_owner = N'bmssa',
@.vertical_partition = N'false'
GO

I added the @.creation_script value, and changed the schema_option to 0x00.

The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @.creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.

Any ideas?

Thanks for your help.

Mike Hayes

Hi Mike, you should specify the path to the file containing the create table statement for the @.creation_script parameter instead of just the "create table" statement.

Hope that helps,

-Raymond

|||Thanks for the reply. So this should be an Oracle script? Should I refer to it on the server (i.e. c:\scripts, where c:\ refers to the SQL Server drive)?

Thanks,

Mike Hayes|||

The syntax can arguably be anything compatible with Oracle although the safer thing to do is to just modify what the snapshot agent would have generated otherwise. The path should be something that is accessible(and\or relative) to the snapshot agent process since your file will then be copied by the snapshot agent to the snapshot folder. I am actually a bit surprised that the snapshot agent didn't die a horrible death trying to copy the "weird path" so something may be amiss here.

Come to think of it, would it be easier to just modify the script generated by the snapshot agent?

-Raymond

Blank spaces in my Crosstab

I'm using Crystal 10. I have several summary fields in my crosstab. Three of those columns occasionally show blank spaces (depending on the date range I use), even though I've formated the cells to print a zero if there's no data. Even the borders disappear.

The only thing unique about these 3 columns is that they hold 'sums' rather than 'counts'. The zero formatting works for all other fields.

What can I do to stop this disappearing act. It makes the crosstab look really strange.If any one of the values in Null then Sum is Null
Make sure values are not null

Blank fields in Excel file

I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?
The code is as follows:

Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn)
Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader()
Dim row As Integer = 0

While excelreader.Read()
NameValsBuffer.AddRow()
NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0)))
NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1)))
NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2)))
NameValsBuffer.CompanyId = Me.Variables.CompanyId
NameValsBuffer.UserId = Me.Variables.UserId
End WhileThe thread title says "blank fields", while the text of the question references "blank columns", and lastly "blank spaces". Can you give a small pictorial representation of the problem? It sounds relatively easy to solve, but I'd like to make what the nature of the issue is first.|||

Item

ItemDescription

TaxCode

AAA123

Sample Item 1

XXXXXX

BBB255

Sample Item 2

AAAAA

CCC366

Sample Item 3

BBBBB

XDDD489

Sample Item 4

CCCCC

5EEE

Sample Item 5

DDDDDD

6FFF

Sample Item 6

EEEEEE

GGG

Sample Item 7

FFFFFFF

HHH

Sample Item 8

GGGGG

III

Sample Item 9

HHHHHH

JJJ

Sample Item 10

IIIIIIIIIII

KKK

Sample Item 11

ZZZZZZZZ

MMM

The value in the last row for the ItemDescription and TaxCode is null..so thats the problem I am facing when I am trying to validate and upload data.


|||First off, I'm not sure why you're using a script component, when an ADO.NET connection with the following connnection string will work fine:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

In your dataflow, rather than script source, use a DataReader source and set your select statement in the SqlCommand property (e.g. SELECT * FROM [sheet1$] WHERE ...) . At that point, you can do whatever you want in the pipeline, including using conditional splits and/or derived columns as appropriate to handle the NULL ItemDescription and TaxCode pipeline columns, and adding your static columns via a derived column transform.

However, if for whatever reason you need to use a script component, you can use the following CreateNewOutput rows override to handle null cells. "OrElse" is used because its a short-curcuit operator, and .Item is used because it returns an Object, which can hold either a String or a DBNull object. Note there is no "WHERE" clause either, since nulls are handled in the while loop.

Public Overrides Sub

CreateNewOutputRows()
Dim

excelcmd As OleDbCommand = New OleDbCommand("SELECT

Item,TaxCode,ItemDescription FROM [Sheet1$]", excelConn)
Dim

excelreader As OleDbDataReader =

excelcmd.ExecuteReader()
Dim row

As Integer = 0

While

excelreader.Read()
With

NameValsBuffer
.AddRow()
.ItemCode = CStr(IIf(IsDBNull(excelreader.Item(0)) OrElse excelreader.GetString(0).Length = 0, "#", excelreader.Item(0)))
.TaxCode = CStr(IIf(IsDBNull(excelreader.Item(1))

OrElse excelreader.GetString(1).Length = 0, "#", excelreader.Item(1)))
.ItemDescription = CStr(IIf(IsDBNull(excelreader.Item(2)) OrElse excelreader.GetString(2).Length = 0, "#", excelreader.Item(2)))
End

With
End While
End Sub

|||Just to add to what jaegd mentioned, this may also be done with following components: Excel Source, Conditional Split, and your destination component.

In conditional split the condition could be set as follows:
ISNULL(ItemDescription) || ISNULL(TaxCode)

HTH..

Blank fields in Excel file

I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?
The code is as follows:

Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn)
Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader()
Dim row As Integer = 0

While excelreader.Read()
NameValsBuffer.AddRow()
NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0)))
NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1)))
NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2)))
NameValsBuffer.CompanyId = Me.Variables.CompanyId
NameValsBuffer.UserId = Me.Variables.UserId
End WhileThe thread title says "blank fields", while the text of the question references "blank columns", and lastly "blank spaces". Can you give a small pictorial representation of the problem? It sounds relatively easy to solve, but I'd like to make what the nature of the issue is first.|||

Item

ItemDescription

TaxCode

AAA123

Sample Item 1

XXXXXX

BBB255

Sample Item 2

AAAAA

CCC366

Sample Item 3

BBBBB

XDDD489

Sample Item 4

CCCCC

5EEE

Sample Item 5

DDDDDD

6FFF

Sample Item 6

EEEEEE

GGG

Sample Item 7

FFFFFFF

HHH

Sample Item 8

GGGGG

III

Sample Item 9

HHHHHH

JJJ

Sample Item 10

IIIIIIIIIII

KKK

Sample Item 11

ZZZZZZZZ

MMM

The value in the last row for the ItemDescription and TaxCode is null..so thats the problem I am facing when I am trying to validate and upload data.


|||First off, I'm not sure why you're using a script component, when an ADO.NET connection with the following connnection string will work fine:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

In your dataflow, rather than script source, use a DataReader source and set your select statement in the SqlCommand property (e.g. SELECT * FROM [sheet1$] WHERE ...) . At that point, you can do whatever you want in the pipeline, including using conditional splits and/or derived columns as appropriate to handle the NULL ItemDescription and TaxCode pipeline columns, and adding your static columns via a derived column transform.

However, if for whatever reason you need to use a script component, you can use the following CreateNewOutput rows override to handle null cells. "OrElse" is used because its a short-curcuit operator, and .Item is used because it returns an Object, which can hold either a String or a DBNull object. Note there is no "WHERE" clause either, since nulls are handled in the while loop.

Public Overrides Sub

CreateNewOutputRows()
Dim

excelcmd As OleDbCommand = New OleDbCommand("SELECT

Item,TaxCode,ItemDescription FROM [Sheet1$]", excelConn)
Dim

excelreader As OleDbDataReader =

excelcmd.ExecuteReader()
Dim row

As Integer = 0

While

excelreader.Read()
With

NameValsBuffer
.AddRow()
.ItemCode = CStr(IIf(IsDBNull(excelreader.Item(0)) OrElse excelreader.GetString(0).Length = 0, "#", excelreader.Item(0)))
.TaxCode = CStr(IIf(IsDBNull(excelreader.Item(1))

OrElse excelreader.GetString(1).Length = 0, "#", excelreader.Item(1)))
.ItemDescription = CStr(IIf(IsDBNull(excelreader.Item(2)) OrElse excelreader.GetString(2).Length = 0, "#", excelreader.Item(2)))
End

With
End While
End Sub

|||Just to add to what jaegd mentioned, this may also be done with following components: Excel Source, Conditional Split, and your destination component.

In conditional split the condition could be set as follows:
ISNULL(ItemDescription) || ISNULL(TaxCode)

HTH..

Black area where column is hidden in a table when exporting to PDF

I have a very strange problem. I'm using a table and I've
conditionally hidden columns before without any problems, but all of a
sudden, this one report I'm working on has a conditionally hidden
column in the table, but when the column is hidden and I export the
report as PDF, there is a solid black rectangle. It's not directly
where the column is hidden (the hidden column is the next to last
column to the right), but it's one column further to the
right...outside the table. So for instance, if all the columns were
shown in the table, the black rectangle would be to the right of the
table...where another column would go if I were to add one more on the
end. So with the hidden column, it basically goes...end of the table,
white space column, then black rectangle.
Anyone have any ideas what's up with this? I'm using reporting
services 2000 with SP2. Thanks.
RyanHi Ryan
[apologies if you have already received a mail through google groups from me]
did you ever find a solution to this issue, i am having exactly the same
problem of a black square being tacked on to the end of a table?
I'd be grateful if you do have a solution
thanks
"Ryan" wrote:
> I have a very strange problem. I'm using a table and I've
> conditionally hidden columns before without any problems, but all of a
> sudden, this one report I'm working on has a conditionally hidden
> column in the table, but when the column is hidden and I export the
> report as PDF, there is a solid black rectangle. It's not directly
> where the column is hidden (the hidden column is the next to last
> column to the right), but it's one column further to the
> right...outside the table. So for instance, if all the columns were
> shown in the table, the black rectangle would be to the right of the
> table...where another column would go if I were to add one more on the
> end. So with the hidden column, it basically goes...end of the table,
> white space column, then black rectangle.
> Anyone have any ideas what's up with this? I'm using reporting
> services 2000 with SP2. Thanks.
> Ryan
>

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.
The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
David Portas
SQL Server MVP
|||Thank you very much. That makes sense.
John

>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||Hi
VB: -1 = True
All other languages: +1 = True.
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you very much. That makes sense.
> John
>
> there's no good
> implicitly cast to 1,
> Boolean values and
> You can avoid this
> transformation. Alternatively,