Calculated fields

I'm an SRS 2005 newbie. I'm trying to create a Cash Requirements
Forecast that plots values in six weekly buckets (columns) based on
the Required Date from line items in the PO. I'm stuck on one last
task in the report - getting the values bucketed - and am trying to
use calculated fields to do so.
REQDATE is the field from the PO line item that I'm applying this
expression to. startdate is a parameter that I have setup. I'm
trying to limit the output in a field on the report to a one-week
period beginning on the startdate that the user selects. Here is my
expression for that calculated field -
=Fields!REQDATE.Value>=Parameters!startdate.Value And Fields!
REQDATE.Value < (Parameters!startdate.Value+8)
First, is this the correct way to do this? If not, how should I
calculate this value and add it to my Matrix?
Second, should I be using a Matrix in my report or a Table? It seems
less than intuitive as to how to add column fields in a Matrix so
perhaps I should be using a Table instead.
Third, please keep any responses in plain English as I'm not a
programmer and I did not stay at Holiday Inn Express last night.
Thanks and best regards,
Frank Hamelly
MCP-GP, MCT
East Coast Dynamics
www.eastcoast-dynamics.comQuestions:
1. What's a PO?
2. Is this what you want your report to look like:
Week of 12/3-12/10 | Week of
12/11-12/18 |
Bikes
50 58
Cars
1 3
Boats
0 99
I'm guessing you want to combine all items that fit in that week.
Some answers:
Your expression will not work unfortunately. Second, you should use
the SQL statement in the data table and your parameter to limit the
records for the dates you select. Sorry I have to talk geek now so
book a room in the Holiday Inn (I still have no idea what that joke
referenced). For example:
select *
from table1
where datetime >= @.startdate
When the user puts a start date, the query will only bring back data
after that so you are limiting your data from the get go. If you
understand me so far and you can answer my questions, I can try to
help you out a little more.|||On Dec 5, 9:53 am, SQL Guy <ayma...@.gmail.com> wrote:
> (I still have no idea what that joke referenced).
There is a long-running series of television commercials here in the
USA about a chain of hotels called "Holiday Inn Express". In the
commercial, a person is doing a very technical job (doctor,
electrician, etc) using all sorts of jargon, and at the end of the
commercial the person says "Oh, I'm not a <profession>, I just stayed
at a Holiday Inn Express last night", implying that simply sleeping at
the hotel will make you more rested and more intelligent the next
day.
As far as the query, SQL Guy is correct, it will be more efficient to
limit the data to 7 days in the Data tab (the query itself) rather
than filtering the data in the report. Imagine that 5 years from now
your query returned 20,000 rows, so every time you view the query, it
has to pull all the data from the database, then filter it down at
Report Render time to the 10 that occurred this week. Inefficient.
It is much better to put a WHERE clause in the query to let the server
limit the rows.
Next the "weekly buckets": go to your DataSet, right-click and Add a
new Field, then give it a calculated expression like:
= DateAdd( "d", 1 - Weekday(Fields!datetime.Value), Fields!
datetime.Value )
for every row in your DataSet, this returns a DateTime equal to the
Sunday in that week based off of the field datetime. You can then
group on this calculated field just like you would any othe from your
original dataset.
The Matrix object is good when you have an undetermined number of
elements that you want to group by. For example, you put LineItem in
the Rows group, the calculted Week field in the Column group, and
=Format( Sum( Fields!amount.Value ), "$#,##0.00") in the Details
group. You dont care how many line items you have to report on, or
how many weeks you are reporting, the object draws it.
But, a Table is good for... well... tabular reports. You have one
report for a week period, each Detail row is a line item, and you have
a column that is explicitly one value of your billing amount per line
item. Sum()s then go in headers and footers, with some grouping
capability as well. When you Group here (by right-clicking and Adding
a Group), it just repeats the Grouped sections, and every section has
the exact same explicit layout.
-- Scott|||Thank yoiu Scott and SQL Guy. Scott, what does the "d" in the
expression represent?
Thx, Frank|||Also, assuming I need 6 distinct weekly buckets in my report, I need
to define 6 calculated fields with explicit filtering for each field,
correct? If so, how/where do I insert the calculated fields into the
matrix so that the result is a 6-week header with one column for each
week?
Thanks so much. I really appreciate the help. I've been trying to
figure this out on my own for too long now!
Frank|||On Dec 5, 11:54 am, "Frank Hamelly, MCP-GP" <fhame...@.cfl.rr.com>
wrote:
> Also, assuming I need 6 distinct weekly buckets in my report, I need
> to define 6 calculated fields with explicit filtering for each field,
> correct? If so, how/where do I insert the calculated fields into the
> matrix so that the result is a 6-week header with one column for each
> week?
> Thanks so much. I really appreciate the help. I've been trying to
> figure this out on my own for too long now!
> Frank
The "d" represents Day. The DateAdd function is very funky. Unlike
normal VB, the DateAdd and Format commands use case sensitive
identifiers: MM for month, dd for day, yyyy for year, HH for 24 hour
format, hh for 12 hour format, mm for minutes, ss for second.
The way a Matrix works is that it takes the Field that you supplied,
breaks it into Groups containing distinct values of the contents of
the Field, then Aggregates on the Group in the Details section. The
two display groups are Rows and Columns.
Think of it this way, you have a list of items with similar
characteristics:
Shape, Color, Price
Circle, Red, $10
Circle, Green, $25
Square, Blue, $15
Square, Red, $5
If you drag the Shape field into a Row of a Matrix, and the Color into
a Column, and the =Sum( Fields!Price.Value ) in the Details section,
when you go to Preview mode you will see two rows (Circle and
Sequare), three columns (Red, Green, Blue), and Summed values ($10 |
$25 | Nothing || $5 | Nothing | $15) in the Details table. You didn't
have to define anything, it just evaluated the row contents and
created the groups.
For your dataset:
-- If using a Matrix, all you need is a Field that returns 6 distinct
values for your 6 weeks, and it will automatically build 6 columns.
Any function in the Details section is going to display the aggregate
for only the
-- If using a Table, you will need 6 distinct calculations, and it
gets a little trickier.
1. Add a "WeekDate" Field that rounds the ReqDate back to the first
day of the week using the method previously outlined.
2. Drag the "WeekDate" into the Column section of the Matrix
3. Drag the "LineItemName" into the Row section of the Matrix
4. Drag the "AmountNeedingAggregation" into the Details section of
the Matrix
Right-click on the Row/Column to and Edit Group to change Sorting,
Filtering, etc.
-- Scott|||Thanks for the Holiday Inn info. I've stayed in so many but I don't
watch TV at all so that's why I was missing that valuable nugget of
information. I now feel like I've spent the night there!
"d" stands for day. That function is quite nice for many things. Lets
say you want to default a parameter to last week you can simply type
for the default parameter =dateadd("d",-7,today()) - today is a
function that gets today's date in RS. So what I understand from
Orne's suggestion is that he will take the date in the field and
subtract it from... actually I'm not really following that so much
maybe because it's almost closing time. I think it can be done in
tabular form but I will wait for Orne's explanation so I can better
understand what he's trying to get at.|||Thank you Scott and SQL Guy for your help. I'm going to give your
further suggestions a try tomorrow.
Thanks again,
Frank

Calculated field is calculated wrong

Hi there,
(SQL Server 2000, running on Windows 2000 server, all service packs
installed)
I have a table with columns A, B and C, all three are tinyint. I added a
fourth column D, that has this formula:
(A * 3) + (B * 2) + C
This doesn't get calculated well, for example:
A = 8, B = 10, C = 10 -> D = 57
I worked around it by changing the formula into A + A + A + B + B + C which
works correctly, still I don't understand what is wrong with the first
formula... or is it a bug?
Any ideas are appreciated.
RayI could not reproduce this:
USE tempdb
CREATE TABLE t(A tinyint, B tinyint, C tinyint, D AS (A * 3) + (B * 2) + C)
INSERT INTO t (A, B, C) values(8, 10, 10)
SELECT * FROM t
A B C D
-- -- -- --
8 10 10 54
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"R. van Laake" <nospam_r.vanlaake@.dfk.nl> wrote in message
news:41f4c08d$0$14112$d456229f@.news.routit.net...
> Hi there,
> (SQL Server 2000, running on Windows 2000 server, all service packs
> installed)
> I have a table with columns A, B and C, all three are tinyint. I added a
> fourth column D, that has this formula:
> (A * 3) + (B * 2) + C
> This doesn't get calculated well, for example:
> A = 8, B = 10, C = 10 -> D = 57
> I worked around it by changing the formula into A + A + A + B + B + C whi
ch
> works correctly, still I don't understand what is wrong with the first
> formula... or is it a bug?
> Any ideas are appreciated.
> Ray
>
>|||Hmmm... still I get it.
I entered the formula using Enterprise Manager. When I enter:
(A * 3) + (B * 2) + C
EM changes it into:
([A]*3 + [B]*2 + [C])
which should be correct of course... but it does get calculated wrong
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPiS6jfAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> I could not reproduce this:
> USE tempdb
> CREATE TABLE t(A tinyint, B tinyint, C tinyint, D AS (A * 3) + (B * 2) +
C)
> INSERT INTO t (A, B, C) values(8, 10, 10)
> SELECT * FROM t
> A B C D
> -- -- -- --
> 8 10 10 54
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "R. van Laake" <nospam_r.vanlaake@.dfk.nl> wrote in message
> news:41f4c08d$0$14112$d456229f@.news.routit.net...
which
>|||Did you run the code that Tibor posted? What result did you get? If in
doubt avoid Enterprise Manager for data and schema changes - TSQL code
gives you more control. However, I couldn't reproduce the problem even
using EM - the result I get is 54.
If you still think there's a problem after trying Tibor's code then
post a complete, tested set of steps to reproduce the problem: CREATE
TABLE, INSERT followed by the exact steps you performed in EM. Also
tell us your product edition, version and service pack.
--
David Portas
SQL Server MVP
--|||Tibor's code runs just fine. I just changed my workaround formula
(A+A+A+B+B+C) back to (A*3)+(B*2)+C and now it does seem to work
correctly... I really don't understand. I am 100% sure there was no typing
error, and that the calculation was wrong. I even changed some values in the
A, B and C columns and saw a wrong calculation over and over again.
I will keep the (A*3)+(B*2)+C formula and if I see that it goes wrong
again, post here again.
Thanks for all your help,
Ray
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106563785.886482.117750@.c13g2000cwb.googlegroups.com...
> Did you run the code that Tibor posted? What result did you get? If in
> doubt avoid Enterprise Manager for data and schema changes - TSQL code
> gives you more control. However, I couldn't reproduce the problem even
> using EM - the result I get is 54.
> If you still think there's a problem after trying Tibor's code then
> post a complete, tested set of steps to reproduce the problem: CREATE
> TABLE, INSERT followed by the exact steps you performed in EM. Also
> tell us your product edition, version and service pack.
> --
> David Portas
> SQL Server MVP
> --
>

Calculated columns...

Hello,
can anyone help me with this?
SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
The point here is, if I use the following query
SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
the thing works, but for programming-technical reasons a have to use X
SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
What's wrong here?
Regards,
Kurt RogiersWHERE is evaluated before the field list, meaning that when you run
this, there is no column named "X" when the WHERE clause is evaluated.
Something like this will achieve the desired effect:
SELECT X
FROM (SELECT (A+B) AS X FROM TABLE) AS calcX
WHERE X = 'sqdf'
Kurt Rogiers wrote:
> Hello,
> can anyone help me with this?
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> The point here is, if I use the following query
> SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
> the thing works, but for programming-technical reasons a have to use X
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> What's wrong here?
> Regards,
> Kurt Rogiers|||You cannot use column aliases like this. You can only reference a column
alias in an ORDER BY clause, or outside of an inline query.
You can change the way you are coding this, to eliminate the "
programming-technical reasons " that require you to use "X".
You can also try one of these approaches:
select X from
(
SELECT (A+B) AS X FROM TABLE
)
where X = 'sqdf'
Create view MyView as
SELECT (A+B) AS X FROM TABLE
select X from MyView where X='sqdf'
"Kurt Rogiers" <k.rogiers@.skynet.be> wrote in message
news:%23jLUu%23viGHA.4056@.TK2MSFTNGP02.phx.gbl...
> Hello,
> can anyone help me with this?
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> The point here is, if I use the following query
> SELECT (A+B) AS X FROM TABLE WHERE (A+B) = 'sqdf'
> the thing works, but for programming-technical reasons a have to use X
> SELECT (A+B) AS X FROM TABLE WHERE X = 'sqdf'
> What's wrong here?
> Regards,
> Kurt Rogiers
>|||Hello,
life can be SOOO easy!!!
Thanks a lot, the solution works wonderful
Regard,
Kurt
"Jim Underwood" <james.underwoodATfallonclinic.com> schreef in bericht
news:O%23wf3IwiGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You cannot use column aliases like this. You can only reference a column
> alias in an ORDER BY clause, or outside of an inline query.
> You can change the way you are coding this, to eliminate the "
> programming-technical reasons " that require you to use "X".
> You can also try one of these approaches:
> select X from
> (
> SELECT (A+B) AS X FROM TABLE
> )
> where X = 'sqdf'
>
> Create view MyView as
> SELECT (A+B) AS X FROM TABLE
> select X from MyView where X='sqdf'
>
> "Kurt Rogiers" <k.rogiers@.skynet.be> wrote in message
> news:%23jLUu%23viGHA.4056@.TK2MSFTNGP02.phx.gbl...
>sql

calculated columns in a table

Hi,
I have a table with fields FirstName and LastName. I also have a column
FullName. Each time the table is updated, I'd like the field FullName
to be calculated automatically based on the values of FirstName and
LastName (FullName = FirstName + ' ' + LastName).
What Default value should I enter when I design the table ?
Thankscreate table test1 ( firstnm varchar(10),
lastname varchar(10),
fullname as firstnm + ' ' + lastname
)
insert into test1 (firstnm,lastname) values ('Omni','buzz')
select * from test1
-Omni|||Thanks. that answers my question :)
Omnibuzz wrote:

> create table test1 ( firstnm varchar(10),
> lastname varchar(10),
> fullname as firstnm + ' ' + lastname
> )
>
> insert into test1 (firstnm,lastname) values ('Omni','buzz')
> select * from test1
> -Omni

Calculated Columns

Hi,
In have a select query with one calculated column in the select column
collection. When I change the select FROM clause from table name to a table
defined with select statement, I get error. The query is:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
(Please copy paste the query somewhere else, it’s much easier to read and
understand the problem)
The error I get is:
Server: Msg 207, Level 16, State 3, Line 24
Invalid column name 'DISTRIBUTION CENTER'.Can you replace the * with the column names and then try executing it and
repaste the query if it doesn't work (with the error message).
Thanks
Omnibuzz|||Hi Omnibuzz, thanks for the quick response, but its not working. Here is the
query again:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:

> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Sorry, this is the updated query:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]
SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
[AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
FLOAT))) AS [SALES AMT]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:

> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Can you post the create script for my_table.
"Aviad" wrote:
> Hi Omnibuzz, thanks for the quick response, but its not working. Here is t
he
> query again:
> DECLARE @.YearsSet TABLE (
> [YEARCOLTIME] VARCHAR(8000))
> INSERT @.YearsSet
> SELECT [YEARCOLTIME]
> FROM (SELECT *,
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> WHERE [YEARCOLTIME] = N'2000'
> OR [YEARCOLTIME] = N'2001'
> GROUP BY [YEARCOLTIME]
> DECLARE @.ProductsSet TABLE (
> [PRODUCTS] VARCHAR(8000))
> INSERT @.ProductsSet
> SELECT [PRODUCTS]
> FROM [MY_TABLE]
> WHERE [PRODUCTS] = N'IES XXI JK'
> OR [PRODUCTS] = N'Troy Sys 4'
> OR [PRODUCTS] = N'Core Series 12'
> GROUP BY [PRODUCTS]
> SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
> [@.YEARSSET].[YEARCOLTIME],
> (SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS
> [AGGREGATEDSALES AMT],
> (SELECT AVG([SALES AMT])
> FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS
> FLOAT))) AS [SALES AMT]
> FROM (SELECT [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS
> [YEARCOLTIME]
> FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
> INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
> ON [@.PRODUCTSSET].[PRODUCTS] =
> [FORMULATIMELEVELTABLE].[PRODUCTS]
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
> AND [@.YEARSSET].[YEARCOLTIME] =
> [FORMULATIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
> [AGGREGATEDFORMULA0]
> FROM (SELECT [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> INNER JOIN @.YearsSet AS [@.YEARSSET]
> ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
> [@.YEARSSET].[YEARCOLTIME]
>
> "Omnibuzz" wrote:
>|||Here it is:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[Products] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Distribution Center] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Sales Amt] [float] NULL ,
[Time] [datetime] NULL ,
[Col100] [int] NOT NULL
) ON [PRIMARY]
GO
"Omnibuzz" wrote:
> Can you post the create script for my_table.
>
> "Aviad" wrote:
>|||Hi Aviad,
The create table script was having a syntax error. Fixed it.
But here it seems to work fine in my machine :)
try removing the distribution center column from the select and the group by
of the final query and try..|||Hi,
Its not working here, somehow it doesn’t "recognize" the column:
[TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
Which in the calculated column.
"Omnibuzz" wrote:

> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||and the fixed script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[Products] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Distribution Center] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Sales Amt] [float] NULL ,
[Time] [datetime] NULL ,
) ON [PRIMARY]
GO
sorry :->
"Omnibuzz" wrote:

> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||I remember someone posting something like this...
the query analyzer was taking the next line of the comment as commented or
something similar.
I am not able to see what the problem might be :(
few last resorts..
If MY_TABLE is really your table then rename the column with an "_" in
between and try to get rid of this square brackets.
try it in a new QA window (Never knew I could get down to this level :)
use it as "DISTRIBUTION CENTER" instead of sq bracks..
"Aviad" wrote:
> Hi,
> Its not working here, somehow it doesn’t "recognize" the column:
> [TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
> Which in the calculated column.
> "Omnibuzz" wrote:
>

Calculated column not working

Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much.

Dave Roda

Change the stored proc so that it only performs the calulation if both columns contain positive integers. There's no point calculating an average if either column has no value.

If ((TotalRating > 0 And TotalRating Is Not Null) AND (TotalVotes > 0 AND TotalVotes Is Not Null))

Update Tablename Set AverageRating = TotalRating/TotalVotes...

|||

Hi Dave,

Based on your description, I understand that you're getting null values in AverageRating if one of the other columns is null.

Actually, this is by design. In this case, you have to check for nulls using ISNULL in your calculate stored procedure.

HTH. If anything is unclear, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||This is a very helpful post thank you. I was wondering if there was a way to use conditionals in a stored procedure. Could you please give me a quick example? I understand the concept i will check if the cells values are null and set the value to one instead of incrementing null+1. That would work but it would also be just as easy to set the default value of the columns to 0 but i cannot figure out how to do this. Answers to either of those questions would be greatly appreciated. Thanks so much!|||

I'm not sure what's causing your problem with the default value. You shouldn't have any problem setting the default for a number column to 0; Can't you change this in table design mode (if sql doesn't work)? If you're using SQL express, you candownload a SQL Enterprise Manager-like interface to access and change database info, if you are more familiar with SQL Server than Server Explorer (again, I don't know what you're using right now).

As for conditionals:

DECLARE @.TotalintSELECT @.Total =CASEWHEN TotalVotesisnullTHEN 0WHEN TotalVotes < 0THEN 0ELSE TotalVotesFROM VoteTableWhere ItemID = 10Return @.Total

This statement dynamically assigns a value to a variable. If doing a view, etc., you could leave out the @.Total =, and the results would come through like a regular query but with your altered values.

Calculate Expression

Hello,
I have a query that I am using to calculate an order filled rate.
One of my columns is OrderNo. to get the total orders for a date I take the
order no. and just count to get a total for that column.
Now the trick is I need to subtract from that count cancelled orders.
How would I do that?
I have tried:
Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
Count(Fields!order_type.Value) - Fields!cancel_qty.Value
No luck either way.
I would appreciate any help that you could give. Thanks,
Kevindepends, perhaps:
count(Fields!order_type:Value) - sum(Fields!cancel_qty.Value)
might work.
"Kevin Eck" wrote:
> Hello,
> I have a query that I am using to calculate an order filled rate.
> One of my columns is OrderNo. to get the total orders for a date I take the
> order no. and just count to get a total for that column.
> Now the trick is I need to subtract from that count cancelled orders.
> How would I do that?
> I have tried:
> Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
> Count(Fields!order_type.Value) - Fields!cancel_qty.Value
> No luck either way.
> I would appreciate any help that you could give. Thanks,
> Kevin
>
>|||Thanks Jimbo...
worked like a charm!!!
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B167604A-34A2-4076-99A1-FF608AF774B5@.microsoft.com...
> depends, perhaps:
> count(Fields!order_type:Value) - sum(Fields!cancel_qty.Value)
>
> might work.
>
> "Kevin Eck" wrote:
>> Hello,
>> I have a query that I am using to calculate an order filled rate.
>> One of my columns is OrderNo. to get the total orders for a date I take
>> the
>> order no. and just count to get a total for that column.
>> Now the trick is I need to subtract from that count cancelled orders.
>> How would I do that?
>> I have tried:
>> Count(Fields!order_type.Value - Fields!cancel_qty.Value) and
>> Count(Fields!order_type.Value) - Fields!cancel_qty.Value
>> No luck either way.
>> I would appreciate any help that you could give. Thanks,
>> Kevin
>>
>>

C++ .net data binding

I have a datagrid control bound to a dataview of a table in a dataset.
I am using a tablestyle to control which of the table columns are
displayed in the datagrid. When any cell in a row is selected I want to
select and highlight the entire row.
By catching the mousedown event and using HitTest I can work out what
row was clicked on the control and set the selected row but I can
highlight the row.
Can anyone tell me where I am going wrong ?"Jez" <jezario@.hotmail.co.uk> wrote in message
news:1129131568.948426.123380@.g14g2000cwa.googlegroups.com...
>I have a datagrid control bound to a dataview of a table in a dataset.
> I am using a tablestyle to control which of the table columns are
> displayed in the datagrid. When any cell in a row is selected I want to
> select and highlight the entire row.
> By catching the mousedown event and using HitTest I can work out what
> row was clicked on the control and set the selected row but I can
> highlight the row.

> Can anyone tell me where I am going wrong ?
Posting in the wrong newsgroup. :-)

Bulkload copying identity columns within a table

The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
1TestData
Child
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.
I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>
|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. The
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:

> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>
|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...[vbcol=seagreen]
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote:

Bulkload copying identity columns within a table

The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
--
1 TestData
Child
--
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. Th
e
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be
the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:

> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote:
>