?
Solved

Can I create a nested SQL Statement?

Posted on 2010-01-08
7
Medium Priority
?
218 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:ES-Components
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Accepted Solution

by:
wrmichael earned 2000 total points
ID: 26213005
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
 
LVL 82

Expert Comment

by:hielo
ID: 26213114
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
 

Author Comment

by:ES-Components
ID: 26213139
I get error message....
Extra ) in query expression.
Any ideas???
Rick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ES-Components
ID: 26213199
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
 
LVL 82

Expert Comment

by:hielo
ID: 26213202
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26213205
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
 
LVL 11

Expert Comment

by:wrmichael
ID: 26213218

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



0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question