Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]..
.
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]
..
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfran
You probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing string and Inserting each element?

Hi All,
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:

> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:

>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>

Parse A column containing SYntax

Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.http://www.dbforums.com/showthread.php?t=1196943|||I haven't seen a script to do this, and it would be quite a challenge.

Parent ID in a Slowly Changing Dimension

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

Jeroen Alblas wrote:

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish

|||

Why would your Id column change if you have a type2 change?

Are you using a typ2 column to lookup your parentID?

|||

Okay, let me try to give an example:

Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999

Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999

If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...

Thnx again!
Jeroen

|||

I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.

Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning

|||Why don't you normalize that table?|||

Phil wrote:

Why don't you normalize that table?

Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...

Rafael wrote:

You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

Jeroen

|||

Jeroen Alblas wrote:

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:

“No changes there so Doopie's record remains unchanged.”

So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.

In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.

Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.

|||

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".

Thnx, Jeroen

|||

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999

After the change the data would look like this:

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999

|||

Jeroen Alblas wrote:

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.

Indeed, I would not consider it an attribute of the child row.

Jeroen Alblas wrote:

And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Jeroen Alblas wrote:

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

That is certanly not practical...

Jeroen Alblas wrote:

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

That was pretty much my original sugestion...good luck with that

|||

David Frommer wrote:

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)

Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.

Jeroen

|||

Rafael Salas wrote:

You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.

Jeroen

Parent ID in a Slowly Changing Dimension

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

Jeroen Alblas wrote:

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish

|||

Why would your Id column change if you have a type2 change?

Are you using a typ2 column to lookup your parentID?

|||

Okay, let me try to give an example:

Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999

Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999

If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...

Thnx again!
Jeroen

|||

I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.

Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning

|||Why don't you normalize that table?|||

Phil wrote:

Why don't you normalize that table?

Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...

Rafael wrote:

You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

Jeroen

|||

Jeroen Alblas wrote:

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:

“No changes there so Doopie's record remains unchanged.”

So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.

In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.

Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.

|||

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".

Thnx, Jeroen

|||

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999

After the change the data would look like this:

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999

|||

Jeroen Alblas wrote:

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.

Indeed, I would not consider it an attribute of the child row.

Jeroen Alblas wrote:

And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Jeroen Alblas wrote:

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

That is certanly not practical...

Jeroen Alblas wrote:

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

That was pretty much my original sugestion...good luck with that

|||

David Frommer wrote:

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)

Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.

Jeroen

|||

Rafael Salas wrote:

You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.

Jeroen

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".

There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.

My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".

There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.

My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:
> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
Naty
Hi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that unio
n
> a few tables that have a computed column (defined in the tables themselves
).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains
a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Parse Column and Sort

Hi can anyone give your idea on how this can be implemented.
I have a table which has a column whose values looks like
Page 10 Line 1
Document
1
3
Sheet: Landscape
Sheet: Part 2
Page: 29 Line: 2
Page91
Page 10
Sheet: Sheet1
i need to sort by Page number and line number if no line number then page
number alone or if no page number then sort by sheet number else normal sort
Can anyone throw some light on itBoss
Why not do that on client?
CREATE TABLE #Test
(
col1 VARCHAR(15)
)
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END DESC
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> Hi can anyone give your idea on how this can be implemented.
> I have a table which has a column whose values looks like
> Page 10 Line 1
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Part 2
> Page: 29 Line: 2
> Page91
> Page 10
> Sheet: Sheet1
> i need to sort by Page number and line number if no line number then page
> number alone or if no page number then sort by sheet number else normal
sort
>
> Can anyone throw some light on it
>|||Can't you clean up the data and create a table where these attributes
are properly represented in atomic columns? Storing meaningful data in
this form is an extremely poor and inconvenient design. That's why it's
usual to scrub and transform external data before it is loaded into
tables.
If a redesign is impossible for you then take a look at CHARINDEX and
PATINDEX. You can extract the different string components into separate
calculated columns and then sort on those.
If you need more help, please post DDL (CREATE TABLE statement
including keys and constraints), some sample data (as INSERT
statements) and show your required end result.
David Portas
SQL Server MVP
--|||Hi Thanks for your reply
It dosent seem to return correct records
CREATE TABLE #Test
(
col1 VARCHAR(25)
)
-- drop table #test
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page: 29 Line: 1')
INSERT INTO #Test VALUES ('Page: 29 Line: 10')
INSERT INTO #Test VALUES ('Page: 2 Line: 18')
INSERT INTO #Test VALUES ('Page: 71 Line: 1')
INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page1')
INSERT INTO #Test VALUES ('Page 20')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Page 8')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
INSERT INTO #Test VALUES ('Sheet: Sheet21')
INSERT INTO #Test VALUES ('Sheet: Sheet10')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
The result is
Document
1
3
Sheet: Landscape
Sheet: Sheet1
Sheet: Sheet21
Sheet: Sheet10
Page: 201 Line: 1000 - this is in wrong order
Page1
Page 8
Page91
Page 10
Page 20
Page 10 Line 1
Page 10 Line 1
Page 10 Line 1
Page: 2 Line: 18 - this is in wrong order
Page: 29 Line: 1
Page: 29 Line: 2
Page: 71 Line: 1 - this is in wrong order
Page: 29 Line: 10
"Uri Dimant" wrote:

> Boss
> Why not do that on client?
> CREATE TABLE #Test
> (
> col1 VARCHAR(15)
> )
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END DESC
>
>
>
> "Boss" <Boss@.discussions.microsoft.com> wrote in message
> news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> sort
>
>|||SELECT col1 FROM
(
SELECT replace(col1,' ','')as col1 FROM #Test
) AS Der
ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
If it does not help ,post expected result, as I and David stated you will be
better off to do that on the client side, but befor that clean up the table
from redundant data
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:B175FA7A-14E9-4A57-A018-F7B48C805937@.microsoft.com...
> Hi Thanks for your reply
> It dosent seem to return correct records
> CREATE TABLE #Test
> (
> col1 VARCHAR(25)
> )
> -- drop table #test
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page: 29 Line: 1')
> INSERT INTO #Test VALUES ('Page: 29 Line: 10')
> INSERT INTO #Test VALUES ('Page: 2 Line: 18')
> INSERT INTO #Test VALUES ('Page: 71 Line: 1')
> INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page1')
> INSERT INTO #Test VALUES ('Page 20')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Page 8')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> INSERT INTO #Test VALUES ('Sheet: Sheet21')
> INSERT INTO #Test VALUES ('Sheet: Sheet10')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END ASC
> The result is
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Sheet1
> Sheet: Sheet21
> Sheet: Sheet10
> Page: 201 Line: 1000 - this is in wrong order
> Page1
> Page 8
> Page91
> Page 10
> Page 20
> Page 10 Line 1
> Page 10 Line 1
> Page 10 Line 1
> Page: 2 Line: 18 - this is in wrong order
> Page: 29 Line: 1
> Page: 29 Line: 2
> Page: 71 Line: 1 - this is in wrong order
> Page: 29 Line: 10
>
> "Uri Dimant" wrote:
>
page
normal

Parse a numeric string from a field

Hello All,

I'm trying to parse for a numeric string from a column in a table. What
I'm looking for is a numeric string of a fixed length of 8.
The column is a comments field and can contain the numeric string in
any position
Here's an example of the values in the column

1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-98
2) wed.kx10/26 Netrez 95860536

Now I need to parse through these lines and return only the 8 digit
numbers in it
The result set should be

27068935
95860536

This is what I've done so far

Declare @.tmp table
(
Comments_Txt varchar(255)
)

Insert into @.tmp

select Comments_Txt from Reservation

select * FROM @.tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')

But it returns the entire comments field in the result set. What I need

is a way to return just those 8 digits.

Any Ideas??

Thanks in advance!!!You could use the following:

select substring(comments_txt,
patindex('%[0-9][0-9][0-9][0-9][0-9][0***9]%', comments_txt), 8)
from @.tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0***9]%')

--
David Rowland
dbmonitor.tripod.com|||Thank You very much !!! That was just what I needed.

parameters really only a filter?

Am I missing a trick here? When i set up my parameter in reporting
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >