Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL subquery

Posted on 2012-08-16
1
Medium Priority
?
452 Views
Last Modified: 2012-08-30
The following query gives me what I want - invoices which have a non-zero balance as of a certain date.

SELECT [EPCONO]
      ,[EPDIVI]
      ,[EPSPYN]
      ,[EPSUNO]
      ,[EPSINO]
      ,[EPINYR]
      ,sum([EPCUAM]) as Balance

  FROM [MVXJDTA].[FPLEDGJ1]
 
WHERE EPACDT <201200816
 
GROUP BY EPCONO, EPDIVI, EPSPYN, EPSUNO, EPSINO, EPINYR

HAVING (sum([EPCUAM]))<>0

When I include this as a subquery in a query in order to get the detail records making up these sums, I get all records, not just those associated with the selected non-zero balance invoices.
0
Comment
Question by:gofasteddie
1 Comment
 
LVL 18

Accepted Solution

by:
Cluskitt earned 1050 total points
ID: 38300997
Not knowing what your keys are, I'll have to assume something like:

SELECT *
FROM [MVXJDTA].[FPLEDGJ1]
INNER JOIN (SELECT [EPCONO] cono
      ,[EPDIVI] divi
      ,[EPSPYN] spyn
      ,[EPSUNO] suno
      ,[EPSINO] sino
      ,[EPINYR] inyr
      ,sum([EPCUAM]) as Balance
  FROM [MVXJDTA].[FPLEDGJ1]
WHERE EPACDT <201200816
GROUP BY EPCONO, EPDIVI, EPSPYN, EPSUNO, EPSINO, EPINYR
HAVING (sum([EPCUAM]))<>0) t
ON [EPCONO]=cono
      AND [EPDIVI]=divi
      AND [EPSPYN]=spyn
      AND [EPSUNO]=suno
      AND [EPSINO]=sino
      AND [EPINYR]=inyr
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

580 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