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

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

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)

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...

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)