Correlated Subquery

Wonder if anyone can help

If I wanted 2 Values from a Correlated Subquery (same table join etc) would i have to have 2 subqueries or is there a better way to do it ?

ie.
SELECT t1.MyMainCode,
(SELECT SUM(t2.Qty) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty,
(SELECT SUM(t2.Qty2) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty2
FROM t1

Any Help appreciated

GWselect t1.MyMainCode
, sum(t2.Qty)
, sum(t2.Qty2)
from t1
left outer
join t2
on t1.MyMainCode = t2.MyMainCode
group
by t1.MyMainCode
rudy
http://r937.com/

Correlated Subqueries?

I need to loop through several rows and find matches for values and the
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LPA few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:
>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20
2 4 B 40
3 4 C
4 9 ABC 200
5 8 X
6 8 Y
7 8 Z
8 9 XYZ 210
9 9 ABC-XYZ
10 5 X1
11 5 X2
12 12 PQ 90
13 12 P 50
14 12 Q 40
15 15 MD
16 15 M 240
17 15 D 110
18 17 D1 60
19 17 D2 70
20 16 M1 50
Ideally I want something like this.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20 470
2 4 B 40 470
3 4 C 470
4 9 ABC 200 470
5 8 X 470
6 8 Y 470
7 8 Z 470
8 9 XYZ 210 470
9 9 ABC-XYZ 470
10 5 X1 470
11 5 X2 470
12 12 PQ 90 180
13 12 P 50 180
14 12 Q 40 180
15 15 MD 530
16 15 M 240 530
17 15 D 110 530
18 17 D1 60 530
19 17 D2 70 530
20 16 M1 50 530
"Steve Kass" wrote:
> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
> >I need to loop through several rows and find matches for values and the
> >aggregate a different column. Being a VB type and not as much the sql type
> >my approach would be to loop through the rows and be done with it. But that
> >does not fit here I need to do it in stored procedure. I was told that
> >correlated sub queries are the ticket to do what I wish but I can't seem to
> >get to to work.
> >
> >My sql looks like this:
> >use Prototype_BIDW_TALX
> >--Variable Declaration
> >DECLARE @.ParentID int
> >DECLARE @.UltPID int
> >DECLARE @.UltParentName varchar (50)
> >DECLARE @.TotalEEs int
> >DECLARE @.UltTopAcct bit
> >
> >set @.ParentID = (select distinct [Parent ID] from Customers)
> >
> >SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> >sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
> >WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> >group by [Company ID] , [Name],[accttype]
> >
> >I get this error:
> >Sub query returned more than 1 value. This is not permitted when the sub
> >query follows =, !=, <, <= , >, >= or when the sub query is used as an
> >expression.
> >
> >Isn't the correlated sub query supposed to return more than one value so you
> >can basically loop through?
> >
> >What am I missing?
> >
> >LP
> >
> >
> >
> >
>|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20
>2 4 B 40
>3 4 C
>4 9 ABC 200
>5 8 X
>6 8 Y
>7 8 Z
>8 9 XYZ 210
>9 9 ABC-XYZ
>10 5 X1
>11 5 X2
>12 12 PQ 90
>13 12 P 50
>14 12 Q 40
>15 15 MD
>16 15 M 240
>17 15 D 110
>18 17 D1 60
>19 17 D2 70
>20 16 M1 50
>
>Ideally I want something like this.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20 470
>2 4 B 40 470
>3 4 C 470
>4 9 ABC 200 470
>5 8 X 470
>6 8 Y 470
>7 8 Z 470
>8 9 XYZ 210 470
>9 9 ABC-XYZ 470
>10 5 X1 470
>11 5 X2 470
>12 12 PQ 90 180
>13 12 P 50 180
>14 12 Q 40 180
>15 15 MD 530
>16 15 M 240 530
>17 15 D 110 530
>18 17 D1 60 530
>19 17 D2 70 530
>20 16 M1 50 530
>
>"Steve Kass" wrote:
>
>>LP,
>> I don't see a correlated subquery here. You have two subqueries,
>>(select distinct [Parent ID] from Customers) and (select distinct
>>[CompanyParent ID] from Customers), both of which look like standalone
>>queries, and one or the other returns more than one row, apparently. A
>>subquery is correlated if it depends on some column value from the outer
>>query, as in
>>select ...
>>from someTable as T1
>>where [condition contains a query like:] (select ... from anywhere where
>>[condition or expression in select list includes T1.somecolumn])
>>It's very hard to guess what you want here. If you could give the table
>>declarations, sample data, and show the output you are looking for, it
>>would help.
>>Steve Kass
>>Drew University
>>LP wrote:
>>
>>I need to loop through several rows and find matches for values and the
>>aggregate a different column. Being a VB type and not as much the sql type
>>my approach would be to loop through the rows and be done with it. But that
>>does not fit here I need to do it in stored procedure. I was told that
>>correlated sub queries are the ticket to do what I wish but I can't seem to
>>get to to work.
>>My sql looks like this:
>>use Prototype_BIDW_TALX
>>--Variable Declaration
>>DECLARE @.ParentID int
>>DECLARE @.UltPID int
>>DECLARE @.UltParentName varchar (50)
>>DECLARE @.TotalEEs int
>>DECLARE @.UltTopAcct bit
>>set @.ParentID = (select distinct [Parent ID] from Customers)
>>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>>group by [Company ID] , [Name],[accttype]
>>I get this error:
>>Sub query returned more than 1 value. This is not permitted when the sub
>>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>>expression.
>>Isn't the correlated sub query supposed to return more than one value so you
>>can basically loop through?
>>What am I missing?
>>LP
>>
>>
>>

Correlated Subqueries?

I need to loop through several rows and find matches for values and the
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LP
A few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>
|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:

>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>
|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.
|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20
24B40
34C
49ABC200
58X
68Y
78Z
89XYZ210
99ABC-XYZ
105X1
115X2
1212PQ90
1312P50
1412Q40
1515MD
1615M240
1715D110
1817D160
1917D270
2016M150
Ideally I want something like this.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20470
24B40470
34C470
49ABC200470
58X470
68Y470
78Z470
89XYZ210470
99ABC-XYZ470
105X1470
115X2470
1212PQ90180
1312P50180
1412Q40180
1515MD530
1615M240530
1715D110530
1817D160530
1917D270530
2016M150530
"Steve Kass" wrote:

> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
>
|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
[vbcol=seagreen]
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20
>24B40
>34C
>49ABC200
>58X
>68Y
>78Z
>89XYZ210
>99ABC-XYZ
>105X1
>115X2
>1212PQ90
>1312P50
>1412Q40
>1515MD
>1615M240
>1715D110
>1817D160
>1917D270
>2016M150
>
>Ideally I want something like this.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20470
>24B40470
>34C470
>49ABC200470
>58X470
>68Y470
>78Z470
>89XYZ210470
>99ABC-XYZ470
>105X1470
>115X2470
>1212PQ90180
>1312P50180
>1412Q40180
>1515MD530
>1615M240530
>1715D110530
>1817D160530
>1917D270530
>2016M150530
>
>"Steve Kass" wrote:
>

Correct Table Structure - Optional Values

Hello,

I have 3 optional text boxes. I don't know if the best way to set up
the table would be a field for each box, since this would leave gaps in
the table if the user only filled in one box. Is there a good method
to use?? This is kind of like storing check box values, in that there
could be multiple answers.traceyburger@.sw.rr.com wrote:
> Hello,
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

You don't give us much to go on but based off what you said, it sounds
as if the data that goes in these text boxes should be in their own table.

Zach|||(traceyburger@.sw.rr.com) writes:
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

What do you mean with gaps? With this miniscule information, it sounds
to me that the columns mapping to these text boxes should be nullable.
Thus if a user only enters value in one box, you store NULL in the other
columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Correct Procedures for Testing Against NULLs from SQL Server

Hi all,
I have some C# code that is pulling data from a database where a majority of the values being retrieved areNULL, yet their initial column data types are bothstringandint, which means that I have to temporarily store theseNULL's inintandstringdatatypes in C#. Later on in my code I have to test against these values,and was wondering if I am doing it correctly with the following code.
The following statement the variableor_team_home_idis of astringdata type, but may have had aNULLvalue assigned to it from the database
if (!or_team_home_id.Equals(DBNull.Value)) {}
The following statement the variableor_manager_id is of aintdata type, but also may have aNULLvalue assigned to it from the database.
if (!Convert.IsDBNull(or_manager_id)){}
Are these the correct way to test against NULL values retrieved fromteh database and that are stored in their respective data types.
Tryst
For string types you could compare with System.DbNull.Value as in (VB.NET)
If strVal IS System.DbNull.Value then

End If
For numeric datatypes I'd recommend doing an ISNULL(column,0) from the SQL stmt so it will be easier/faster to check for 0 from the front end.sql

Copying tables each other but with new row numbers or something like that

Take T4.ProductID , T4.ProductName from T4 where T4.CartID = @.CartID
.IF EXISTS
.Insert Into T1 values(T4.ProductId) and Get the T1.RowNo (T1.RowNo autoincrement column)
.Take T5.ProductID , T5.ProductGroup where T5.ProductID = T4.ProductID
.IF EXISTS
.INSERT INTO T2 values(T1.RowNo,T5.ProductGroup)
.Take T6.ProductID,T6.ProductGroup,T6.ProductDetail Where T6.ProductID = T4.ProductName AND T6.ProductGroup = T5.ProductGroup
.IF EXISTS
.INSERT INTO T3 values (T1.RowNo,T5.ProductGroup,T6.ProductDetail)

I Hope i am clear. It is very confusing but try to thing. I really need help. I am trying to make a shopping site.
T1 stores ProductID, this table is ShoppingCart
T2 stores DetailGroup of Product stored in T1.
T3 stores Detail of Product.

T1 - T2 - T3
EX: 1,Samsung HDD - 1,External - 1,External,750GB

User can save this cart. When user saves cart, tables are copying each other. T1 to T4 , T2 to T5 , T3 to T6. So everything is ok up to this point. But when user try to add his/her saved cart to current cart problem begins.

T4 - T5 - T6
EX: 1,Samsung HDD - 1,External - 1,External,750GB

When user adds a product, it is inserted into T1 as a new row and first column(T1.RowNo) is autoincrement. I need to copy user cart values from T1 to T4 and get the inserted rows new RowNo and use it when inserteing the T5 into T2 and T6 into T3. I hopw i am clear. I try to write code but it was very long. If you need code please let me know. I REALLY NEED HELP .

AND

Another simple question. Are stored procedures takes values that comes from another stored procedure as Table. I mean SP1 returns a table. And SP2 takes that table row by row and makes the operation?

Ex: you are calling SP2 5 times for different 5 values(nvarchar(50),int). Likewise can you call SP2 one time and getting a Table whisch stores 5 nvarchar(50),int value as rows.

Happy Coding

"MasterG"

Surely your table names are NOT "T1", T2", etc.

|||Sure names are not T1 or T2 etc. But is it important? Just a table name. Why did you asked that?

Copy values from previous row

I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:

In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).

Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?

Thanks

Kory

as far as i know, the derived column transformation cannot store a value in a variable. your problem seems to cry out for a script component or custom component solution.

i hope this helps.

|||I would do this in a T-SQL, Stored Procedure, or Execute SQL Task, avoiding a cursor. I worked on a SQL script like this once and avoided using a cursor by using an incremented identity field.|||

KoryS wrote:

I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:

In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).

Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?

Thanks

Kory

I think you're going to need an asynchronous script transform in order to achieve that.

-Jamie

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
=============
VB .NET Developer
http://www.rocketscience.uk.com
Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
--
=============
VB .NET Developer
http://www.rocketscience.uk.comMatt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!
Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Thanks a lot guys... more easy than what I think
"Tibor Karaszi" wrote:

> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
>
>

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Thanks a lot guys... more easy than what I think :)
"Tibor Karaszi" wrote:
> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> > Hi guys,
> > I've a problem...
> > I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> > the values in T1 to the same table (T2) located on the SQL Server database
> > DB2.
> > I want to perform this task by using a stored procedure launched by the
> > server DB1.
> > How can I perform this task?
> > If I have the 2 table on the same db I'll do something like this:
> > SELECT ....
> > INTO T2
> > FROM T1 WHERE ....
> > but how can I write this by saying that the 2 tables are on differen
> > databases?
> > Thanks in advance for all that can help me!
>
>

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Thanks a lot guys... more easy than what I think
"Tibor Karaszi" wrote:

> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
>
>