Can I create a nested SQL Statement?

I am using ASP on an Intranet webpage.
I have two statements that work. Both statements use the same record source.
See Code1 and Code2
Can I combine these two into one Statement?
Thanks in advance for any help.
Rick
Code1
SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 >#12/31/10#
GROUP BY Account

Code2
SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 <=#12/31/10#
GROUP BY Account

Open in new window

ES-ComponentsAsked:
Who is Participating?
 
Wayne MichaelConnect With a Mentor Senior Software DeveloperCommented:
yes

select (
SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 <=#12/31/10#) , (SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 >#12/31/10#)

GROUP BY Account

I think...
0
 
hieloCommented:
SELECT Account, SUM(Expr2) as SumofOrders
      FROM TotalOpenOrdersDetail_CP
      GROUP BY Account

given your where conditions, they should ultimately give you all the data, so use a single select with no whereclause
0
 
ES-ComponentsAuthor Commented:
I get error message....
Extra ) in query expression.
Any ideas???
Rick
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ES-ComponentsAuthor Commented:
I need the where clause.
I need to select the totals orders where ship date <= 12/31/10 AND where the total orders
ship dates are >12/31/10.
I have verified the detail data and there are two distinct totals
Rick
0
 
hieloCommented:
Code 1:
WHERE Expr1 >#12/31/10#

Code 2:
WHERE Expr1 <=#12/31/10#

Think of a date, ANY date. Whatever you are thinking of, it is either  >#12/31/10# OR 1 <=#12/31/10#.

In other words, any date you can think of will match the above criteria.  That is what you are doing when you "Combine" those queries. So to "Combine" those queries all you need to do is get rid of the where clause.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 >#12/31/10#
GROUP BY Account
union all
SELECT Account, SUM(Expr2) as SumofOrders
FROM TotalOpenOrdersDetail_CP
WHERE Expr1 <=#12/31/10#
GROUP BY Account
0
 
Wayne MichaelSenior Software DeveloperCommented:

might be easier to use a temp table,  dump both sets into the table and query the table for the results.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.