query roll up ? Possible. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query roll up ? Possible.

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-05-04 : 21:55:31

I have been shown thank to help from this forum how to take 2 tables and find the the records that are unique to the other like this:

SELECT t.linenr
FROM statlineNrTest t
LEFT OUTER JOIN StatLineNrProced p
ON t.linenr = p.linenr
WHERE p.linenr IS NULL

However statlineNrTest can be found as the result of a query :

When I try to insert the result set I get errors, may be its not possible or I dont know the syntax to get it done. ( I suspect my ignorance ) What do you think ?? Thanks

SELECT tt.linenr
FROM SELECT FROM ( insert the query below between the parenthesis ) as tt
LEFT OUTER JOIN StatLineNrProced p
ON t.linenr = p.linenr
WHERE p.linenr IS NULL

... the messy query that drives the inner query would be :
SELECT c.LineNumber, mystats.CUSTOMER, mystats.PROC_FKey, mystats.inTotal
FROM dbo.CustomerLineNumber AS c INNER JOIN
(SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
FROM (SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
FROM dbo.TestDynamic
WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')) AS t
GROUP BY CUSTOMER, PROC_FKey) AS mystats ON c.Customer = mystats.CUSTOMER

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 22:02:04

What is the error you are getting ?

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-05-04 : 23:00:55

2 errors : incorrect syntax near the keyword select
incorrect syntax near the keyword as

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 23:09:12

[code]
SELECT a.linenr
FROM
(
SELECT *
FROM
(
insert the query below between the parenthesis
) as tt
) as a
LEFT OUTER JOIN StatLineNrProced p
ON t.linenr = p.linenr
WHERE p.linenr IS NULL
[/code]
If you still have problem, post the complete query used rather than section by section

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-05-05 : 11:04:04

I inserted the query per the pattern you showed but I get :
the column prefix 't' does not match with a table name or alias name used in the query . I thought it might refer to this part of the query : LEFT OUTER JOIN StatLineNrProced p ON t.linenr = p.linenr WHERE p.linenr IS NULL
If I change that instance of t.linenr to a.linenr then I get 2 instances of invalid linenr. If I try tt.linenr at that location I get the same error I had with t.linenr

SELECT a.linenr FROM
( SELECT * FROM
(

SELECT c.LineNumber, mystats.CUSTOMER, mystats.PROC_FKey, mystats.inTotal
FROM dbo.CustomerLineNumber AS c INNER JOIN
(SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
FROM (SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
FROM dbo.TestDynamic
WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')) AS t
GROUP BY CUSTOMER, PROC_FKey) AS mystats ON c.Customer = mystats.CUSTOMER

)

as tt ) as a

LEFT OUTER JOIN StatLineNrProced p ON t.linenr = p.linenr WHERE p.linenr IS NULL

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 11:14:17

[code]
LEFT OUTER JOIN StatLineNrProced p
ON a.linenr = p.linenr
WHERE p.linenr IS NULL
[/code]
EDIT : removed t and change to a

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-05-05 : 11:27:22

Hi Khtan,

what does the strange red symbol indicate ?
LEFT OUTER JOIN StatLineNrProced p ON a.linenr = p.linenr WHERE p.linenr IS NULL

If that is what it means, that gave me the 2 instances of invalid linenr

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-05-05 : 23:52:16

I am embarrassed ! When I compared the small pattern against my actual query I noticed the field Name was different : Now it works great !!!

SELECT a.LineNumber
FROM (SELECT c.LineNumber, mystats.CUSTOMER, mystats.PROC_FKey, mystats.inTotal
FROM dbo.CustomerLineNumber AS c INNER JOIN
(SELECT CUSTOMER, PROC_FKey, COUNT(BoolCtr) AS inTotal
FROM (SELECT CSB_FKey, PROC_FKey, LEFT(CSB_FKey, 5) AS CUSTOMER, PassStatus, 1 AS BoolCtr
FROM dbo.TestDynamic
WHERE (PassStatus <> 'PASS') AND (TestTime >= '20060121') AND (TestTime < '20060221')) AS t
GROUP BY CUSTOMER, PROC_FKey) AS mystats ON c.Customer = mystats.CUSTOMER) AS a LEFT OUTER JOIN
dbo.statLineNrProced AS p ON a.LineNumber = p.lineNr
WHERE (p.lineNr IS NULL)

Thanks for your patience with me :)

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources