Is there a better way to write this?

I have a set base query which is causing me problems... the query look like
this
insert into TableA
(columnA1)
select
columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
columnB3 = a.columnC3)
from TableC as a
It's actaully population of a fact table from dimension tables. My problem
is that the subquery is failing because it is returning more than 1 value
from TableB, but I'm unable to pull out the records that are really
returning me the error. Anyone got any suggestions?Why the subquery? Why not use something like this (untested):
insert into TableA (columnA1)
select b.columnB1 from TableB b
inner join TableC c
on b.columnB2 = c.columnC2
and b.columnB3 = c.columnC3
mike hodgson
http://sqlnerd.blogspot.com
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
>I have a set base query which is causing me problems... the query look
>like this
> insert into TableA
> (columnA1)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> from TableC as a
> It's actaully population of a fact table from dimension tables. My problem
> is that the subquery is failing because it is returning more than 1 value
> from TableB, but I'm unable to pull out the records that are really
> returning me the error. Anyone got any suggestions?
>|||Thanks for the quick respond.. primarily the reason is that the actual query
is actaully a lot more complicated than the example, something like this
insert into TableA
(columnA1, columnA2, columnA3, ...)
select
columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
columnB3 = a.columnC3)
columnB2 = (select columnB2 from TableD where ...)
columnB3 = (other conditions)
from TableC as a
where ...
and not exists (...)
I don't think I can really write it using join statements considering the
tables and conditions involved?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:OzbZFlJHGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Why the subquery? Why not use something like this (untested):
> insert into TableA (columnA1)
> select b.columnB1 from TableB b
> inner join TableC c
> on b.columnB2 = c.columnC2
> and b.columnB3 = c.columnC3
>
> --
> mike hodgson
> http://sqlnerd.blogspot.com
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
A wild guess not supported in any way by your posting:
INSERT INTO TableA (columnA1)
SELECT B.columnB1
FROM TableB AS B
WHERE B.columnB2 = A.columnC2
AND B.columnB3 = A.columnC3);
The syntax was wrong afte that point. This can still blow up since we
have no DDL.|||to find the rows that are causing you fits, try something like
SELECT B2, B3, COUNT(*)
FROM TableB
GROUP BY B2, B3
HAVING COUNT(*) > 1
See if that points you in the right direction.
Stu|||Surely you can still do it without all the subqueries (unless the values in
different columns for a single row are unrelated), you just need to figure
out the appropriate SELECT statement. It's hard to offer helpful
suggestions in your case as you've provided us with very little info -
posting some relevant schema & test data, as Joe suggested, would make it
easier for others to help you.
mike hodgson
http://sqlnerd.blogspot.com
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23d$1SwJHGHA.3036@.tk2msftngp13.phx.gbl...
> Thanks for the quick respond.. primarily the reason is that the actual
> query is actaully a lot more complicated than the example, something like
> this
> insert into TableA
> (columnA1, columnA2, columnA3, ...)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> columnB2 = (select columnB2 from TableD where ...)
> columnB3 = (other conditions)
> from TableC as a
> where ...
> and not exists (...)
> I don't think I can really write it using join statements considering the
> tables and conditions involved?
> "Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
> news:OzbZFlJHGHA.2444@.TK2MSFTNGP11.phx.gbl...
>|||Is the problem that you have duplicate rows which you need to clean up?
Stu suggested and approach to identify the duplicates. A unique constraint
will prevent them from occurring in the future.
Or do you want to have the SQL ignore these duplicates, and just take the
first value that it finds?
Try using max(columnB1) or Min(columnB1) in your sub select
OR try this...
columnB1 = (select top 1 columnB1 from TableB where columnB2 =
a.columnC2 and columnB3 = a.columnC3)
Or is your join incorrect in the subquery and you need another key field in
there?
Maybe the data is perfect and you are just not being specific enough?
As others have said, the DDL will help make some of this clear, as will a
more in depth explanation.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
> I have a set base query which is causing me problems... the query look
like
> this
> insert into TableA
> (columnA1)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> from TableC as a
> It's actaully population of a fact table from dimension tables. My problem
> is that the subquery is failing because it is returning more than 1 value
> from TableB, but I'm unable to pull out the records that are really
> returning me the error. Anyone got any suggestions?
>