Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!
Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!
|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.
|||On Apr 4, 11:07Xam, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? X"Empty of data" and "blank" are two different
> things, in my opinion. XEmpty of data means NULL (and you have the correct
> syntax, of that were the case). XBlank means an empty string, e.g. ''. XIn
> which case,
> WHERE CompanyID = ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) = ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptual
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!
|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,[vbcol=seagreen]
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql

Having conversion issues - String to Float

I am running into some issues with conversion and would appreciate someone who can help on this.

I need to roll up a column which contains numeric (float) data but it is stored in a varchar field.

I was trying to do the following (please note that Activity field is varchar(50) in MyTable):

SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable

and i get the following error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '-39862.8' to data type int.

I even tried with case statement (as below) but still got same issue.

SELECT CASE ISNUMERIC(NULLIF(Activity,0))

WHEN 1 THEN

CONVERT(float, (NULLIF(Activity,0)))

ELSE 0

END

from MyTable

Thanks,

Ashish

Try using 0.0 rather than 0 in the NULLIF functions. Also, do you mean to use ISNULL, or NULLIF? NULLIF returns a NULL, if the expressions are equal, while ISNULL returns the second argument if the first is NULL.
|||Yes. I want to use ISNULL. I changed it but I still get the error. Even with '0.0'|||What happens when you run the following:

Code Snippet

select *

from MyTable

where isnumeric(activity) = 0

|||SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable where isnumeric(Activity)=1|||

Are you sure that you want to use NULLIF()? (I would think that ISNULL() would be a better option.)

Also note that the isnumeric() test will PASS because '-39862.8' will always test to be a number.

This works as you want.

Code Snippet

DECLARE @.Activity varchar(50)

SET @.Activity = '-39862.8'

SELECT cast( isnull( @.Activity, 0 ) AS float )

--
-39862.800000000003

|||Good point about isnull(), Arnie.

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for
each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or
if I'm just missing something. Anyway the scan results is stored as an
XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
Now here is the insert statement for the table I am having trouble
with.
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
#temp.Name
If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> Using ms sql 2000
> I have 2 tables.
> I have a table which has information regarding a computer scan. Each
> record in this table has a column called MAC which is the unique ID for
> each Scan. The table in question holds the various scan results of
> every scan from different computers. I have an insert statement that
> works however I am having troulbe getting and update statement out of
> it, not sure if I'm using the correct method to insert and thats why or
> if I'm just missing something. Anyway the scan results is stored as an
> XML document(@.iTree) so I have a temp table that holds the relevent
> info from that. Here is my Insert statement for the temporary table.
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
>
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote:

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)

Now here is the insert statement for the table I am having trouble
with.

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON
tblScanAttribute.Name =
#temp.Name

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.rhaazy (rhaazy@.gmail.com) writes:
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON
> tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:
> rhaazy (rhaazy@.gmail.com) writes:
> > INSERT INTO #temp
> > SELECT * FROM openxml(@.iTree,
> > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > WITH(
> > ID nvarchar(50) './@.ID',
> > ParentID nvarchar(50) './@.ParentID',
> > Name nvarchar(50) './@.Name',
> > scanattribute nvarchar(50) '.'
> > )
> > Now here is the insert statement for the table I am having trouble
> > with.
> > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > ScanID, AttributeValue, DateCreated, LastModified)
> > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > #temp.scanattribute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribute JOIN #temp ON
> > tblScanAttribute.Name =
> > #temp.Name
> > If there is a way to do this without the temporary table that would be
> > great, but I haven't figured a way around it yet, if anyone has any
> > ideas that would be great, thanks.
> I have some difficulties to understand what your problem is. If all
> you want to do is to insert from the XML document, then you don't
> need the temp table, but you could use OPENXML directly in the
> query.
> But then you talk about an UPDATE as well, and if your aim is to insert
> new rows, and update existing, it's probably better to use a temp
> table (or a table variable), so that you don't have to run OPENXML twice.
> Some DB engines support a MERGE command which performs the task of
> UPDATE and INSERT in one statement, but this is not available in
> SQL Server, not even in SQL 2005.
> If this did not answer your question, could you please clarify?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fixed it no problems.
rhaazy wrote:
> My app runs on all my companies PCs every month a scan is performed and
> the resulst are stored in a database. So the first time a scan is
> performed for any PC it will be an insert, but after that it will
> always be an update. I tried using openxml in my insert statement but
> kept getting an error stating my sub query is returning more than one
> result... So since I couldn't do it that way I'm trying this method.
> All the relevent openxml is there I just couldn't figure out how to
> insert each column using it. If you have any suggestions I'm open to
> give it a try.
> Erland Sommarskog wrote:
> > rhaazy (rhaazy@.gmail.com) writes:
> > > INSERT INTO #temp
> > > SELECT * FROM openxml(@.iTree,
> > > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > > WITH(
> > > ID nvarchar(50) './@.ID',
> > > ParentID nvarchar(50) './@.ParentID',
> > > Name nvarchar(50) './@.Name',
> > > scanattribute nvarchar(50) '.'
> > > )
> > > > Now here is the insert statement for the table I am having trouble
> > > with.
> > > > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > > ScanID, AttributeValue, DateCreated, LastModified)
> > > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > > #temp.scanattribute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribute JOIN #temp ON
> > > tblScanAttribute.Name =
> > > #temp.Name
> > > > If there is a way to do this without the temporary table that would be
> > > great, but I haven't figured a way around it yet, if anyone has any
> > > ideas that would be great, thanks.
> > I have some difficulties to understand what your problem is. If all
> > you want to do is to insert from the XML document, then you don't
> > need the temp table, but you could use OPENXML directly in the
> > query.
> > But then you talk about an UPDATE as well, and if your aim is to insert
> > new rows, and update existing, it's probably better to use a temp
> > table (or a table variable), so that you don't have to run OPENXML twice.
> > Some DB engines support a MERGE command which performs the task of
> > UPDATE and INSERT in one statement, but this is not available in
> > SQL Server, not even in SQL 2005.
> > If this did not answer your question, could you please clarify?
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspxsql

have elements even when column has NULL value

Hi guys,
Sorry if posted twice as I could not see the previous post...
I need to capture element name even when the column it is associate to has a NULL value. I can best describe this with an example...
Say for example we have 2 tables names tblProduct and tblCategory with following columns and relation...
tblProduct
ProductId int (PK)
ProductName varchar(30)
CategoryId int (FK to tblCategory)
tblCategory
CategoryId (PK)
CategoryName varchar(30)
table data...
tblProduct
ProductId ProductName CategoryId
123 testProduct1 777
345 testProduct2 NULL
678 testProdyct3 888
tblCategory
CategoruId CategoryName
777 testCategory1
888 testCategory9
999 testCategory44
Now I need to have XML output as...
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName><CategoryName></CategoryName></Product><Produ
ct>
...
...
</Product></Products>
In the above case, CategoryName element should be captured even when the product has null value for it.
So, you have an schema xsd as...
<?xml version="1.0" encoding="UTF-8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="ProductCategory"
parent="tblProduct"
parent-key="CategoryId"
child="tblCategory"
child-key="CategoryId" /></xsd:appinfo></xsd:annotation><xsd:element name="Products" sql:is-constant="1"><xsd:complexType><xsd:sequence maxOccurs="unbounded"><xsd:element ref="Product"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element name="P
roduct" type="ProductDetails"
sql:relation="tblProduct"
sql:key-field="ProductId"/><xsd:complexType name="ProductDetails"><xsd:sequence><xsd:element name="ProductId"
type="xsd:integer"
sql:field="ProductId"/><xsd:element name="ProductName"
type="xsd:string"
sql:field="ProductName"/><xsd:element name="CategoryName"
type="xsd:string"
sql:field="CategoryName"
sql:relation="tblCategory"
sql:relationship="ProductCategory"/></xsd:sequence></xsd:complexType></xsd:schema>
The problem is that you get below output when CategoryId is null for some of the products
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName>
***** error ***** does not get captured
</Product><Product>
...
...
</Product></Products>
I would apprecaite if anyone has a solution for this.
Thanks
-Sid
"sid" <anonymous@.discussions.microsoft.com> wrote in message
news:FBC3236C-9C0E-49E1-971D-4E7FD4F638DF@.microsoft.com...
> Hi guys,
> Sorry if posted twice as I could not see the previous post...
> I need to capture element name even when the column it is associate to has
> a NULL value. I can best describe this with an example...
See this FAQ:
http://sqlxml.org/faqs.aspx?faq=14
Bryant

Harmonic Mean

Hello everyone.
My first time posting here! I have recentl been computing the geometric mean of a column in out Swl Server 200 Db using the following script:
@.PE_Geo = exp(avg(log(PE1)))

this has worked greate, however I need to change this to the harmonic mean. Anyone have any ideas on the cimplest way to do this.

A simple example of the harmonic mean:
If you want the harmonic mean of 10 and 20, you first
take 1/10 and 1/20, find their average, which is 3/40, and then take
the reciprocal of that, 40/3.

In algebra, the harmonic mean h of two numbers a and b is

1 / ( (1/a + 1/b) / 2),

or in other words

1/m = 1/2 (1/a + 1/b).

Thanks for any help!select 1/(sum(1/[value])/count([value]))

You made need to cast your value as a different datatype to prevent rounding errors.

blindman|||Originally posted by blindman
select 1/(sum(1/[value])/count([value]))

You made need to cast your value as a different datatype to prevent rounding errors.

blindman

Thank you!
Float ok?|||Depends on your data. Float and Real are approximate values, and I don't know how they might propogate errors. Numeric and Decimal are more accurate, but you must define their accuracy ahead of time.

blindman|||Thanks again...
BTW: Have an experts-exchange account: 100pts!

http://www.experts-exchange.com/Databases/Q_20704165.html|||I've thought about joining Experts Exchange, but what would I do with the 100 points? Can I trade them in for a toaster?

blindman|||Hehe...

hard question.. is it possible to update many column of data in 1 query?

in my original database have a column which is for "path" ,the record in this column is like → 【mms://192.12.34.56/2/1/kbe-1a1.wmv】

this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..

I have to change 【 mms://192.12.34.56/2/1/kbe-1a1.wav】 to 【 mms://202.11.34.56/2/1/kbe-1a1.wav】

I tried to find the reference book and internet . can't find out the answer for this problem.

can you help? or maybe is it a impossible job?

thanks

As long as you're using an exact match it shouldn't be an issue. (UPDATE TableName SET FieldName = NewFieldValue WHERE FieldName = OldFieldValue)

But, if you're talking about have to fix the path for a lot of different files, you'll have to do either a complex sproc that can parse the path or a small application that pulls the initial set using a LIKE query for the path to be changed and then turns around and does an UPDATE on each record to the new path.

|||

thank you

problem is to fix the path but I can't really understand your suggest

can you explain more or just give me a simple example please

I appreciate your help, thank you very much

|||

You can use REPLACE to fix your path value. Here is a sample:

UPDATE pathToChangeSET path =replace(path,'192.12','202.11')

It will replace all 192.12 with 202.11 if you run this query for your table.

|||

thank you

sorry I didn't make my question clearly ..

the problem can't be resolved by use replace

for example

in the same column " voice"

some are looks like → mms:// 1.2.3.4/1/2/voice.wma

some are looks like → mms://www.showhigh.com/1/2/voice.wma

also we have others like → "this voice is mms://1.2.3.4/1/2/voice.wma " -- the last kind of data we don't know how many cheracter front of the path...

-----------

do you have any good idea to change the path without manually

thanks again..

|||

Do you want to change mms:// 1.2.3.4/, mms://www.showhigh.com/, and this voice is mms://1.2.3.4/ or anything up to the first slash (/) to mms://202.11.34.56/? If this is the case, you need to find the location of this slash and get this job done with a string operation.

Post some sample data and the result you are expecting. It will save time to get result. Thanks.

|||

sorry.. not make my question clearly enough still..

here are the example for my question..

in Table name Movie, have a column name "Movie_Voice"

Voice_no | Movie_Voice

1 | mms://1.2.3.4/1/65/05-65-01.wma want change it to be → mms/1.2.3.4/1/65/05-65-01.wma

2 | mms://media.movie.com/12/79/ef13-79-01.wma want change it to be → mms/12/79/ef13-79-01.wma

3 | there are the voice path mms://1.2.3.4/23/230.wma want change it to be → there are the voice path mms/1.2.3.4/23/230.wma

4 | your voice path mms://media.movie.com/98/2/23.wma want change it to be → your voice path mms/98/2/23.wma

------------------------------------------------------------

the voice_no 3 and voice_no 4 are the hardest part for me to think out a way to resolve ,because I don't know what are front of the mms path

the reason why I have to change the data in database . I move all wma files to root and creat a file name mms, so now I have to change all path

which alreday list in database...

thank you

|||

Hi jc,

If these are the only patterns or u have more patterns. If you can provide me a list of pattern i can create a regex to update the column for you which you can use to update the column

Satya

|||

unfortunately... I can't list a pattern for this problem

because... the pattern may be up to 4000 or more...

some of tables which including all html tag to 1 column . the column not have the mms path not only have some html tag ..

|||

Can u send me the exported table in a txt file or a mdf file

satya.tanwar@.gmail.com

coz without seeing the patterns nobody will be able to produce a desired solution

Satya

|||

thank you , I have just sent !

|||

Hi Try this function,

Select dbo.PatternReplace(TestColumn,'%mms://%/','mms/'),TestColumnfrom pro_Lesson_Text

CREATEFUNCTION dbo.PatternReplace

(

@.InputStringNVARCHAR(MAX),

@.PatternVARCHAR(100),

@.ReplaceTextNVARCHAR(MAX)

)

RETURNSNVARCHAR(MAX)

AS

BEGIN

DECLARE @.ResultNVARCHAR(MAX)SET @.Result=''

-- First character in a match

DECLARE @.FirstINT

-- Next character to start search on

DECLARE @.NextINTSET @.Next= 1

-- Length of the total string -- 8001 if @.InputString is NULL

DECLARE @.LenINTSET @.Len=COALESCE(LEN(@.InputString), 8001)

-- End of a pattern

DECLARE @.EndPatternINT

WHILE(@.Next<= @.Len)

BEGIN

SET @.First=PATINDEX('%'+ @.Pattern+'%',SUBSTRING(@.InputString, @.Next, @.Len))

IFCOALESCE(@.First, 0)= 0--no match - return

BEGIN

SET @.Result= @.Result+

CASE--return NULL, just like REPLACE, if inputs are NULL

WHEN @.InputStringISNULL

OR @.PatternISNULL

OR @.ReplaceTextISNULLTHENNULL

ELSESUBSTRING(@.InputString, @.Next, @.Len)

END

BREAK

END

ELSE

BEGIN

-- Concatenate characters before the match to the result

SET @.Result= @.Result+SUBSTRING(@.InputString, @.Next, @.First- 1)

SET @.Next= @.Next+ @.First- 1

SET @.EndPattern= 1

-- Find start of end pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))= 0SET @.EndPattern= @.EndPattern+ 1

-- Find end of pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))> 0

AND @.Len>=(@.Next+ @.EndPattern- 1)

SET @.EndPattern= @.EndPattern+ 1

--Either at the end of the pattern or @.Next + @.EndPattern = @.Len

SET @.Result= @.Result+ @.ReplaceTextSET @.Next= @.Next+ @.EndPattern- 1

END

END

RETURN(@.Result)

END

Result looks good Check my mail for the Results. Let me know in case anything else is required

Satya

|||

It looks like this would do it as well:

UPDATE Movie

SET Movie_voice=REPLACE(REPLACE(Movie_voice,'mms://media.movie.com/','mms/'),'mms://','mms/')

|||

Hi motley,

Its a good try but the problem is he has different patterns in the data to update. So need to update patterns not static string.

satya

|||

Maybe I misunderstood what he was asking for, but wouldn't the code you gave change:

mms://1.2.3.4/stuff to mms/stufff? And that's not what he showed what he wanted for #1 & #3.

Handling Empty Strings In DTS

Hello,
I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:

DTSDestination("TTLCrd") = CInt(DTSSource("Col004"))

The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.

Thank you for your help.

cdun2Avoid using DTS to insert directly into production tables.
Avoid putting logic or code that manipulates data in your DTS package.
Use DTS for moving data from point A to point B. All the other features of DTS (or the new SSIS) are crap, and lead inevitably to bad application design.

Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.|||Thank you for your response!
cdun2|||Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.
So what you say is that you should have staging tables (potentially with all nvarchar field if you for instance import from text files), generate a whole lot of disk activity, before you kick off stored procs to do all the work for you? I cannot see why one would want to do it that way. I'm pretty satisfied with the way SSIS work, and would very much like to know why you discourage use of the SSIS features.|||I'm totally with blindman on this. My question in turn would be why would you want to incorporate data\ business logic into discrete, proprietry DTS\ SSIS packages? Virtually everything else I do in T-SQL unless there is no alternative. Even if I used DTS or SSIS at all it would be to get stuff from outside the database to inside it with as little fuss as possible - nothing more. I see nothing gained throwing these ETL tools at the problem when the standard SS language is perfectly capable of everything I have come across so far.

You probably know though that I go further even than blindman and do not use DTS or SSIS at all.

As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.|||KISS.
By not creating code in my DTS package, I keep all of my logic in one place(the database) and in one language(SQL). That makes debugging much easier.
It also compartmentalizes my process, meaning I could use DTS, or BCP, or SSIS, or ASP, or a friggin' Access macro to load data, and my sproc will process it. I can even have multiple data flows going into the staging table.
My staging tables contain columns that record the source of each record, the time it entered the database, and a column for recording any processing errors. As my sproc cleanses, verifies, and loads the staging data, any discrepancies are noted within the ErrorStatus column. At the end of the process finding any records that failed and the reasons for their failure is a snap, and all I need to do is fix the existing staging records and reset the ErrorStatus column, and then I can rerun the sproc.
ETL has become bread and butter to me now. I can almost write these sprocs and packages with my eyes closed.
ROAC, how easily did your DTS packages upgrade to SSIS?|||As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.
Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.|||KISS.
ROAC, how easily did your DTS packages upgrade to SSIS?
Agreed. Keep things simple. If you are having a lot of data sources, I find it more easy working with SSIS than having tons of procs.

When it comes to upgrading, my DTS packages upgraded pretty well, but I know quite a few that did not, which of course is an issue. However, with almost the same arguments you come up with, you could tell to do the work in ASP.NET or Java as well, and have issues when .NET Framework or Java language is upgraded. Or, for that matter, when SQL Syntax changes.

As I said in my last post, please keep in mind that there are smaller countries and companies in the world. What's best for an enterprise is not neccessarily best for a small or medium sized business in Scandinavia or the Baltics. Thus, I think your advices perhaps should be something like "If you can afford ..., you should ...". Got my idea?

I have no problem seeing your points, I just cannot see it as the only solution.|||Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.I might add that the organisation I worked for prior to this was certainly not large, nor specialist like my present company, but I stuck to the same philosophy then. Actually - I think I just did :)

The most expensive resource of all is the bum in the seat supporting the hands keying in the code. This bum is likely to know T-SQL if he\ she is working with SQL Server. Why is it cost effective to introduce a GUI based ETL tool into the mix when bog standard T-SQL is perfectly capable? The worst short term ROI an employer gets from me is when I am wrestling with a new language\ gui\ tool etc.. I think the time aspect of my point might be a pressure for using SSIS\ DTS but not the resources - I'm afraid I have no idea what the difference is between EVA4000 and EVA6000 :)|||ROAC, why do you think my method of storing the data in staging tables and then running a sproc against them is going to entail more disk activity/server resources than an SSIS package?
Presumably the amount of data being imported is some fraction of the data that already exists in production, so if the server is beefy enough to handle day-to-day processing it should not choke on running sprocs against staging data. Especially since these are normally run as batch processes during maintenance hours.|||As the data is written do disk twice instead of once, yes it WILL consume more disk resources. If you are lucky enough to do all the job at night, well then you are definitely more lucky than I am. As I said previously, if you can have the extra disk load, your approach is the best. I'm not questioning that. I just want to make it clear, for you and other reders, that your scenario is not the only one around. Other people may have other needs, which will lead to other solutions. Doubling the disk activity required to import data is not always an option. You are lucky enough to have that option, but is it so hard to believe that other people not neccessarily have the same situation as you?

I think you perhaps should open your eyes a bit and look around, because there are solutions out there, behaving quite differently from those you are working with.|||Disk activity, hmmm..
Well, as a consultant who has created production and data warehouse ETL solutions for dozens of companies in many industries I can't say I've run into a situation where that was the deciding factor in the application architecture. But I'll grant such a situation may be possible.

Obviously I don't think my method is the only way to go. There are certainly many implementations using DTS, SSIS, or 3rd party tools (I recently had to suffer through a project where the client used a tool called DataStage).

The problem is, too many inexperienced people jump into creating DTS/SSIS solutions simply because THEY assume that THAT is the only method. When in reality (and I'm not backing down on this), these GUI tools are SELDOM the best method and lead to fragile designs that are difficult to debug or modify.|||BTW - Roac - totally agreed that everything deserves evaluation in the context of the entire circumstance. There are no absolutes - that is why we are having this fun discussion where we are sharing our opinions :)|||Its all good. <\TouchGloves>|||Lol - Roac - I've just remembered my first post addressing you was when I thought you had made a somewhat absolute statement too. What goes around comes around eh? On that one you were in agreement with blindman.|||I sit on the fence in these issues...

There are many things that I've written in DTS/SSIS where there is simple conversion of incoming data, and as long as there is a well defined way to deal with "bad" data this works very well. The thing that most DTS packages seemed to (incorrectly) assume was that all of the incoming data would be processed correctly on the first try.

When there are enough resources and time, the staging table approach works very well. You need to keep in mind that time is often my biggest problem, and that large amounts of data usually take large amounts of time to process. This can make it functionally impossible to stage some kinds of data because the processing window isn't large enough to support physically handling the data multiple times.

I don't have any inherant problem with either approach. Both work, and with the proper discipline for the ETL approach and sufficient resources (disk, time, etc) for the staging approach they can both produce the same results. Unfortunately, both discipline and resources are often scarce, so you need to find the best solution for the problem at hand.

-PatP

handling double quotes

Hi
i am importing data from table to flat file(csv). i have two problems
1. if a column has commas(,) it should not create a new column i.e the column in csv file can have commas
2.if a column has double quotes then csv file column should have double quotes. please help me.I am using derived column I dont know how to search double quotes in string.

if my table has 2 columns

col1 col2

a abc,"scfddf"ghisk

b bc,de

c de

my csv file should look like this

a abc,"scfddf" ghisk

b bc,de

c de

thanks

I don't understand... You define two columns in the file connection manager and just hook up the data flow columns to it.

In the examples you've given above, I don't see a need for the derived column transformation.

What are you seeing in your results?|||I agree, if you hook up an OLEDB Source to Flat file in your data flow task, this should already get handled automatically.

handling double quotes

Hi
i am importing data from table to flat file(csv). i have two problems
1. if a column has commas(,) it should not create a new column i.e the column in csv file can have commas
2.if a column has double quotes then csv file column should have double quotes. please help me.I am using derived column I dont know how to search double quotes in string.

if my table has 2 columns

col1 col2

a abc,"scfddf"ghisk

b bc,de

c de

my csv file should look like this

a abc,"scfddf" ghisk

b bc,de

c de

thanks

I don't understand... You define two columns in the file connection manager and just hook up the data flow columns to it.

In the examples you've given above, I don't see a need for the derived column transformation.

What are you seeing in your results?|||I agree, if you hook up an OLEDB Source to Flat file in your data flow task, this should already get handled automatically.

Handling columns with Nvarchar value

Hai All,
can any one out there help me in the following scenario.
I have a Remarks column in a table which is NVARCHAR field and length set
4000. This column value is set by a SP. Case is that when the remarks grows
beyond 4000 , how will i handle it. The requirement is, that I should not
ignore any remarks value.
Such Larger values how will i handle.
Thanks,
V.Boomessh
Hi
Look at NTEXT datatype in BOL. After NVARCHAR(4000), with SQL Server 2000,
your only option in NTEXT.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:61F38371-3BEF-436B-9421-FF37E6C30CF0@.microsoft.com...
> Hai All,
> can any one out there help me in the following scenario.
> I have a Remarks column in a table which is NVARCHAR field and length set
> 4000. This column value is set by a SP. Case is that when the remarks
> grows
> beyond 4000 , how will i handle it. The requirement is, that I should not
> ignore any remarks value.
> Such Larger values how will i handle.
> Thanks,
> V.Boomessh
>
>

handling columns with multiple values

I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:

Key

NotANormalizedCol

1

1324, 5124, 5435,5467

2

423, 23, 5345

3

52334, 53443, 1224

4

12, 4, 1243,66

is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.

I am pretty new to this, so I apologize if this is a trivial questions

Look at http://www.sommarskog.se/arrays-in-sql.html|||Hi,

I once wrote a function for that which can be found here in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Handling a Null datetime column

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

ISNULL is a lovely function useful for doing just that.

ISNULL(MyDateColumn, 'ITS NULL!')

returns ITS NULL if column MyDateColumn's value is NULL

|||

select donor_id,isnull(check_date,'No Value') from donors where check_date is null

If the column check_date consists the null value U will get the value No Value.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||I would say it depends what is the type of your field. If your filed is data time you shoudl convert it to string for output to not have problems with data type.Looks that ISNULL is trying to convert default for null value to the same type like tested value so you you would like to use syntax like this:ISNULL(DateField, 'Null date')It can not work because SQL will try to convert you "NUll Date" to be datetime and will fail. So you have to use different syntax:ISNULL(convert(varchar(20),DateField), 'Null date')and this should work but returned column will be varchar(20) not datetime.In case you need datetime column just left nulls inside or use first valid date :ISNULL(DateField, '00:00')it will replace all nulls by date '01/01/1900 00:00' Bu I think that keeping null is better.|||

ISNULL wont work with datetime a column if we r replacing with some characters. so in this case whatjpazgiermentioned is right.but in that too there is a flaw. what i got here is we need to check for each part of the datetime value for NULL.like dd/mm/yyyy, then hh:mm:ss,then am/pm.

so the query will be like

SELECT column1,Isnull(
(convert(varchar(20),columnDate,101) + ' ' + convert(varchar(20),columnDate,108) + ' ' + right(convert(varchar(20),columnDate),2)),'-') column2 from table1

im not sure if the above mentioned is the best solution possible.if anyone have any easy method other than this please reply.

Handeling Parent-Child hierarchy in RS2005

hello,

I have a problem in RS2005:

In my report I see all levels in one column. However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

Hello Hanna

After you select the fields in the query builder, you are prompted to select a matrix or a tabular approach. Then, when you select matrix, you are prompted to select the fields that go to the columns, the fields that go to the rows, and so on.

All you have to do is to check the little checkbox in the lower left corner that says "Enable Drilldown". This will put all your top level member of the hierarchy in the first column and will enable you to drill down.

Did it work?

Andr

|||

Hello again...

Can you tell me what you did to be able to see all levels in one column?

I have an hierarchy:

region -> area -> district -> country

Example:

Europe (region)
Southern Europe (Area)
Southern West Europe (district)
Portugal (country)

my objective is to have the following display:

Hierarchy Value
-
Europe 1000
Southern Europe 500
Southern West Europe 250
Portugal 150
Spain 100

How can I achieve this?

I don't want the hierarchy to be displayed in more than one column like this:

Region Area District Country Value
Europe S. Europe S. West Europe Portugal 150
Europe S. Europe S. West Europe Spain 100

And I don't want the hierarchy to be displayed with drilldown like this:

Hierarchy Value
- Europe 1000
- Southern Europe 500
-Southern West Europe 250
Portugal 150
Spain 100

I just want the hierarchy to be displayed in a single column.

Andr

|||Hello Hannah,

I recently ran into the same problem. Did you solve yours?
Hopefully you can help me ...
Sincerely,
Rhapsy
|||Hello,

I found the other post "Creating report based on parent-child dimension" which includes an answer to my problem ... :D
thanks
Rhapsy

Handeling Parent-Child hierarchy in RS2005

hello,

I have a problem in RS2005:

In my report I see all levels in one column. However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

Hello Hanna

After you select the fields in the query builder, you are prompted to select a matrix or a tabular approach. Then, when you select matrix, you are prompted to select the fields that go to the columns, the fields that go to the rows, and so on.

All you have to do is to check the little checkbox in the lower left corner that says "Enable Drilldown". This will put all your top level member of the hierarchy in the first column and will enable you to drill down.

Did it work?

Andr

|||

Hello again...

Can you tell me what you did to be able to see all levels in one column?

I have an hierarchy:

region -> area -> district -> country

Example:

Europe (region)
Southern Europe (Area)
Southern West Europe (district)
Portugal (country)

my objective is to have the following display:

Hierarchy Value
-
Europe 1000
Southern Europe 500
Southern West Europe 250
Portugal 150
Spain 100

How can I achieve this?

I don't want the hierarchy to be displayed in more than one column like this:

Region Area District Country Value
Europe S. Europe S. West Europe Portugal 150
Europe S. Europe S. West Europe Spain 100

And I don't want the hierarchy to be displayed with drilldown like this:

Hierarchy Value
- Europe 1000
- Southern Europe 500
-Southern West Europe 250
Portugal 150
Spain 100

I just want the hierarchy to be displayed in a single column.

Andr

|||Hello Hannah,

I recently ran into the same problem. Did you solve yours?
Hopefully you can help me ...
Sincerely,
Rhapsy
|||Hello,

I found the other post "Creating report based on parent-child dimension" which includes an answer to my problem ... :D
thanks
Rhapsy

Handeling Parent-Child hierarchy in RS2005

hello,

I have a problem in RS2005:

In my report I see all levels in one column. However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

Hello Hanna

After you select the fields in the query builder, you are prompted to select a matrix or a tabular approach. Then, when you select matrix, you are prompted to select the fields that go to the columns, the fields that go to the rows, and so on.

All you have to do is to check the little checkbox in the lower left corner that says "Enable Drilldown". This will put all your top level member of the hierarchy in the first column and will enable you to drill down.

Did it work?

Andr

|||

Hello again...

Can you tell me what you did to be able to see all levels in one column?

I have an hierarchy:

region -> area -> district -> country

Example:

Europe (region)
Southern Europe (Area)
Southern West Europe (district)
Portugal (country)

my objective is to have the following display:

Hierarchy Value
-
Europe 1000
Southern Europe 500
Southern West Europe 250
Portugal 150
Spain 100

How can I achieve this?

I don't want the hierarchy to be displayed in more than one column like this:

Region Area District Country Value
Europe S. Europe S. West Europe Portugal 150
Europe S. Europe S. West Europe Spain 100

And I don't want the hierarchy to be displayed with drilldown like this:

Hierarchy Value
- Europe 1000
- Southern Europe 500
-Southern West Europe 250
Portugal 150
Spain 100

I just want the hierarchy to be displayed in a single column.

Andr

|||Hello Hannah,

I recently ran into the same problem. Did you solve yours?
Hopefully you can help me ...
Sincerely,
Rhapsy
|||Hello,

I found the other post "Creating report based on parent-child dimension" which includes an answer to my problem ... :D
thanks
Rhapsy

H

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!