problem of UNION query

I maked a following query and excuted it.
SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
FROM
(
(
SELECT SUBJ0.shop AS shop, 0 AS dcrate, sum(SUBJ0.sellcnt) AS sellcnt
FROM
(
SELECT
[shop]shop,
[brnd]brnd,
[item]item,
[seq]seq,
[color]color,
[size]size,
[sellqty]sellqty,
[sellamt]sellamt,
[sellcnt]sellcnt,
[sellterm]sellterm,
[tempseq]tempseq
FROM
"HULUCKS.dbo.x_sellinfo" T1
) SUBJ0
GROUP BY SUBJ0.shop
)
UNION ALL
(
SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
FROM
( SELECT
[shop]shop,
[shopnm]shopnm,
[shop_type]shop_type,
[dcrate]dcrate,
[posgb]posgb,
[manachul]manachul
FROM
"HULUCKS.dbo.x_shop" T1
) SUBJ1
GROUP BY SUBJ1.shop
)
) UT
GROUP BY UT.shop
But occur following error.
"Invalid subject name 'HULUCKS.dbo.x_shop'"
Why occur this error?
Help me......Can you find out if this query works fine?
SELECT
[shop]shop,
[shopnm]shopnm,
[shop_type]shop_type,
[dcrate]dcrate,
[posgb]posgb,
[manachul]manachul
FROM
"HULUCKS.dbo.x_shop" T1|||Thank you for reading my question.
Yes.
This subquery is work fine.
There are no problem.
"Omnibuzz" wrote:

> Can you find out if this query works fine?
> SELECT
> [shop]shop,
> [shopnm]shopnm,
> [shop_type]shop_type,
> [dcrate]dcrate,
> [posgb]posgb,
> [manachul]manachul
> FROM
> "HULUCKS.dbo.x_shop" T1
>|||try this then, I don't know why you complicated the query.
This should give the same result (if I am not wrong)
SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
FROM
(
SELECT SUBJ0.shop AS shop, 0 AS dcrate, SUBJ0.sellcnt AS sellcnt
FROM
"HULUCKS.dbo.x_sellinfo" SUBJ0
UNION ALL
SELECT SUBJ1.shop AS shop, SUBJ1.dcrate AS dcrate, 0 AS sellcnt
FROM
"HULUCKS.dbo.x_shop" SUBJ1
) UT
GROUP BY UT.shop|||Thank you for quickly response.
I need of next case too.
SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
FROM
(
(
SELECT SUBJ0.shop AS shop, 0 AS dcrate, sum(SUBJ0.sellcnt) AS sellcnt
FROM
(
SELECT
[shop]shop,
[brnd]brnd,
[item]item,
[seq]seq,
[color]color,
[size]size,
[sellqty]sellqty,
[sellamt]sellamt,
[sellcnt]sellcnt,
[sellterm]sellterm,
[tempseq]tempseq
FROM
"HULUCKS.dbo.x_sellinfo" T1
) SUBJ0
GROUP BY SUBJ0.shop
)
UNION ALL
(
SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
FROM
( SELECT
T2.[shop_type]shop_type,
T1.[shop]shop,
T1.[sellqty]sellqty,
T1.[dcrate]dcrate,
T1.[sellamt]sellamt
FROM
"HULUCKS.dbo.x_sellinfo" T1
INNER JOIN "HULUCKS.dbo.x_shop" T2 ON T2.[shop]shop = T1.[shop]shop
) SUBJ1
GROUP BY SUBJ1.shop
)
) UT
GROUP BY UT.shop
In this case also raise same error.
But no error at the next time.
SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
FROM
(
(
SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
FROM
( SELECT
T2.[shop_type]shop_type,
T1.[shop]shop,
T1.[sellqty]sellqty,
T1.[dcrate]dcrate,
T1.[sellamt]sellamt
FROM
"HULUCKS.dbo.x_sellinfo" T1
INNER JOIN "HULUCKS.dbo.x_shop" T2 ON T2.[shop]shop = T1.[shop]shop
) SUBJ1
GROUP BY SUBJ1.shop
)
) UT
GROUP BY UT.shop
Thank's!!
"Omnibuzz" wrote:

> try this then, I don't know why you complicated the query.
> This should give the same result (if I am not wrong)
> SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
> FROM
> (
> SELECT SUBJ0.shop AS shop, 0 AS dcrate, SUBJ0.sellcnt AS sellcnt
> FROM
> "HULUCKS.dbo.x_sellinfo" SUBJ0
> UNION ALL
> SELECT SUBJ1.shop AS shop, SUBJ1.dcrate AS dcrate, 0 AS sellcnt
> FROM
> "HULUCKS.dbo.x_shop" SUBJ1
> ) UT
> GROUP BY UT.shop
>|||did the query I posted work'
"kym" wrote:
> Thank you for quickly response.
> I need of next case too.
> SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
> FROM
> (
> (
> SELECT SUBJ0.shop AS shop, 0 AS dcrate, sum(SUBJ0.sellcnt) AS sellcnt
> FROM
> (
> SELECT
> [shop]shop,
> [brnd]brnd,
> [item]item,
> [seq]seq,
> [color]color,
> [size]size,
> [sellqty]sellqty,
> [sellamt]sellamt,
> [sellcnt]sellcnt,
> [sellterm]sellterm,
> [tempseq]tempseq
> FROM
> "HULUCKS.dbo.x_sellinfo" T1
> ) SUBJ0
> GROUP BY SUBJ0.shop
> )
> UNION ALL
> (
> SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
> FROM
> ( SELECT
> T2.[shop_type]shop_type,
> T1.[shop]shop,
> T1.[sellqty]sellqty,
> T1.[dcrate]dcrate,
> T1.[sellamt]sellamt
> FROM
> "HULUCKS.dbo.x_sellinfo" T1
> INNER JOIN "HULUCKS.dbo.x_shop" T2 ON T2.[shop]shop = T1.[shop]shop
> ) SUBJ1
> GROUP BY SUBJ1.shop
> )
> ) UT
> GROUP BY UT.shop
> In this case also raise same error.
> But no error at the next time.
> SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
> FROM
> (
> (
> SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
> FROM
> ( SELECT
> T2.[shop_type]shop_type,
> T1.[shop]shop,
> T1.[sellqty]sellqty,
> T1.[dcrate]dcrate,
> T1.[sellamt]sellamt
> FROM
> "HULUCKS.dbo.x_sellinfo" T1
> INNER JOIN "HULUCKS.dbo.x_shop" T2 ON T2.[shop]shop = T1.[shop]shop
> ) SUBJ1
> GROUP BY SUBJ1.shop
> )
> ) UT
> GROUP BY UT.shop
> Thank's!!
> "Omnibuzz" wrote:
>|||Don't work the query you posted.
Same error occur.
"Omnibuzz" wrote:
> did the query I posted work'
> "kym" wrote:
>|||I think that this problem start from bug of "MS SQLServer 2000".
To my thinking, it can solve by patch SP of MS SQLServer 2000.
Is this right?
"Omnibuzz" wrote:
> did the query I posted work'
> "kym" wrote:
>|||I have never heard of this error "Invalid subject name" in SQL Server.
The only place I have heard is in SSL Connections.
Is that the error that it gives?
It seems to be correct syntactically and the table seems to exist.
Maybe one of the MVPs might have a better insight on the service packs|||Kym,
[HULUCKS.dbo.x_shop] is a very strange table name. My guess
is that you have a table named x_shop in a database called HULUCKS,
but that you don't have a table named [HULUCKS.dbo.x_shop].
This would mean, however, that the query omnibuzz asked you to
run does not work. Are you sure you ran it exactly, including the
" characters?
Steve Kass
Drew University
Try removing the " characters around the table name.
kym wrote:

>I maked a following query and excuted it.
>SELECT UT.shop, sum(UT.dcrate) , sum(UT.sellcnt)
>FROM
>(
> (
> SELECT SUBJ0.shop AS shop, 0 AS dcrate, sum(SUBJ0.sellcnt) AS sellcnt
> FROM
> (
> SELECT
> [shop]shop,
> [brnd]brnd,
> [item]item,
> [seq]seq,
> [color]color,
> [size]size,
> [sellqty]sellqty,
> [sellamt]sellamt,
> [sellcnt]sellcnt,
> [sellterm]sellterm,
> [tempseq]tempseq
> FROM
> "HULUCKS.dbo.x_sellinfo" T1
> ) SUBJ0
> GROUP BY SUBJ0.shop
> )
> UNION ALL
> (
> SELECT SUBJ1.shop AS shop, sum(SUBJ1.dcrate) AS dcrate, 0 AS sellcnt
> FROM
> ( SELECT
> [shop]shop,
> [shopnm]shopnm,
> [shop_type]shop_type,
> [dcrate]dcrate,
> [posgb]posgb,
> [manachul]manachul
> FROM
> "HULUCKS.dbo.x_shop" T1
> ) SUBJ1
> GROUP BY SUBJ1.shop
> )
> ) UT
>GROUP BY UT.shop
>But occur following error.
>"Invalid subject name 'HULUCKS.dbo.x_shop'"
>Why occur this error?
>Help me......
>