Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Thursday, March 29, 2012
Foreign key and indexes
I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql
Foreign key
Hello,
I need to add a column to a table. The new column is a foreign key which references another table's column.
how can you do this? Does it need to be done in two steps, like:
alter table table1
add new_col_name datatype
then
alter table table1
add table constraint.
If so, what is the syntax for adding a foreign key contraint which references another table.
thank youHello,
Yes, it has to be done in two steps, just as you said.
To add a foreign key constraint :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field1)
REFERENCES table2(field2);
Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12)
REFERENCES table2(field21, field22);
Regards,
RBARAER|||thanks! what does the fk_table1_table2 mean? Is it just a lable?|||It can be done in one step, at least it can on Oracle:
alter table table1
add (new_col_name references table2(keycol));
or (to give the constraint a specific name):
alter table table1
add (new_col_name constraint table1_table2_fk references table2(keycol));|||cool, I'll try that also, but the first code worked.
I need to add a column to a table. The new column is a foreign key which references another table's column.
how can you do this? Does it need to be done in two steps, like:
alter table table1
add new_col_name datatype
then
alter table table1
add table constraint.
If so, what is the syntax for adding a foreign key contraint which references another table.
thank youHello,
Yes, it has to be done in two steps, just as you said.
To add a foreign key constraint :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field1)
REFERENCES table2(field2);
Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12)
REFERENCES table2(field21, field22);
Regards,
RBARAER|||thanks! what does the fk_table1_table2 mean? Is it just a lable?|||It can be done in one step, at least it can on Oracle:
alter table table1
add (new_col_name references table2(keycol));
or (to give the constraint a specific name):
alter table table1
add (new_col_name constraint table1_table2_fk references table2(keycol));|||cool, I'll try that also, but the first code worked.
foreign key
Hi
is it a good practice to create FK on each & every column in a table whose values are taken from master table.
should I create or not.
If I create will that be heavy i.e what about memory consumption ?
pls reply
Thanks
Shubhangi1. You can foreign keys.
or
2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field
ro
3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0
or
if exists(select 1 from tablename where ...)
Tuesday, March 27, 2012
Foreach Loop read table data and write to file
Hi,
I want to do the following with a ssis package:
INPUT:
A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS:
I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Thanks
Danny
(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform
|||The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.
What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.
|||You can do in following way :
1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table
2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :
"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"
3. Add an Export Column transformation; in Export Column transformation editor set
Extract Column= ColumnB
File Path Column=NewFilePath
so SSIS will get the file from columnB and put in the folder using NewFilePath
Monday, March 26, 2012
ForEach DataFlow Task
I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.
How can I do this?
Danny Crowell wrote:
I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.
How can I do this?
I don't understand...could you clarify it?
What do you mean with 'loop through a view'? or you mean the rows in a view?...Then how is that you want t pass a column as parameter?
|||
I want to be able to loop through a view and execute a dataflow task for each record. I would like to pass the value of a column to the dataflow task to be used as a parameter in a data reader.
How can I do this?
Load an Execute SQL task with the SQL for the view into an OBJECT-typed variable. Then, using a foreach loop against that ADO recordset, you can grab a column and stick its contents into another variable that you can use in the foreach loop-contained data flow.|||Here is an artilce from Brian Knight that helped me with this.
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
Friday, March 23, 2012
forcing data format mask without modifting code
I have statement which is comparing a smalldatetime column to literal string as follows:
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.
What's wrong with changing the statement?|||Where is the data coming from? Is it always in that format? Can you use SUBSTRING?|||Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.|||Couldn't you do something like...
cast(day(sales_date()) as varchar(2)) + '-' +
cast(month(sales_date()) as varchar(2)) + '-' +
cast(year(sales_date()) as char(4)) + ' 0:0:0.000'
??
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.
What's wrong with changing the statement?|||Where is the data coming from? Is it always in that format? Can you use SUBSTRING?|||Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.|||Couldn't you do something like...
cast(day(sales_date()) as varchar(2)) + '-' +
cast(month(sales_date()) as varchar(2)) + '-' +
cast(year(sales_date()) as char(4)) + ' 0:0:0.000'
??
forcing column to appear
I have a matrix that shows figures by year, broken down by months (1-12).
When the query runs and that particular month has not values, the column is
not displayed. I previously came across something regarding the use of a
function to force the columns to appear but can't seem to find it again.
Anyone have a suggestion for doing this? It would be similar to how the PIVOT
in access works.On Jun 6, 10:08 am, brian <b...@.discussions.microsoft.com> wrote:
> I have a matrix that shows figures by year, broken down by months (1-12).
> When the query runs and that particular month has not values, the column is
> not displayed. I previously came across something regarding the use of a
> function to force the columns to appear but can't seem to find it again.
> Anyone have a suggestion for doing this? It would be similar to how the PIVOT
> in access works.
I traditionally look for the columns (value in the pivot column) that
I am expecting in the dataset and if they do not appear union an empty
record with the column name to the returned dataset (as part of the
stored procedure/query that is sourcing the report). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
When the query runs and that particular month has not values, the column is
not displayed. I previously came across something regarding the use of a
function to force the columns to appear but can't seem to find it again.
Anyone have a suggestion for doing this? It would be similar to how the PIVOT
in access works.On Jun 6, 10:08 am, brian <b...@.discussions.microsoft.com> wrote:
> I have a matrix that shows figures by year, broken down by months (1-12).
> When the query runs and that particular month has not values, the column is
> not displayed. I previously came across something regarding the use of a
> function to force the columns to appear but can't seem to find it again.
> Anyone have a suggestion for doing this? It would be similar to how the PIVOT
> in access works.
I traditionally look for the columns (value in the pivot column) that
I am expecting in the dataset and if they do not appear union an empty
record with the column name to the returned dataset (as part of the
stored procedure/query that is sourcing the report). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Forcing Column Headings
Hi...
I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).
The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.
The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.
I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.
Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??
Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.
Was not aware of that box, and finally tracked it down.
Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!
Thanks again|||Update....
The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.
So I went back and tried it again... and it now works!
No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...
But it now works, as I said.
Many, many thanks for your help.sql
I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).
The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.
The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.
I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.
Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??
Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.
Was not aware of that box, and finally tracked it down.
Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!
Thanks again|||Update....
The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.
So I went back and tried it again... and it now works!
No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...
But it now works, as I said.
Many, many thanks for your help.sql
Wednesday, March 21, 2012
Force Uniqueness on one column
HI:
If when joining parent and child tables, a query returns multiple entries
for a given parent, how can I limit query to showing only first child? Kind
of like grouping on one field in result set.
Thanks,
CharlieDefine "first".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
> HI:
> If when joining parent and child tables, a query returns multiple entries
> for a given parent, how can I limit query to showing only first child?
> Kind
> of like grouping on one field in result set.
> Thanks,
> Charlie
>|||Hi Tom, let me restate..
If query joins a parent table with a child table in a one-to-many relation
the results set will show the parent id repeating for each child. I want
the query to show only one child despite having many. How do I filter join
to limit result set to only one child per parent even though it a parent may
have many child records.
Thanks,
charlie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Define "first".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
entries
>|||SELECT
Parent.ID
, Child.ID
, Child.Data
FROM
Parent
INNER JOIN
(
SELECT
Child.Parent_ID
, Child.ID
, Child.Data
FROM
Child
INNER JOIN
(
SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
Parent_ID
) LowestChildForParent
ON
Child.Parent_ID = LowestChildForParent.Parent_ID
AND
Child.ID = LowestChildForParent.ID
) Child
ON
Parent.ID = Child.Parent_ID
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
join
> to limit result set to only one child per parent even though it a parent
may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Again, define "first". You haven't posted your DDL. We have no idea which
of the child rows is the "first" for a given parent ID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
> join
> to limit result set to only one child per parent even though it a parent
> may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Using min() or max() value for a set of keys in grouping should work. This
will first or last child.
Thanks
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437a10a3$0$133$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT
> Parent.ID
> , Child.ID
> , Child.Data
> FROM
> Parent
> INNER JOIN
> (
> SELECT
> Child.Parent_ID
> , Child.ID
> , Child.Data
> FROM
> Child
> INNER JOIN
> (
> SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
> Parent_ID
> ) LowestChildForParent
> ON
> Child.Parent_ID = LowestChildForParent.Parent_ID
> AND
> Child.ID = LowestChildForParent.ID
> ) Child
> ON
> Parent.ID = Child.Parent_ID
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
relation
want
> join
> may
child?
>
If when joining parent and child tables, a query returns multiple entries
for a given parent, how can I limit query to showing only first child? Kind
of like grouping on one field in result set.
Thanks,
CharlieDefine "first".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
> HI:
> If when joining parent and child tables, a query returns multiple entries
> for a given parent, how can I limit query to showing only first child?
> Kind
> of like grouping on one field in result set.
> Thanks,
> Charlie
>|||Hi Tom, let me restate..
If query joins a parent table with a child table in a one-to-many relation
the results set will show the parent id repeating for each child. I want
the query to show only one child despite having many. How do I filter join
to limit result set to only one child per parent even though it a parent may
have many child records.
Thanks,
charlie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Define "first".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
entries
>|||SELECT
Parent.ID
, Child.ID
, Child.Data
FROM
Parent
INNER JOIN
(
SELECT
Child.Parent_ID
, Child.ID
, Child.Data
FROM
Child
INNER JOIN
(
SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
Parent_ID
) LowestChildForParent
ON
Child.Parent_ID = LowestChildForParent.Parent_ID
AND
Child.ID = LowestChildForParent.ID
) Child
ON
Parent.ID = Child.Parent_ID
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
join
> to limit result set to only one child per parent even though it a parent
may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Again, define "first". You haven't posted your DDL. We have no idea which
of the child rows is the "first" for a given parent ID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
> join
> to limit result set to only one child per parent even though it a parent
> may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Using min() or max() value for a set of keys in grouping should work. This
will first or last child.
Thanks
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437a10a3$0$133$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT
> Parent.ID
> , Child.ID
> , Child.Data
> FROM
> Parent
> INNER JOIN
> (
> SELECT
> Child.Parent_ID
> , Child.ID
> , Child.Data
> FROM
> Child
> INNER JOIN
> (
> SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
> Parent_ID
> ) LowestChildForParent
> ON
> Child.Parent_ID = LowestChildForParent.Parent_ID
> AND
> Child.ID = LowestChildForParent.ID
> ) Child
> ON
> Parent.ID = Child.Parent_ID
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
relation
want
> join
> may
child?
>
Monday, March 19, 2012
Force IS to use column headings
Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.
Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?
Thanks guys...
The connection manager defines the ordering in the text file so if IS is putting your data into the wrong columns in the database it is because the connection manager is defined incorrectly. If your files vary their order of columns then you would need different connection managers (and therefore different sources) for each ordering.
Matt
|||Thanks for the reply but the problem I'm facing is the text file may not have some of the columns or the columns will be in different order etc. I don't know what the file contains at the time of loading.Is it possible for me to get IS to load what ever columns are in the text file and just put null (in the database) for the once we are missing?
|||
If you really have no idea what is coming in until it's loaded, then my suggestion would be to load the text file, including the first row with the names, into a SQL table with columns called "col1", "col2", "col3" etc. up to the max you will have. That gets you over the problem of loading the table using a single data flow task.
Then the problem is one of how to split the data into the relevant columns in your "proper" destination table. ;-D
I'm still gettng my head round the new tools in SSIS, so personally I wouldn't know how to do it (maybe conditional split?). What I would do would be to write a T-SQL stored procedure to parse the first row and construct an SQL string to select the columns from the table.
The example below gives you the idea.
Hope this helps,
Rich
Code to follow --
CREATE TABLE [dbo].[tbl_RAW](
[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_People](
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Phone] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Sex] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_ValueList](
[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
TRUNCATE TABLE tbl_RAW;
TRUNCATE TABLE tbl_People;
TRUNCATE TABLE tbl_ValueList;
-- Populate RAW
INSERT INTO dbo.tbl_RAW
Values ('sex','Name','Phone');
INSERT INTO dbo.tbl_RAW
Values ('Male','Eric','1234');
INSERT INTO dbo.tbl_RAW
Values ('Male','Tim','00000');
INSERT INTO dbo.tbl_RAW
Values ('Female','Simone','9876');
INSERT INTO tbl_ValueList
SELECT top 1 *
FROM dbo.tbl_RAW
DECLARE @.ValueList varchar(50)
DECLARE @.strSQL varchar(100)
DECLARE @.col1value varchar(50)
SELECT @.ValueList = '(' + col1 +','+ col2 +','+ col3 +')' FROM tbl_ValueList
SELECT @.Col1Value = col1 FROM tbl_ValueList
SET @.strSQL = 'INSERT INTO dbo.tbl_People ' + @.ValueList + 'SELECT * FROM dbo.tbl_RAW WHERE col1 <> '''+ @.col1Value +''''
print @.strSQL
EXECUTE (@.strSQL)
SELECT * FROM dbo.tbl_People
-- End of Code--
|||I'm having a similar problem.
I'm using CSVDE.exe to do a bulk export of Active Directory users. The problem is that the column order that CSVDE outputs seems to be non-deterministic.
If I dump the file, go into the connection and do a "Reset Columns", everything works fine. However, I would like to do the dump as part of my Control Flow, and I can't find a way to force a "Reset Columns" before the processing begins.
I know what columns I'm getting, just not the order they'll come in. I also know that the first row will have the column names in it.
Force IS to use column headings
Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.
Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?
Thanks guys...
The connection manager defines the ordering in the text file so if IS is putting your data into the wrong columns in the database it is because the connection manager is defined incorrectly. If your files vary their order of columns then you would need different connection managers (and therefore different sources) for each ordering.
Matt
|||Thanks for the reply but the problem I'm facing is the text file may not have some of the columns or the columns will be in different order etc. I don't know what the file contains at the time of loading.Is it possible for me to get IS to load what ever columns are in the text file and just put null (in the database) for the once we are missing?
|||
If you really have no idea what is coming in until it's loaded, then my suggestion would be to load the text file, including the first row with the names, into a SQL table with columns called "col1", "col2", "col3" etc. up to the max you will have. That gets you over the problem of loading the table using a single data flow task.
Then the problem is one of how to split the data into the relevant columns in your "proper" destination table. ;-D
I'm still gettng my head round the new tools in SSIS, so personally I wouldn't know how to do it (maybe conditional split?). What I would do would be to write a T-SQL stored procedure to parse the first row and construct an SQL string to select the columns from the table.
The example below gives you the idea.
Hope this helps,
Rich
Code to follow --
CREATE TABLE [dbo].[tbl_RAW](
[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_People](
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Phone] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Sex] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_ValueList](
[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
TRUNCATE TABLE tbl_RAW;
TRUNCATE TABLE tbl_People;
TRUNCATE TABLE tbl_ValueList;
-- Populate RAW
INSERT INTO dbo.tbl_RAW
Values ('sex','Name','Phone');
INSERT INTO dbo.tbl_RAW
Values ('Male','Eric','1234');
INSERT INTO dbo.tbl_RAW
Values ('Male','Tim','00000');
INSERT INTO dbo.tbl_RAW
Values ('Female','Simone','9876');
INSERT INTO tbl_ValueList
SELECT top 1 *
FROM dbo.tbl_RAW
DECLARE @.ValueList varchar(50)
DECLARE @.strSQL varchar(100)
DECLARE @.col1value varchar(50)
SELECT @.ValueList = '(' + col1 +','+ col2 +','+ col3 +')' FROM tbl_ValueList
SELECT @.Col1Value = col1 FROM tbl_ValueList
SET @.strSQL = 'INSERT INTO dbo.tbl_People ' + @.ValueList + 'SELECT * FROM dbo.tbl_RAW WHERE col1 <> '''+ @.col1Value +''''
print @.strSQL
EXECUTE (@.strSQL)
SELECT * FROM dbo.tbl_People
-- End of Code--
|||I'm having a similar problem.
I'm using CSVDE.exe to do a bulk export of Active Directory users. The problem is that the column order that CSVDE outputs seems to be non-deterministic.
If I dump the file, go into the connection and do a "Reset Columns", everything works fine. However, I would like to do the dump as part of my Control Flow, and I can't find a way to force a "Reset Columns" before the processing begins.
I know what columns I'm getting, just not the order they'll come in. I also know that the first row will have the column names in it.
Force Excel Column type when exporting from SSRS
Hi all,
I have a tricky behavior here. I have a column in my report which contains alphanumeric codes. When I have a code like 17E001 and I export the report to Excel, excel kindly shows that alphanumeric code to 1+E7 and the value of the column is changed to 1700 which is defintly not what I want.
So I was wondering if there is any way to force the column types when exporting from SSRS?
Sbastien.
By the way if there is a way to force all columns to be formated as Text that will do for me as the excel reports are only used to process data using SSIS.
Force all months along X axis when some months missing data
Hi,
I want to display numeric information by month in a column chart. The
data does not have records for every month. I can't seem to display
all months along the x axis while still using month names instead of
month numbers.
My current chart setup is as follows:
Data > Category group
Name: MonthCategory
Group on (Expression): =Month(Fields!AcceptedDate.Value)
Label: =Fields!AcceptedDate.Value
X Axis
Show labels: <true>
Format Code: MMM
Scale Minimum: 1
Scale Maximum: 12
With this configuration, I get x axis labels for the months that have
data, but the months with no data are skipped.
If I turn on "Numeric or time-scale values", I get all 12 months, but
they are displayed as the literal string "MMM". It seems that the
"Numeric or time-scale values" setting won't allow the labels to
display anything other than numeric values.
Any suggestions?
Thanks in advance,
EricHey Eric,
Can your Format: be an expression? If so you may be able to try
=Choose(xValue, "january", "february","march",etc...) for the 12 months.
Michael C
"Eric" wrote:
> Hi,
> I want to display numeric information by month in a column chart. The
> data does not have records for every month. I can't seem to display
> all months along the x axis while still using month names instead of
> month numbers.
> My current chart setup is as follows:
> Data > Category group
> Name: MonthCategory
> Group on (Expression): =Month(Fields!AcceptedDate.Value)
> Label: =Fields!AcceptedDate.Value
> X Axis
> Show labels: <true>
> Format Code: MMM
> Scale Minimum: 1
> Scale Maximum: 12
> With this configuration, I get x axis labels for the months that have
> data, but the months with no data are skipped.
> If I turn on "Numeric or time-scale values", I get all 12 months, but
> they are displayed as the literal string "MMM". It seems that the
> "Numeric or time-scale values" setting won't allow the labels to
> display anything other than numeric values.
> Any suggestions?
> Thanks in advance,
> Eric
>|||That's close Michael. Though when I use:
=choose(month(Fields!AcceptedDate.Value), "Jan", "Feb", "Mar",
"Apr"...)
I get "Jan" listed for every month.
On Jul 25, 5:28 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Hey Eric,
> Can your Format: be an expression? If so you may be able to try
> =Choose(xValue, "january", "february","march",etc...) for the 12 months.
> Michael C
>
> "Eric" wrote:
> > Hi,
> > I want to display numeric information by month in a column chart. The
> > data does not have records for every month. I can't seem to display
> > all months along the x axis while still using month names instead of
> > month numbers.
> > My current chart setup is as follows:
> > Data > Category group
> > Name: MonthCategory
> > Group on (Expression): =Month(Fields!AcceptedDate.Value)
> > Label: =Fields!AcceptedDate.Value
> > X Axis
> > Show labels: <true>
> > Format Code: MMM
> > Scale Minimum: 1
> > Scale Maximum: 12
> > With this configuration, I get x axis labels for the months that have
> > data, but the months with no data are skipped.
> > If I turn on "Numeric or time-scale values", I get all 12 months, but
> > they are displayed as the literal string "MMM". It seems that the
> > "Numeric or time-scale values" setting won't allow the labels to
> > display anything other than numeric values.
> > Any suggestions?
> > Thanks in advance,
> > Eric- Hide quoted text -
> - Show quoted text -
I want to display numeric information by month in a column chart. The
data does not have records for every month. I can't seem to display
all months along the x axis while still using month names instead of
month numbers.
My current chart setup is as follows:
Data > Category group
Name: MonthCategory
Group on (Expression): =Month(Fields!AcceptedDate.Value)
Label: =Fields!AcceptedDate.Value
X Axis
Show labels: <true>
Format Code: MMM
Scale Minimum: 1
Scale Maximum: 12
With this configuration, I get x axis labels for the months that have
data, but the months with no data are skipped.
If I turn on "Numeric or time-scale values", I get all 12 months, but
they are displayed as the literal string "MMM". It seems that the
"Numeric or time-scale values" setting won't allow the labels to
display anything other than numeric values.
Any suggestions?
Thanks in advance,
EricHey Eric,
Can your Format: be an expression? If so you may be able to try
=Choose(xValue, "january", "february","march",etc...) for the 12 months.
Michael C
"Eric" wrote:
> Hi,
> I want to display numeric information by month in a column chart. The
> data does not have records for every month. I can't seem to display
> all months along the x axis while still using month names instead of
> month numbers.
> My current chart setup is as follows:
> Data > Category group
> Name: MonthCategory
> Group on (Expression): =Month(Fields!AcceptedDate.Value)
> Label: =Fields!AcceptedDate.Value
> X Axis
> Show labels: <true>
> Format Code: MMM
> Scale Minimum: 1
> Scale Maximum: 12
> With this configuration, I get x axis labels for the months that have
> data, but the months with no data are skipped.
> If I turn on "Numeric or time-scale values", I get all 12 months, but
> they are displayed as the literal string "MMM". It seems that the
> "Numeric or time-scale values" setting won't allow the labels to
> display anything other than numeric values.
> Any suggestions?
> Thanks in advance,
> Eric
>|||That's close Michael. Though when I use:
=choose(month(Fields!AcceptedDate.Value), "Jan", "Feb", "Mar",
"Apr"...)
I get "Jan" listed for every month.
On Jul 25, 5:28 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Hey Eric,
> Can your Format: be an expression? If so you may be able to try
> =Choose(xValue, "january", "february","march",etc...) for the 12 months.
> Michael C
>
> "Eric" wrote:
> > Hi,
> > I want to display numeric information by month in a column chart. The
> > data does not have records for every month. I can't seem to display
> > all months along the x axis while still using month names instead of
> > month numbers.
> > My current chart setup is as follows:
> > Data > Category group
> > Name: MonthCategory
> > Group on (Expression): =Month(Fields!AcceptedDate.Value)
> > Label: =Fields!AcceptedDate.Value
> > X Axis
> > Show labels: <true>
> > Format Code: MMM
> > Scale Minimum: 1
> > Scale Maximum: 12
> > With this configuration, I get x axis labels for the months that have
> > data, but the months with no data are skipped.
> > If I turn on "Numeric or time-scale values", I get all 12 months, but
> > they are displayed as the literal string "MMM". It seems that the
> > "Numeric or time-scale values" setting won't allow the labels to
> > display anything other than numeric values.
> > Any suggestions?
> > Thanks in advance,
> > Eric- Hide quoted text -
> - Show quoted text -
Monday, March 12, 2012
for xml path reverse?
just started playing with xml datatype, and I'm running into a performance issue...
Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...
I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...
select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x
Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...
Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?
thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?
There are a couple of things you can try doing.
Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]
Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||
Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?
Also, as a performance hint: You may want to use
where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')
which can give you better performance than doing the cast into SQL and then the comparison.
Best regards
Michael
rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...
changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...
adding a for path index made very little difference ( < 5%)
CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH
Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?
we have generic data audit triggers that look like this:
insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.
the select described above is used to get the audit trail of changes to a row.
we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...
thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?
If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).
Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||
Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.
The optimization can apply to your case:
1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.
Thank you,
Shankar
Program Manager
Microsoft SQL Server
Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.
Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!
for xml path reverse?
just started playing with xml datatype, and I'm running into a performance issue...
Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...
I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...
select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x
Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...
Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?
thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?
There are a couple of things you can try doing.
Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]
Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||
Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?
Also, as a performance hint: You may want to use
where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')
which can give you better performance than doing the cast into SQL and then the comparison.
Best regards
Michael
rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...
changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...
adding a for path index made very little difference ( < 5%)
CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH
Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?
we have generic data audit triggers that look like this:
insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.
the select described above is used to get the audit trail of changes to a row.
we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...
thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?
If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).
Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||
Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.
The optimization can apply to your case:
1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.
Thank you,
Shankar
Program Manager
Microsoft SQL Server
Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.
Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!
Friday, March 9, 2012
FOR XML Output
How do I get an output from the query below (1) one
record at a time, (2) insert the output one row at a time
into a XMLType column of a table? In addition, how do I
assign the output to a variable before step (2) so that I
can manipulate the output?
select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
***Please disregard the new Path feature in Yukon
Thanks,
C TO
I presume you use SQL Server 2005 Express or Beta 2.
If you need to insert each customer info in XML format into a separate row
of another table you can do (I use FOR XML ..., TYPE for FOR XML to generate
XML type directly):
insert into your_table_with_xml_col
select
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
for xml path('Customer'), TYPE
)
FROM Customers
If you want all customers into an XML variable you can write:
declare @.x xml
set @.x=
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer'), TYPE
)
Does it answer your question?
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:867101c47843$77008040$a601280a@.phx.gbl...
> How do I get an output from the query below (1) one
> record at a time, (2) insert the output one row at a time
> into a XMLType column of a table? In addition, how do I
> assign the output to a variable before step (2) so that I
> can manipulate the output?
>
<skip/>
> Thanks,
> C TO
|||Dear Eugene,
Beautiful!!!!!
Thanks, thank, thanks!!!
TO
>--Original Message--
>I presume you use SQL Server 2005 Express or Beta 2.
>If you need to insert each customer info in XML format
into a separate row
>of another table you can do (I use FOR XML ..., TYPE for
FOR XML to generate
>XML type directly):
>insert into your_table_with_xml_col
>select
> (select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> for xml path('Customer'), TYPE
> )
>FROM Customers
>If you want all customers into an XML variable you can
write:
>declare @.x xml
>set @.x=
>(select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> FROM Customers
> FOR XML PATH('Customer'), TYPE
>)
>Does it answer your question?
>Regards,
>Eugene
>--
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>"C TO" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:867101c47843$77008040$a601280a@.phx.gbl...
time[vbcol=seagreen]
I
><skip/>
>
>.
>
record at a time, (2) insert the output one row at a time
into a XMLType column of a table? In addition, how do I
assign the output to a variable before step (2) so that I
can manipulate the output?
select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
***Please disregard the new Path feature in Yukon
Thanks,
C TO
I presume you use SQL Server 2005 Express or Beta 2.
If you need to insert each customer info in XML format into a separate row
of another table you can do (I use FOR XML ..., TYPE for FOR XML to generate
XML type directly):
insert into your_table_with_xml_col
select
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
for xml path('Customer'), TYPE
)
FROM Customers
If you want all customers into an XML variable you can write:
declare @.x xml
set @.x=
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer'), TYPE
)
Does it answer your question?
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:867101c47843$77008040$a601280a@.phx.gbl...
> How do I get an output from the query below (1) one
> record at a time, (2) insert the output one row at a time
> into a XMLType column of a table? In addition, how do I
> assign the output to a variable before step (2) so that I
> can manipulate the output?
>
<skip/>
> Thanks,
> C TO
|||Dear Eugene,
Beautiful!!!!!
Thanks, thank, thanks!!!
TO
>--Original Message--
>I presume you use SQL Server 2005 Express or Beta 2.
>If you need to insert each customer info in XML format
into a separate row
>of another table you can do (I use FOR XML ..., TYPE for
FOR XML to generate
>XML type directly):
>insert into your_table_with_xml_col
>select
> (select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> for xml path('Customer'), TYPE
> )
>FROM Customers
>If you want all customers into an XML variable you can
write:
>declare @.x xml
>set @.x=
>(select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> FROM Customers
> FOR XML PATH('Customer'), TYPE
>)
>Does it answer your question?
>Regards,
>Eugene
>--
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>"C TO" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:867101c47843$77008040$a601280a@.phx.gbl...
time[vbcol=seagreen]
I
><skip/>
>
>.
>
Wednesday, March 7, 2012
FOR XML AUTO, ELEMENTS Problem
I have a column ('ProblemResolution') in a table ('Incident') that holds plain text. I am doing a query against that column and converting the results to XML as follows:
Select ProblemResolution From Incident Where RowID = 2 FOR XML AUTO, ELEMENTS
The problem is the XML that is being generated. It appears the XML that is generated is illegal (in some cases) because if I save the resulting XML in a text file and load into Internet Explorer, IE generates errors.
Here is the plain text (actually part of it - enough to demo the problem) as stored in the column. The quotes are not stored.
"8/11/2006 dabonder -
Carol –
Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .|
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.
I haven't had time to clarify this. If you want to discuss when you get time I would be happy to.
-D Abonder
D Abonder
Director of Consulting
Some Company
www.SomeCompany.com
Email: dgonder@.somecompany.com
Phone: 123-555-3450"
Here is the generated (illegal) XML:
<Incident><ProblemResolution>8/11/2006 dabonder -

Carol –

Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .

If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.

I haven't had time to clarify this. If you want to discuss when you get time I would be happy to.

-D Abonder

D Abonder
Director of Consulting
Some Company
www.SomeCompany.com

Email: dgonder@.somecompany.com
Phone: 123-555-3450</ProblemResolution></Incident>
The problem is that the document contains invalid U+0000 characters that are not allowed in XML. FOR XML does not mark them as errors but outputs them anyway. You should clean your data in the table or when running your FOR XML expression or before passing the XML to the parser and remove the U+0000 code point or replace the 
 with the zero-length string.
Best regards
Michael
Sunday, February 26, 2012
FOR XML / stored procedures
Does anyone know how to generate a resultset using FOR XML
and save that result (XML document) into a column without
leaving SQL Server to render the document?I dont think this is possible as the FOR XML clause sends a stream data out
and it is not possible to save it into a varible at the SQL Server in the
current version atleast ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"T. Wade" <tnolte@.foundrysoftware.com> wrote in message
news:04ac01c366a0$fe916d90$a401280a@.phx.gbl...
> Does anyone know how to generate a resultset using FOR XML
> and save that result (XML document) into a column without
> leaving SQL Server to render the document?|||Hello Wade,
Thanks for posting to MSDN Managed Newsgroup. I will look into this issue
and
let you know as soon as I have update for you.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>|||Hello Wade,
There isn?t a way to do this without going out to a client and back in.
The FOR XML formatting of the recordset is done as the last step when the
TDS output stream is created so the output has to leave the server.
One workaround could be to use link servers by linking a server back to
itself. Ken Henderson's book contains related information
http://btobsearch.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=2VOBU
N18XR&btob=Y&isbn=0201700468&itm=1
[ Disclaimer: This is a third party info and Microsoft does not guarantee
the accuracy of it]
In above case, the data is still being streamed out of the server through
the OLEDB provider so you might wanna watch the performance.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>
and save that result (XML document) into a column without
leaving SQL Server to render the document?I dont think this is possible as the FOR XML clause sends a stream data out
and it is not possible to save it into a varible at the SQL Server in the
current version atleast ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"T. Wade" <tnolte@.foundrysoftware.com> wrote in message
news:04ac01c366a0$fe916d90$a401280a@.phx.gbl...
> Does anyone know how to generate a resultset using FOR XML
> and save that result (XML document) into a column without
> leaving SQL Server to render the document?|||Hello Wade,
Thanks for posting to MSDN Managed Newsgroup. I will look into this issue
and
let you know as soon as I have update for you.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>|||Hello Wade,
There isn?t a way to do this without going out to a client and back in.
The FOR XML formatting of the recordset is done as the last step when the
TDS output stream is created so the output has to leave the server.
One workaround could be to use link servers by linking a server back to
itself. Ken Henderson's book contains related information
http://btobsearch.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=2VOBU
N18XR&btob=Y&isbn=0201700468&itm=1
[ Disclaimer: This is a third party info and Microsoft does not guarantee
the accuracy of it]
In above case, the data is still being streamed out of the server through
the OLEDB provider so you might wanna watch the performance.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>
Friday, February 24, 2012
For Loop help
I have a problem not receiving any data back from a for loop in my report. The column data shows only as False or True.
Here is my code below. The one in VB and the one i have in Crystal Reports
Crystal Reports Code:
Dim ServicePeriod As number
ServicePeriod = {command.Advisor_Service_Period}
Dim amount As number
Dim i as number
For i=1 To 28
If ServicePeriod > 53 Then
formula = amount =+ 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
formula = amount =+ 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
formula = amount =+ 1000
ElseIf ServicePeriod = 0 Then
formula = amount =+ 800
End If
ServicePeriod =+ 1
Next i
VB Code:
Dim ServicePeriod As Integer = 1
Dim amount As Integer
For i As Integer = 1 To 28
If ServicePeriod > 53 Then
amount += 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount += 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount += 1000
ElseIf ServicePeriod = 0 Then
amount += 800
End If
ServicePeriod += 1Next iI don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.|||I don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.
Thanks for the quick response. removing the = helped eliminate the bool problem but for some reason i am still getting bad data. Should i maybe take a different approach on how to retreive this data? It returns 1000 for every record and does not seem to loop.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.
It does not seem to loop and add values. It instead just checks once and adds a value rather than looping for a set amount of times. How would you write the loop with crystal syntax. I have been stuck with this for a while any help is greatly appreciated.|||This is still basic syntax:
whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number
amount = 0
ServicePeriod = 54
For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i
formula = amount|||This is still basic syntax:
whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number
amount = 0
ServicePeriod = 54
For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i
formula = amount
works perfectly. Thanks for all the help.
Here is my code below. The one in VB and the one i have in Crystal Reports
Crystal Reports Code:
Dim ServicePeriod As number
ServicePeriod = {command.Advisor_Service_Period}
Dim amount As number
Dim i as number
For i=1 To 28
If ServicePeriod > 53 Then
formula = amount =+ 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
formula = amount =+ 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
formula = amount =+ 1000
ElseIf ServicePeriod = 0 Then
formula = amount =+ 800
End If
ServicePeriod =+ 1
Next i
VB Code:
Dim ServicePeriod As Integer = 1
Dim amount As Integer
For i As Integer = 1 To 28
If ServicePeriod > 53 Then
amount += 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount += 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount += 1000
ElseIf ServicePeriod = 0 Then
amount += 800
End If
ServicePeriod += 1Next iI don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.|||I don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.
Thanks for the quick response. removing the = helped eliminate the bool problem but for some reason i am still getting bad data. Should i maybe take a different approach on how to retreive this data? It returns 1000 for every record and does not seem to loop.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.
It does not seem to loop and add values. It instead just checks once and adds a value rather than looping for a set amount of times. How would you write the loop with crystal syntax. I have been stuck with this for a while any help is greatly appreciated.|||This is still basic syntax:
whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number
amount = 0
ServicePeriod = 54
For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i
formula = amount|||This is still basic syntax:
whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number
amount = 0
ServicePeriod = 54
For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i
formula = amount
works perfectly. Thanks for all the help.
Subscribe to:
Posts (Atom)