OUTPUT - help with please

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @.Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @.SQL @.Params

@.SQL is the Proc Name (varchar) and @.Params is the parameter string (nvarchar).

If I include the @.Fatal_Error variable in the dynamic creation of the @.Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @.Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @.SQL @.Param_List = @.Fatal_Error

AND I also tried:

EXEC @.SQL @.Param_List + ' '+@.Fatal_Error+' '

You have to use the sp_executesql for parameterized dynamic sql,

Code Snippet

Declare @.idParm as int;

Declare @.nameParm as varchar(100);

Declare @.dynamicSql as nvarchar(1000);

Declare @.dynamicParamDef as nvarchar(1000);

Set @.idParm = 2;

Set @.nameParm = 'sysobjects'

Set @.dynamicSql = N'Select * from sysobjects where id=@.id or name=@.name'

Set @.dynamicParamDef = N'@.id as int, @.name as varchar(100)'

Exec sp_executesql @.dynamicSql, @.dynamicParamDef, @.idParm, @.nameParm

|||

I quote from BOL(look for sp_executesql, building statement at runtime)

"

Transact-SQL supports the following methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string."|||...and Manivannan prove it|||

Thank you all for your assistance. I will give it a whirl.

I was able to finally execute with EXEC @.SQL @.Params, @.Fatal_Error = @.Fatal_Error

However, I'm sure this will come back to bite me in the long run..

Thank you ALL for your quick replies!

|||

@.SQL is my stored procedure name

@.Param_List is the list of enumerated parameters

SET @.SQL = @.SQL + IsNull(@.Param_List,'');

EXECUTE sp_executesql @.SQL;

Worked like a charm.

Thanks!

Sandy

outer-join results to cartesian product .... help!

All,

A very happy New Year to you all!!!

I have two tables f and sm

structure for f:

uid int not null,
cbuid int not null,
pid int not null,
gid int not null,
sid int not null,
mnth tinyint not null

structure for sm:

sid int not null,
mnth tinyint not null

contents/rows in table f are:

uid cbid pid gid sid mnth
-- -- -- -- -- --
8 92 10057 4 40 2
8 92 10057 4 40 3
8 92 10057 4 40 4
18 125 10057 4 40 2

contents/rows in table sm are:

sid mnth
-- --
40 2
40 3
40 4
40 5

The requirement is compare (f, sm) and return matching and non-matching
rows.

now the sql:

1)

select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '125'

output:

uid cbid pid gid sid
mnth mnth
---- ------- ---- ---- ----
-- --
18 125 10057 4 40
2 5 -> Row retrieved
18 125 10057 4 40
2 4
18 125 10057 4 40
2 3
18 125 10057 4 40
2 2

The above output returns as expected until I change the predicate....
See below:

select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '92'

output:

uid cbid pid gid sid
mnth mnth
---- ------- ---- ---- ----
-- --
8 92 10057 4 40
2 5
8 92 10057 4 40
3 5
8 92 10057 4 40
4 5
8 92 10057 4 40
2 4
8 92 10057 4 40
3 4
8 92 10057 4 40
4 4
8 92 10057 4 40
2 3
8 92 10057 4 40
3 3
8 92 10057 4 40
4 3
8 92 10057 4 40
2 2
8 92 10057 4 40
3 2
8 92 10057 4 40
4 2

The above output seems to be cartesian ?

Please help on how to resolve ...

2)

Is there a way where I could have non-matching rows like MINUS in
Oracle... I even tried NOT EXISTS but that did not work...

Any thoughts would be highly appreciated...
Thanks a bunch in advance,
AnuOn 12 Jan 2005 19:24:20 -0800, anuu_radhaa@.yahoo.com wrote:

(snip)
>The above output seems to be cartesian ?
>Please help on how to resolve ...

Hi Anu,

The output appears to be correct. Three rows in table f match the filter
condition in the WHERE clause. Each of these three rows matches the join
condition in the ON clause for all 4 rows in table sm, so you'll get a
result set of (3 x 4 =) 12 rows.

You seem to expect different results, but you didn't specify what the
desired results are and why.

>2)
>Is there a way where I could have non-matching rows like MINUS in
>Oracle... I even tried NOT EXISTS but that did not work...

I don't know Oracle, nor the MINUS operator. Is MINUS the Orcale
implementation of the ANSI-standard EXCEPT operation? Or does it something
else?

Both of your questions can be answered lots better if you provide
a) A SQL script to create your tables (including constraints and indexes,
but excluding irrelevant columns) and fill them with some sample data, and
b) The expected output, along with aan explanation.

Also, read http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||2)

You can do it with an outer join. Example:

CREATE TABLE A (x INTEGER PRIMARY KEY)
CREATE TABLE B (x INTEGER PRIMARY KEY)

INSERT INTO A VALUES (1)

SELECT A.x
FROM A
LEFT JOIN B
ON A.x = B.x
WHERE B.x IS NULL

--
David Portas
SQL Server MVP
--|||Thanks all for the info.

Here are the details

CREATE TABLE [FCast] (
[BusinessUnitId] [int] NOT NULL ,
[UserId] [int] NOT NULL ,
[SeasonId] [int] NOT NULL ,
[FMonth] [tinyint] NULL ,
[DivisionId] [int] NOT NULL ,
[ProductId] [int] NOT NULL
)
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 3, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 4, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 125, 18, 40, 2, 4, 10057 )
GO

CREATE TABLE [SMonths] (
[SeasonId] [int] NOT NULL ,
[SMonth] [tinyint] NOT NULL
)

GO

INSERT INTO [SMONTHS] VALUES (40, 2)
GO

INSERT INTO [SMONTHS] VALUES (40, 3)
GO

INSERT INTO [SMONTHS] VALUES (40, 4)
GO

INSERT INTO [SMONTHS] VALUES (40, 5)
GO

one of my colleage happened to delete all those values having 'null'
which caused the problem.

for every month in smonths there would be a row in fcast for a
productid. earlier, the application,
would insert a row into fcast table with month value as 'null'. This
was actually a application bug.
to resolve this, my colleage did took up a hasty decision and wrote a
SQL which really blew up
all the rows in production environment...

the funniest part is, it is almost 3 months after this SQL is executed.

so, database restore is not possible...

hence, thought of writing a SQL which populates the missing rows in
fcast table.

so, now the requirement is to insert the missing rows in fcast table.

the query which i framed works fine for businessunitid = 125 and fails
for businessunitid = 92

the output should be:

userid businessunitid productid divisionid seasonid smonth
-- ----- --- ---- --- --
18 125 10057 4 40 3
18 125 10057 4 40 4
18 125 10057 4 40 5
8 92 10057 4 40 5

this output would then be inserted into fcast table...

any ideas or thoughts would really help...
thanks in advance,

Anu|||On 13 Jan 2005 18:06:42 -0800, anuu wrote:

>Thanks all for the info.
>Here are the details
(snip)

Hi Anu,

Thanks. Unfortunately, therre still are some questions to ask.

1. What are the keys for your tables? For SMonths, either SMonth or
(SeasonId, SMonth) are logical possibilities. For FCast, I can't even
begin to guess.

2. In your example, the input for business unit 125 consists of one row;
the output has three rows, with the "missing" months and the remaining
columns taken from the one row that is present. Fine. For business unit
92, the situation gets muddy: you start withh three rows and want to
create one extra row for the "missing" month, again with the remainig
columns taken from the rows already presen. But which one? In your
example, the three rows for BU 92 all have user 8, division 4 and product
10057. What would be the expected output if the input changes to
INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )

3. From your examples, it appears that there always is a row for the
"first" month of the season (month 2), but rows for subsequent months
might be missing. Is this a correct assumption or is your example
incomplete?

Here's some code that will produce the requested output from your sample
data, but relies very heavy on several assumptions. If my assumptions are
wrong, the code will produce incorrect results. I didn't try to optimize
it, as this is probably (hopefully!) a one-time operation.

SELECT f.BusinessUnitId, f.UserId, f.SeasonId,
s.SMonth, f.DivisionId, f.ProductId
FROM FCast AS f
INNER JOIN SMonths AS s
ON s.SeasonId = f.SeasonId
WHERE f.FMonth = (SELECT MIN(s2.SMonth)
FROM SMonths AS s2
WHERE s2.SeasonId = f.SeasonId)
AND NOT EXISTS (SELECT *
FROM FCast AS f2
WHERE f2.BusinessUnitId = f.BusinessUnitId
AND f2.FMonth = s.SMonth)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 13 Jan 2005 18:06:42 -0800, anuu wrote:
> >Thanks all for the info.
> >Here are the details
> (snip)
> Hi Anu,
> Thanks. Unfortunately, therre still are some questions to ask.
[Anu]: No issues, Hugo. Ready to answer the questions. Find below
embedded

> 1. What are the keys for your tables? For SMonths, either SMonth or
> (SeasonId, SMonth) are logical possibilities. For FCast, I can't even
> begin to guess.
[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
which relates to SMonths

> 2. In your example, the input for business unit 125 consists of one
row;
> the output has three rows, with the "missing" months and the
remaining
> columns taken from the one row that is present. Fine. For business
unit
> 92, the situation gets muddy: you start withh three rows and want to
> create one extra row for the "missing" month, again with the remainig
> columns taken from the rows already presen. But which one? In your
> example, the three rows for BU 92 all have user 8, division 4 and
product
> 10057. What would be the expected output if the input changes to
> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
[Anu]: Fine. If SMonths has these values

SI Mo SI = SeasonId, Mo = Month
---
40, 2
40, 3
40, 4
40, 5

then for the above input below would be output
BU = Business Unit, UI = User Id, SI = Season ID, DI =
Division ID, Mo = Month, PI = Product ID

BU UI SI DI Mo PI
--------
92, 8, 40, 2, 2, 10057
92, 8, 40, 2, 3, 10057
92, 8, 40, 2, 5, 10057

92, 7, 40, 3, 2, 10056
92, 7, 40, 3, 4, 10056
92, 7, 40, 3, 5, 10056

92, 6, 40, 4, 3, 10055
92, 6, 40, 4, 4, 10055
92, 6, 40, 4, 5, 10055

In short, FCast table would have per ProductId, per BU, all the months
available for a season.

> 3. From your examples, it appears that there always is a row for the
> "first" month of the season (month 2), but rows for subsequent months
> might be missing. Is this a correct assumption or is your example
> incomplete?

[Anu]: Nope, the assumption is not correct. For a season, the months
spread would be defined in SMonths
table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then the
output for the above input (in point 2) would differ. The available
rows in FCast would _be_ the ones defined in SMonths.

> Here's some code that will produce the requested output from your
sample
> data, but relies very heavy on several assumptions. If my assumptions
are
> wrong, the code will produce incorrect results. I didn't try to
optimize
> it, as this is probably (hopefully!) a one-time operation.
> SELECT f.BusinessUnitId, f.UserId, f.SeasonId,
> s.SMonth, f.DivisionId, f.ProductId
> FROM FCast AS f
> INNER JOIN SMonths AS s
> ON s.SeasonId = f.SeasonId
> WHERE f.FMonth = (SELECT MIN(s2.SMonth)
> FROM SMonths AS s2
> WHERE s2.SeasonId = f.SeasonId)
> AND NOT EXISTS (SELECT *
> FROM FCast AS f2
> WHERE f2.BusinessUnitId = f.BusinessUnitId
> AND f2.FMonth = s.SMonth)
[Anu]: Thanks, Hugo. I would start working on this and see if I could
accomplish. Meanwhile, let me know if you need more info....

> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 14 Jan 2005 17:10:05 -0800, anuu wrote:

Hi Anu,

(snip)
>> 1. What are the keys for your tables? For SMonths, either SMonth or
>> (SeasonId, SMonth) are logical possibilities. For FCast, I can't even
>> begin to guess.
>>
>[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
>which relates to SMonths

Huh? The sample data you posted in your original post in this thread
violates the key you state for FCast - it has two rows for SeasonId 40,
FMonth 2, which would not be possible with the key you state above!

>> 2. In your example, the input for business unit 125 consists of one
>row;
>> the output has three rows, with the "missing" months and the
>remaining
>> columns taken from the one row that is present. Fine. For business
>unit
>> 92, the situation gets muddy: you start withh three rows and want to
>> create one extra row for the "missing" month, again with the remainig
>> columns taken from the rows already presen. But which one? In your
>> example, the three rows for BU 92 all have user 8, division 4 and
>product
>> 10057. What would be the expected output if the input changes to
>> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
>> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
>> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
>>
>[Anu]: Fine. If SMonths has these values
>SI Mo SI = SeasonId, Mo = Month
>---
>40, 2
>40, 3
>40, 4
>40, 5
>
>then for the above input below would be output
>BU = Business Unit, UI = User Id, SI = Season ID, DI =
>Division ID, Mo = Month, PI = Product ID
>BU UI SI DI Mo PI
>--------
>92, 8, 40, 2, 2, 10057
>92, 8, 40, 2, 3, 10057
>92, 8, 40, 2, 5, 10057
>92, 7, 40, 3, 2, 10056
>92, 7, 40, 3, 4, 10056
>92, 7, 40, 3, 5, 10056
>92, 6, 40, 4, 3, 10055
>92, 6, 40, 4, 4, 10055
>92, 6, 40, 4, 5, 10055
>In short, FCast table would have per ProductId, per BU, all the months
>available for a season.

Again: huh? This data would never be accepted in the table if the primary
key for FCast is (SeasonID, FMonth), as you state above. So I guess that's
not the primary key after all.

Also, in a previous post you wrote "for every month in smonths there would
be a row in fcast for a productid". Now, you write that you need to have a
row for every month "per ProductId, per BU". Not exactly the same, right?

I guess I could now make a new guess at the primary key in FCast, then
change the code I posted before to reflect my new guess. But there would
still be a lot of uncertainty. So instead of wasting time on writing a new
query on insufficient specs, I'll now refer you to www.aspfaq.com/5006,
where you will find instructions on how to assemble the details you should
post here to get help, in the best format for this group: SQL.

Also, please tell me the expected output if the input looks like this:

BU UI SI DI Mo PI
--------
92, 8, 40, 2, 2, 10057
92, 7, 40, 2, 3, 10057
92, 7, 40, 3, 5, 10057

From your description above, I guess there should be one extra row, for BU
92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI and
DI?

>> 3. From your examples, it appears that there always is a row for the
>> "first" month of the season (month 2), but rows for subsequent months
>> might be missing. Is this a correct assumption or is your example
>> incomplete?
>>
>[Anu]: Nope, the assumption is not correct. For a season, the months
>spread would be defined in SMonths
>table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then the
>output for the above input (in point 2) would differ. The available
>rows in FCast would _be_ the ones defined in SMonths.

And if the SeasonId 40 has months 12, 1, 2, 3, and 4, would there than be
any months that is "complete", such as month 2 was "complete" in your
original sample data?
Please post better sample data (as INSERT statements - see the link I
supplied above), indicating all possible situations. The "garbage in,
garbage out" principle applies in this group as much as anywhere else!

>[Anu]: Thanks, Hugo. I would start working on this and see if I could
>accomplish. Meanwhile, let me know if you need more info....

I don't "need" more info. But if could probably help you better if you
provided more info...

If you need more help, then please provide table structure (as CREATE
TABLE statements, including constrainst but excluding irrelevant columns),
sample data (as INSERT statements) and expected output. In case you missed
the link above: see www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Anu,

(snip)

>> 1. What are the keys for your tables? For SMonths, either SMonth or
>> (SeasonId, SMonth) are logical possibilities. For FCast, I can't
even
>> begin to guess.

>[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
>which relates to SMonths

Huh? The sample data you posted in your original post in this thread
violates the key you state for FCast - it has two rows for SeasonId 40,
FMonth 2, which would not be possible with the key you state above!

[Anu_Again] Hugo, the key mentioned is Foreign keys and not Primary.

- Hide quoted text -
- Show quoted text -

>> 2. In your example, the input for business unit 125 consists of one
>row;
>> the output has three rows, with the "missing" months and the
>remaining
>> columns taken from the one row that is present. Fine. For business
>unit
>> 92, the situation gets muddy: you start withh three rows and want to
>> create one extra row for the "missing" month, again with the
remainig
>> columns taken from the rows already presen. But which one? In your
>> example, the three rows for BU 92 all have user 8, division 4 and
>product
>> 10057. What would be the expected output if the input changes to

>> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
>> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
>> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
>[Anu]: Fine. If SMonths has these values

>SI Mo SI = SeasonId, Mo = Month

>---
>40, 2
>40, 3
>40, 4
>40, 5

>then for the above input below would be output

>BU = Business Unit, UI = User Id, SI = Season ID, DI =
>Division ID, Mo = Month, PI = Product ID

>BU UI SI DI Mo PI

>--------
>92, 8, 40, 2, 2, 10057
>92, 8, 40, 2, 3, 10057
>92, 8, 40, 2, 5, 10057

>92, 7, 40, 3, 2, 10056
>92, 7, 40, 3, 4, 10056
>92, 7, 40, 3, 5, 10056

>92, 6, 40, 4, 3, 10055
>92, 6, 40, 4, 4, 10055
>92, 6, 40, 4, 5, 10055

>In short, FCast table would have per ProductId, per BU, all the months
>available for a season.

Again: huh? This data would never be accepted in the table if the
primary
key for FCast is (SeasonID, FMonth), as you state above. So I guess
that's
not the primary key after all.

Also, in a previous post you wrote "

for every month in smonths there would
be a row in fcast for a productid

". Now, you write that you need to have a
row for every month "per ProductId, per BU". Not exactly the same,
right?

[Anu_again]: Hugo, it is foreign key and not primary key. primary key
is an identity column which I did not incude
in the structure as I thought that will not make any difference.
Nope, it is same. all these ProductID, BU are all foreign keys in FCast
table. the primary key
is only an identity column.

I guess I could now make a new guess at the primary key in FCast, then
change the code I posted before to reflect my new guess. But there
would
still be a lot of uncertainty. So instead of wasting time on writing a
new
query on insufficient specs, I'll now refer you to www.aspfaq.com/5006,
where you will find instructions on how to assemble the details you
should
post here to get help, in the best format for this group: SQL.

[Anu_again]: No guesses.....

Also, please tell me the expected output if the input looks like this:

BU UI SI DI Mo PI

--------
92, 8, 40, 2, 2, 10057
92, 7, 40, 2, 3, 10057
92, 7, 40, 3, 5, 10057

>From your description above, I guess there should be one extra row, for
BU
92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI
and
DI?

[Anu_again] : should be 92, 7, 40, 3, 4, 10057. You are right

>> 3. From your examples, it appears that there always is a row for the
>> "first" month of the season (month 2), but rows for subsequent
months
>> might be missing. Is this a correct assumption or is your example
>> incomplete?

>[Anu]: Nope, the assumption is not correct. For a season, the months
>spread would be defined in SMonths
>table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then
the
>output for the above input (in point 2) would differ. The available
>rows in FCast would _be_ the ones defined in SMonths.

And if the SeasonId 40 has months 12, 1, 2, 3, and 4, would there than
be
any months that is "complete", such as month 2 was "complete" in your
original sample data?
[Anu_again]: Complete ? The available months in FCast table are
considered as complete and the ones
not are to be INSERTed

Please post better sample data (as INSERT statements - see the link I
supplied above), indicating all possible situations. The "garbage in,
garbage out" principle applies in this group as much as anywhere else!

[Anu_again]: I feel, I did not communicate properly and this caused the
confusion otherwise you are in
the right track.

>[Anu]: Thanks, Hugo. I would start working on this and see if I could
>accomplish. Meanwhile, let me know if you need more info....

I don't "need" more info. But if could probably help you better if you
provided more info...

If you need more help, then please provide table structure (as CREATE
TABLE statements, including constrainst but excluding irrelevant
columns),
sample data (as INSERT statements) and expected output. In case you
missed
the link above: see

www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address|||On 16 Jan 2005 16:25:23 -0800, anuu wrote:

(snip)
>[Anu_again]: Hugo, it is foreign key and not primary key. primary key
>is an identity column which I did not incude
>in the structure as I thought that will not make any difference.

Hi Anu,

You're right, knowing that the primary key is an identity column doesn't
help me to help you. But knowing the natural key of your data would have
helped. I assume that you do know the difference between an artificial key
(identity) and a natural key? I also assume that you are aware than even
if you use an identity column as primary key, the natural key should still
be declared (using a UNIQEU constraint)?

If you had posted your table structure and illustrative sample data, as I
requested in my previous message, then I would now be able to see the
PRIMARY KEY constraint on the identity column, as well as the UNIQUE
constraint on whatever combination of columns makes up the natural key for
this table. This is information I really *need* in order to write a query
that returns the rows you need.

(snip)
>>Also, please tell me the expected output if the input looks like this:
>>
>>
>>BU UI SI DI Mo PI
>>
>>
>>--------
>>92, 8, 40, 2, 2, 10057
>>92, 7, 40, 2, 3, 10057
>>92, 7, 40, 3, 5, 10057
>>
>>>From your description above, I guess there should be one extra row, for
>>BU
>>92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI
>>and
>>DI?
>[Anu_again] : should be 92, 7, 40, 3, 4, 10057. You are right

While I still don't know the natural key of your table, your answer
supports my hunch that the natural key is the combination of (business
unit, productid, seasonid, month).
On the other hand, your answer also raises some questions. WHY should the
user id in the extra row be 7 (as in the rows for march and may), not 8
(as in the row for february)? And why should the division in the extra row
be 3 (as in the row for may), not 2 (as in the rows for february and
march)? This part of the specifications is still unclear!

(snip)
>>Please post better sample data (as INSERT statements - see the link I
>>supplied above), indicating all possible situations. The "garbage in,
>>garbage out" principle applies in this group as much as anywhere else!
>[Anu_again]: I feel, I did not communicate properly and this caused the
>confusion otherwise you are in
>the right track.

You're right. The proper way to communicate in this group, is to post your
table structure as CREATE TABLE statements, including all constraints and
properties, some illustrative sample data as INSERT statements and the
output expected from that sample data.
You did post a partial table structure in an earlier post, but you didn't
include the constraints. You also posted some sample data, but it was not
illustrative of your problem, so the query I wrote and tested against that
set of sample data will probably not be of much use.

If you still need assistance, I strongly urge you (again!) to read the
information at http://www.aspfaq.com/etiquette.asp?id=5006 and follow
those instructions to post the information and specifications that are
required to get a good working solution to your problem.
Without clear specifications, table structure and good sample data, I
really don't think I can help you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Outer join query

Hi!
I have a problem with a query:
Two tables:
CREATE TABLE Emp (empno INT, depno INT)
CREATE TABLE Work (empno INT, depno INT, date DATETIME)

I want a list of all employees that belongs to a department (from Emp
table), together with ("union") all employeees WORKING on that department a
spescial day (An employee can have been borrowed from another department
which he does not belong)

Sample data
INSERT INTO Emp (empno, depno) VALUES (1,10)
INSERT INTO Emp (empno, depno) VALUES (2,10)
INSERT INTO Emp (empno, depno) VALUES (3,20)

INSERT INTO Work (empno, depno, date) VALUES (1,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-18')

Note that Employee 3 works on a department to which he does not belong (he
is borrowed to another department)

The following query
SELECT empno, depno, date FROM work WHERE depno = 10 AND date = '2003-10-17'
gives me this result set:

empno depno date
1 10 2003-10-17 00:00:00.000
3 10 2003-10-17 00:00:00.000

But I want employee 2 to appear in the result set as well, because he
belongs to department 10 (eaven thoug he is not working this particular day)

The result set should look like this
empno depno date
1 10 2003-10-01 00:00:00.000
2 10 NULL
3 10 2003-10-01 00:00:00.000

I have tried different approaches, but none of them is good.
Could someone please help me?
Thanks in advance

Regards,
Gunnar Vyenli
EDB-konsulent as
NORWAYSELECT empno, depno,
CASE [date] WHEN '20031017' THEN [date] END AS [date]
FROM Work
WHERE depno = 10

Date is a reserved word and shouldn't be used as a column name (it's a
pretty meaningless name for a column anyway - Date of what?)

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for your reply, but am afraid this will not do.

I need data from BOTH the tables, not only from Work.
With your query, employee 2 will not be included in the result set, because
he belongs to the Employee table.

In other words: I want a list of all employees who belongs to depno 10,
TOGETHER with all employees which does not belong to depno 10, but work on
depno 10 this particular day.

We are talking about two categories of employees:
1) All the employees who belong to depno 10 (whether they work this day or
not)
2) Those employees who does NOT belong to depno 10, BUT is working at depno
10 this date.

A new suggestion would be apprechiated.

-Gunnar

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yrednQ5UUuzGQxKiRVn-vQ@.giganews.com...
> SELECT empno, depno,
> CASE [date] WHEN '20031017' THEN [date] END AS [date]
> FROM Work
> WHERE depno = 10
> Date is a reserved word and shouldn't be used as a column name (it's a
> pretty meaningless name for a column anyway - Date of what?)
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
Work is (empno, date) and that there is an FK constraint on Work (empno NOT
NULL REFERENCES Emp (empno)):

SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
FROM Emp AS E
LEFT JOIN Work AS W
ON W.empno = E.empno AND W.[date] = '20031017'
WHERE E.depno = 10 OR W.depno = 10

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:cNednSW9WZJGaBKiRVn-vQ@.giganews.com...
> OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
> Work is (empno, date) and that there is an FK constraint on Work (empno NOT
> NULL REFERENCES Emp (empno)):
> SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
> FROM Emp AS E
> LEFT JOIN Work AS W
> ON W.empno = E.empno AND W.[date] = '20031017'
> WHERE E.depno = 10 OR W.depno = 10

Hi David, minor aside but the call to COALESCE is unnecessary as E.empno
will do. Nice solution.

So as to not be taking up bandwidth with total triviality, here's another take.

SELECT depno, empno, MAX(work_date) AS work_date
FROM (SELECT empno, depno, "date" AS work_date
FROM Work
UNION ALL
SELECT empno, depno, NULL AS work_date
FROM Emp) AS W
WHERE depno = 10 AND
(work_date = '20031017' OR work_date IS NULL)
GROUP BY depno, empno

Regards,
jag

> --
> David Portas
> ----
> Please reply only to the newsgroup
> --

outer join problem

outer join problem - hope I can explain it OK
4 tables
listingTypes
listingTypeID int
listings
listingID int, listingTypeID int, listingTitle varchar
listingKeys
keyID int, listingTypeID int, keyName varchar
listingKeyValues
listingID int, keyID int, keyValue varchar
In words:
I have a number of listingTypes defined
In a listingType I have multiple Listings
A listingType defines a set of keys that listings of this type may have e.g.
colour, size, etc
An individual listing may or may not have keyValues set
I'm trying to output the info so that even when keyValues aren't set I get a
row with a null in
e.g.
ListingType, listingTitle, keyName, keyValue
1, test1, colour, red
1, test1, size, big
1, test2, colour, null
1, test2, size, small
1, test3, colour, null
1, test3, size, null
Sounds like this should be easily solved with outer joins but I seem unable
to return rows like above
Any help gratefully receivedSelect ListingTitle, ListingID,
ListingTypeID,
KeyName, KeyID,
IsNull(KeyValue, 'NotSet') Value
From Listings L
Left Join ListingKeys K
On K.ListingTYpeID = L.ListingTypeID
Left Join ListingKeyValues V
On V.KeyID = K.KeyID
And V.ListingID = L.ListingID
"Joe Gass" wrote:

> outer join problem - hope I can explain it OK
> 4 tables
> listingTypes
> listingTypeID int
> listings
> listingID int, listingTypeID int, listingTitle varchar
> listingKeys
> keyID int, listingTypeID int, keyName varchar
> listingKeyValues
> listingID int, keyID int, keyValue varchar
> In words:
> I have a number of listingTypes defined
> In a listingType I have multiple Listings
> A listingType defines a set of keys that listings of this type may have e.
g.
> colour, size, etc
> An individual listing may or may not have keyValues set
> I'm trying to output the info so that even when keyValues aren't set I get
a
> row with a null in
> e.g.
> ListingType, listingTitle, keyName, keyValue
> 1, test1, colour, red
> 1, test1, size, big
> 1, test2, colour, null
> 1, test2, size, small
> 1, test3, colour, null
> 1, test3, size, null
> Sounds like this should be easily solved with outer joins but I seem unabl
e
> to return rows like above
> Any help gratefully received
>
>|||Thanks !!!
Would you beleive I've been trying to do this all day!
Cheers
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:346C2099-8997-4731-B60F-388CE4C9B44F@.microsoft.com...
> Select ListingTitle, ListingID,
> ListingTypeID,
> KeyName, KeyID,
> IsNull(KeyValue, 'NotSet') Value
> From Listings L
> Left Join ListingKeys K
> On K.ListingTYpeID = L.ListingTypeID
> Left Join ListingKeyValues V
> On V.KeyID = K.KeyID
> And V.ListingID = L.ListingID
> "Joe Gass" wrote:
>|||yr very welcome !
"Joe Gass" wrote:

> Thanks !!!
> Would you beleive I've been trying to do this all day!
> Cheers
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:346C2099-8997-4731-B60F-388CE4C9B44F@.microsoft.com...
>
>

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisG
See if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1
|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.
|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegr oups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to joi
n "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a
FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.33
0487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question
, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "a
Xb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can
be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model
(described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need*
it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Out params

I heva a sproc that looks something like this:
CREATE PROCEDURE GetData
@.Id INT,
@.Param1 INT OUT,
@.Param2 INT OUT,
@.Param3 INT OUT,
@.Param4 INT OUT,
@.Param5 INT OUT
AS
SET @.Param1 = (SELECT Value1 FROM MyTable WHERE [Id] = @.Id)
SET @.Param2 = (SELECT Value2 FROM MyTable WHERE [Id] = @.Id)
SET @.Param3 = (SELECT Value3 FROM MyTable WHERE [Id] = @.Id)
SET @.Param4 = (SELECT Value4 FROM MyTable WHERE [Id] = @.Id)
SET @.Param5 = (SELECT Value5 FROM MyTable WHERE [Id] = @.Id)
Now this means five selects. How can I rewrite it so it will do only one
select? (I do want to use out params. Not return a recordset.)
--
Mikael EngdahlUse below construct:
SELECT @.parm1 = value1, @.parm2 = value2, ...
FROM MyTable
WHERE...
Note that above will not give an error if the SELECT return more than one row.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael Engdahl" <mikael-l@.engdahl.no.spam.com> wrote in message
news:uUyK1POtDHA.1744@.TK2MSFTNGP12.phx.gbl...
> I heva a sproc that looks something like this:
> CREATE PROCEDURE GetData
> @.Id INT,
> @.Param1 INT OUT,
> @.Param2 INT OUT,
> @.Param3 INT OUT,
> @.Param4 INT OUT,
> @.Param5 INT OUT
> AS
> SET @.Param1 = (SELECT Value1 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param2 = (SELECT Value2 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param3 = (SELECT Value3 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param4 = (SELECT Value4 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param5 = (SELECT Value5 FROM MyTable WHERE [Id] = @.Id)
> Now this means five selects. How can I rewrite it so it will do only one
> select? (I do want to use out params. Not return a recordset.)
>
> --
> Mikael Engdahl
>

out of order identity field - sql2000

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don't understand why the values in tmp.C aren't in the order
suggested by the sort.

Any comments most appreciated
BevanTry ORDER BY C

<bevanward@.gmail.com> wrote in message
news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> Hi All
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.
> Any comments most appreciated
> Bevan|||Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:
> Try ORDER BY C
>
> <bevanward@.gmail.com> wrote in message
> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> > Hi All
> > I am finding unexpected results when inserted into a newly created
> > table that has a field of datatype int identity (1,1).
> > Basically the order I sort on when inserting into the table is not
> > reflected in the order of the values from the identity field.
> > Have I been wrong in assuming that it should reflect the order from the
> > sort?
> > The code is ...
> > create table tmp (A varchar(50), L float, C int identity(1,1))
> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> > and I don't understand why the values in tmp.C aren't in the order
> > suggested by the sort.
> > Any comments most appreciated
> > Bevan|||You can't rely on an IDENTITY column to be assigned in a particular order or
to not have gaps in the sequence, btw. Try assigning a rank value manually
instead:

CREATE TABLE #tmp (A VARCHAR(50),
L FLOAT,
C INT NOT NULL PRIMARY KEY)

CREATE TABLE #tmp1 (Aa VARCHAR(50),
Ll FLOAT(50),
PRIMARY KEY (Aa, Ll))

INSERT INTO #tmp1 (Aa, Ll)
SELECT 'ABC', 123.45
UNION SELECT 'DEF', 456.12
UNION SELECT 'XYZ', 999.99
UNION SELECT 'RST', 023.43
UNION SELECT 'GHI', 146.56

INSERT INTO #tmp (A, L, C)
SELECT t1.Aa, t1.Ll, COUNT(*) Rank
FROM #tmp1 t1
INNER JOIN #tmp1 t2
ON t1.Aa >= t2.Aa
AND t2.Ll >= t2.Ll
GROUP BY t1.Aa, t1.Ll
ORDER BY t1.Aa, t1.Ll

SELECT C, A, L
FROM #tmp
ORDER BY C

DROP TABLE #tmp1
DROP TABLE #tmp

<bevanward@.gmail.com> wrote in message
news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> Hi Mike
> Thanks for your comment - C is the field in the target table of the
> insert that I was hoping would increment in the same sequence as the
> sort of Aa, Ll
> Cheers
> Bevan
> Mike C# wrote:
>> Try ORDER BY C
>>
>>
>> <bevanward@.gmail.com> wrote in message
>> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
>> > Hi All
>>> > I am finding unexpected results when inserted into a newly created
>> > table that has a field of datatype int identity (1,1).
>>> > Basically the order I sort on when inserting into the table is not
>> > reflected in the order of the values from the identity field.
>>> > Have I been wrong in assuming that it should reflect the order from the
>> > sort?
>>> > The code is ...
>>> > create table tmp (A varchar(50), L float, C int identity(1,1))
>> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>>> > and I don't understand why the values in tmp.C aren't in the order
>> > suggested by the sort.
>>> > Any comments most appreciated
>> > Bevan
>|||Hi Mike

Thanks for your comprehensive response. I had always assumed that this
insert was dependable (sequential and contiguous) ... I guess I need to
go back and re-write anywhere I have existing code that made this
assumption.

Thanks again, most appreciated.

Cheers
Bevan

Mike C# wrote:
> You can't rely on an IDENTITY column to be assigned in a particular order or
> to not have gaps in the sequence, btw. Try assigning a rank value manually
> instead:
> CREATE TABLE #tmp (A VARCHAR(50),
> L FLOAT,
> C INT NOT NULL PRIMARY KEY)
> CREATE TABLE #tmp1 (Aa VARCHAR(50),
> Ll FLOAT(50),
> PRIMARY KEY (Aa, Ll))
> INSERT INTO #tmp1 (Aa, Ll)
> SELECT 'ABC', 123.45
> UNION SELECT 'DEF', 456.12
> UNION SELECT 'XYZ', 999.99
> UNION SELECT 'RST', 023.43
> UNION SELECT 'GHI', 146.56
> INSERT INTO #tmp (A, L, C)
> SELECT t1.Aa, t1.Ll, COUNT(*) Rank
> FROM #tmp1 t1
> INNER JOIN #tmp1 t2
> ON t1.Aa >= t2.Aa
> AND t2.Ll >= t2.Ll
> GROUP BY t1.Aa, t1.Ll
> ORDER BY t1.Aa, t1.Ll
> SELECT C, A, L
> FROM #tmp
> ORDER BY C
> DROP TABLE #tmp1
> DROP TABLE #tmp
>
> <bevanward@.gmail.com> wrote in message
> news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> > Hi Mike
> > Thanks for your comment - C is the field in the target table of the
> > insert that I was hoping would increment in the same sequence as the
> > sort of Aa, Ll
> > Cheers
> > Bevan
> > Mike C# wrote:
> >> Try ORDER BY C
> >>
> >>
> >> <bevanward@.gmail.com> wrote in message
> >> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> >> > Hi All
> >> >> > I am finding unexpected results when inserted into a newly created
> >> > table that has a field of datatype int identity (1,1).
> >> >> > Basically the order I sort on when inserting into the table is not
> >> > reflected in the order of the values from the identity field.
> >> >> > Have I been wrong in assuming that it should reflect the order from the
> >> > sort?
> >> >> > The code is ...
> >> >> > create table tmp (A varchar(50), L float, C int identity(1,1))
> >> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >> >> > and I don't understand why the values in tmp.C aren't in the order
> >> > suggested by the sort.
> >> >> > Any comments most appreciated
> >> > Bevan
> >|||<bevanward@.gmail.com> wrote in message
news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> Hi Mike
> Thanks for your comprehensive response. I had always assumed that this
> insert was dependable (sequential and contiguous) ... I guess I need to
> go back and re-write anywhere I have existing code that made this
> assumption.
> Thanks again, most appreciated.

No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
of the need for the self-join ranking method.|||Hi Mike

I have read fondly of row_number() in 2005 and can't wait. This has
existed in Oracle for years, from what I understand, and I'm not sure
how we have done without it for so long.

I have re-written the code for this and it doubles the execution time
unfortunately.

Thanks again for taking the time, most appreciated

Bevan

Mike C# wrote:
> <bevanward@.gmail.com> wrote in message
> news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> > Hi Mike
> > Thanks for your comprehensive response. I had always assumed that this
> > insert was dependable (sequential and contiguous) ... I guess I need to
> > go back and re-write anywhere I have existing code that made this
> > assumption.
> > Thanks again, most appreciated.
> No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
> of the need for the self-join ranking method.|||(bevanward@.gmail.com) writes:
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.

Interesting. I get it to work most of the time, and I've even been told
that this is guarranteed to work as expected. Definitely in SQL 2005,
but the source said it was OK for SQL 2000 as well.

However, if you are running on a multi-processor machine (including a
hyper-threaded CPU), try adding OPTION (MAXDOP 1) at the end of the
query.

Note that is you use SELECT INTO instead, there is no guarantee that
the order is the desired.

By the way, what does SELECT @.@.version say?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E41419257FYazorman@.127.0.0.1...
> (bevanward@.gmail.com) writes:
> Interesting. I get it to work most of the time, and I've even been told
> that this is guarranteed to work as expected. Definitely in SQL 2005,
> but the source said it was OK for SQL 2000 as well.

I've found that it doesn't work all too often; particularly, as you pointed
out, if you are running hyperthreading, multiple processors, or have
multiple programs updating the table simultaneously. In that third
situation IDENTITY can leave extremely large gaps in a sequence. In my
experience, the only thing an IDENTITY column can guarantee is a different
number for each row.

To be honest, I don't think the INSERT statement guarantees the order in
which the rows will be inserted, which is a large part of the OP's problem
in this situation. Normally it doesn't matter what order rows get inserted
as long as they get in there. In this case the OP is dynamically assigning
numeric identifiers to each row as they're inserted which makes the order of
insertion important.

BTW - I didn't think about it last night, but with the SELECT INTO statement
(instead of INSERT) you might be able to use the IDENTITY() function to
assign values in the order you require. But SELECT INTO requires the target
table not exist before it's run. I haven't tried it, so can't guarantee it
would work, but hey...|||>> I am finding unexpected results when inserted into a newly created table that has a field [sic] of datatype int identity (1,1). <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

Next, by definition -- repeat BY DEFINITION !!! -- IDENTITY is not a
key.

>> Have I been wrong in assuming that it should reflect the order from the sort? <<

Your assumptions are MUCH worse than that! You have missed ALL of the
foundations of RDBMS. As they say in Zen, you must empty your cup to
drink new tea. Please get a good book on RDBMS, take some time and get
it right before you kill someone.|||This might help from the SQL Engine team blog...

http://blogs.msdn.com/sqltips/archi.../20/441053.aspx

Its point 4, the identities are calculated in the right order just not
inserted but the insert order shouldn't matter if the identities are
calculated in the correct order.

1.. If you have an ORDER BY in the top-most SELECT block in a query, the
presentation order of the results honor that ORDER BY request
2.. If you have a TOP in the same SELECT block as an ORDER BY, any TOP
computation is performed with respect to that ORDER BY. For example, if
there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows
within a given sort. Note that this does not guarantee that subsequent
operations will somehow retain the sort order of a previous operation. The
query optimizer re-orders operations to find more efficient query plans
3.. Cursors over queries containing ORDER BY in the top-most scope will
navigate in that order
4.. INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which the
rows are inserted
5.. SQL Server 2005 supports a number of new "sequence functions" like
RANK(), ROW_NUMBER() that can be performed in a given order using a OVER
clause with ORDER BY
6.. For backwards compatibility reasons, SQL Server provides support for
assignments of type SELECT @.p = @.p + 1 ... ORDER BY at the top-most scope.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Mike C#" <xxx@.yyy.com> wrote in message
news:Gylkg.2679$%12.1269@.fe09.lga...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97E41419257FYazorman@.127.0.0.1...
>> (bevanward@.gmail.com) writes:
>> Interesting. I get it to work most of the time, and I've even been told
>> that this is guarranteed to work as expected. Definitely in SQL 2005,
>> but the source said it was OK for SQL 2000 as well.
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a different
> number for each row.
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted, which is a large part of the OP's problem
> in this situation. Normally it doesn't matter what order rows get
> inserted as long as they get in there. In this case the OP is dynamically
> assigning numeric identifiers to each row as they're inserted which makes
> the order of insertion important.
> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require. But SELECT INTO
> requires the target table not exist before it's run. I haven't tried it,
> so can't guarantee it would work, but hey...|||Mike C# (xxx@.yyy.com) writes:
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a
> different number for each row.

Gaps due to simultaneous updates is another story. If you want contiguous
numbers, you should not use IDENTITY for your real tables. (You can
still generate ids with help of a temp table with an IDENTITY column.)

> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted,

Correct.

> which is a large part of the OP's problem in this situation.

I hope it isn't! What should matter is in which order the IDENTITY values
are generated. And that is what is guaranteed, at least in SQL 2005.

> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require.

No! I pointed this out in my post, but I say it again: SELECT INTO
with the IDENTITY() function gives no guarantee about order, and is
overall more prone to botch the order.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e6timt$hna$1$8300dec7@.news.demon.co.uk...
> This might help from the SQL Engine team blog...
> http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
> Its point 4, the identities are calculated in the right order just not
> inserted but the insert order shouldn't matter if the identities are
> calculated in the correct order.

I noticed the blogger states "*most* of the rules are valid for SQL 2000
too", though he doesn't specify which ones.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E4EF3786F5CYazorman@.127.0.0.1...
> Mike C# (xxx@.yyy.com) writes:
>> I've found that it doesn't work all too often; particularly, as you
>> pointed out, if you are running hyperthreading, multiple processors, or
>> have multiple programs updating the table simultaneously. In that third
>> situation IDENTITY can leave extremely large gaps in a sequence. In my
>> experience, the only thing an IDENTITY column can guarantee is a
>> different number for each row.
> Gaps due to simultaneous updates is another story. If you want contiguous
> numbers, you should not use IDENTITY for your real tables. (You can
> still generate ids with help of a temp table with an IDENTITY column.)

So we agree on gaps.

>> To be honest, I don't think the INSERT statement guarantees the order in
>> which the rows will be inserted,
> Correct.

And insert statement order guarantees.

>> which is a large part of the OP's problem in this situation.
> I hope it isn't! What should matter is in which order the IDENTITY values
> are generated. And that is what is guaranteed, at least in SQL 2005.

But this is a SQL 2000 problem. If this is supposed to be guaranteed in SQL
2000 as well, then there's apparently a hot fix needed for the OP's problem.

>> BTW - I didn't think about it last night, but with the SELECT INTO
>> statement (instead of INSERT) you might be able to use the IDENTITY()
>> function to assign values in the order you require.
> No! I pointed this out in my post, but I say it again: SELECT INTO
> with the IDENTITY() function gives no guarantee about order, and is
> overall more prone to botch the order.

Hence my use of the word "might", as in "I didn't try this, so I don't know
if it will produce desired results or not."

OSQL Performance Problem

System info:
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)

Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index

I am using a stored procedure that updates the values based on the DocId. I have an program that creates a sql script file that should be executed. Approx. 440000 lines.

Example:
Using TableName
Go
SET NOCOUNT ON
GO
exec sp_SPNAME @.docId=1, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
exec sp_SPNAME @.docId=2, @.a = 'blah', @.b = 'blah', @.c = 'blah', @.d = 'blah', @.e = 'blah'
GO
repeats 440K.

Question: When I execute this script per osql.exe, the update takes more the 24 hours... Any suggestions?

Thanks in advance.System info:
Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)

Table info:
DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
DocId has a number, all other columns are null. No index

INDEX !!!

Put a clustered index on DocID.

CREATE CLUSTERED INDEX IXc_TableName_DocID ON TableName (DocID)
GO

Regards,

hmscott|||another route is to bulkcopy/insert all the new data into a table then do a single update against the base table. Index on docid would be desired when you start dml.|||Hi hmscott,

thanks for the reply and the sql. I added the index and, while it is much faster, it still takes more than 8 hours. Maybe this is normal for executing 440,000 statements?

Thanks,
Lens|||Hi oj,

thanks to you as well for the reply. I will change my program to make a csv file and see if a bulk update increases the speed.

Thanks,
Lens|||Hi,

just a quick status update. I changed my program to create a csv-file. Approx. 440,000 lines imported into temp table; less than 2 minutes. Update into final table, less than two minutes...zoinks.

Thanks again.