Making a view that shows the results of several different queries.

[Ryan]

Hello,

I am trying to create a view that shows the following

Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A

Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
..
..
..
Field3: Sum of of Amounts from Table H
Field4: Count of Amounts from Table H
..
..
..
Things are a bit more complex but this is the gist.

I am using SQL 2000.

I know how to do this pretty easily using a stored procedure. But how
can I do it in a view? A SQL server won't meet my needs in this
situation.

I tried OpenQuery ('myserver', 'exec myprocedure') but get the message
that my server is not configured for data access. I tried the system
stored procedure to set data access to true but nothing seemed to
happen.

I also tried Select * from (

Select Statement1, select statement2

)

but got syntax error at the comma between statement1 and statement2.

Trying to use select Statement1 as ABC to does not seem to work either.

Is there a way to do what I want without making 15 views and then a
final view that shows them all together? I know I could probably do
something by creating a ton of functions, but it really seems this
should not be that hard...

I am definitely open to any easy suggestions!

Thanks,
Ryan

[Hugo Kornelis]

On 21 Dec 2005 13:51:38 -0800, Ryan wrote:

Hello,

I am trying to create a view that shows the following

Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A

Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
.
.
.
Field3: Sum of of Amounts from Table H
Field4: Count of Amounts from Table H
.
.
.
Things are a bit more complex but this is the gist.

I am using SQL 2000.

I know how to do this pretty easily using a stored procedure. But how
can I do it in a view? A SQL server won't meet my needs in this
situation.

Hi Ryan,

This can be done in a single query. You can of course encapsulate that
in a view, stored procedure, or whatever.

To prevent double table-scanning, here's a query that will scan each
table only once:

SELECT Field1, Field2, Field3, ..., Field16
FROM (SELECT SUM(Amount) AS Field1, COUNT(Amount) AS Field2
FROM TableA) AS A
CROSS JOIN (SELECT SUM(Amount) AS Field3, COUNT(Amount) AS Field4
FROM TableB) AS B
.....
CROSS JOIN (SELECT SUM(Amount) AS Field15, COUNT(Amount) AS Field16
FROM TableH) AS H

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo
--

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

[Ryan]

I have tried what you suggested but it seems to snag with Syntax
errors. SQL accepts my Select .... AS A, B, C if I use it with the
Select * construct but it does not accept the CROSS JOIN STATEMENT

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2"....
)
AS A

CROSS JOIN

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2" and some other stuff....
)
AS B

Any ideas? I am not sure why the CROSS JOIN won't work actually. It
error out as soon as it hits the SELECT statement that follows CROSS
JOIN...

[Hugo Kornelis]

On 21 Dec 2005 16:58:18 -0800, Ryan wrote:

I have tried what you suggested but it seems to snag with Syntax
errors. SQL accepts my Select .... AS A, B, C if I use it with the
Select * construct but it does not accept the CROSS JOIN STATEMENT
Hi Ryan,

You didn't use the correct syntax. Check my previous replly and compare
it carefully with your query.

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2"....
)
AS A

CROSS JOIN
Remove the line below!Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2" and some other stuff....
)
AS B

Any ideas? I am not sure why the CROSS JOIN won't work actually. It
error out as soon as it hits the SELECT statement that follows CROSS
JOIN...

Best, Hugo
--

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