Solved

SQL SUBQUERY SYNTAX ISSUE

Posted on 2012-12-23
20
1,037 Views
Last Modified: 2012-12-26
First off happy holidays to you all and I hope if anyone else is working this weekend like me everything is going smoothly. so here is this situation. I have two separate queries I am trying to merge into a sub-query however I can’t seem to get it to work properly and I know it is a simple syntax issue that someone with more skills can identify.

Here are the 2 queries:

QUERY 1

SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(AFACINIAMT) AS TOTAL_PLACED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)

Which returns:
YEAR      MONTH      TOTAL_PLACED
2012      8      3101542.03
2012      9      285786.43
2012      10      1224473.49
2012      11      327823.32
2012      12      315477.44

QUERY 2



SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'BAD%'
AND      ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)

WHICH RETURNS:
YEAR      MONTH      TOTAL_LOCATED
2012      8      2905070.72
2012      9      202220.81
2012      10      462872.94
2012      11      77451.27
2012      12      22331.84

Both return the values expected.  The problem I have is when I try to nest (sub-query) the 2nd query into the first with this query:


SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(AFACINIAMT) AS TOTAL_PLACED,
(SELECT SUM(AFACINIAMT)
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'BAD%'
AND      ARR.ARRELPHASE = 'REGULAR')AS TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)

I get the following which is incorrect:

YEAR      MONTH      TOTAL_PLACED      TOTAL_LOCATED
2012      8      3101542.03      3669947.58
2012      9      285786.43      3669947.58
2012      10      1224473.49      3669947.58
2012      11      327823.32      3669947.58
2012      12      315477.44      3669947.58

Please help if you can!.  Thanks and Happy Holidays
0
Comment
Question by:djkontroll
  • 9
  • 3
  • 3
  • +3
20 Comments
 
LVL 4

Expert Comment

by:jmdl1983
ID: 38717077
You should look at doing the UNION ALL command as it will allow the joining of two result sets.

http://www.w3schools.com/sql/sql_union.asp
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38717081
I would rather do a JOIN of the 2 subquerys...

SELECT q1.YEAR, q1.MONTH, q1.TOTAL_PLACED, q2.TOTAL_LOCATED
   FROM  ( <yourquery1 ) q1
   JOIN  ( <yourquery2) q2
      ON q1.YEAR = q2.YEAR
     AND q1.MONTH = q2.MONTH

Open in new window

0
 
LVL 4

Expert Comment

by:jmdl1983
ID: 38717097
A join is dependant on relationship between the two tables, whereas a UNION ALL allows completely distinct queries to be independent of each other.
0
 

Author Comment

by:djkontroll
ID: 38717100
Just because I forgot to mention I am running these queries from AQT on a Cache database via ODBC.   That said 'angelIII' would your solution still be suitable?
0
 

Author Comment

by:djkontroll
ID: 38717123
@jmdl1983 - I think you are definitley pointing me in the right direction.  I tried union when I first started working on this and then had someone point me towards subqueries.  I have just tried a few variations and remembered the individual queries with unions must contain the final count of columns so I tried this and no luck.  I'm sure I'm close... Thoughts?

SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION ALL
SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
         SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'BAD%'
AND      ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
0
 
LVL 4

Expert Comment

by:jmdl1983
ID: 38717128
What is the error you are receiving?
0
 

Author Comment

by:djkontroll
ID: 38717143
Error during Prepare
 37000(25)[Cache ODBC][State : 37000][Native Code 25]
[C:\Program Files (x86)\Advanced Query Tool v9\aqtv9.exe]
[SQLCODE: <-25>:<Input encountered after end of query>]
[Cache Error: <<SYNTAX>errdone+2^%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #25: Input (() encountered after end of query^SELECT YEAR ( araclstdte ) AS YEAR , MONTH ( araclstdte ) AS MONTH , SUM ( AFACINIAMT ) AS TOTAL_PLACED , :%qpar(1) as TOTAL_LOCATED FROM SQLUser . STFIELDAUD STF , SQLUser . ARRELATIONSHIP ARR , SQLUser . ARACCOUNT ARA
0
 

Author Comment

by:djkontroll
ID: 38717155
if I remove:
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)

I of course get the details I need but not the summary.  This is the bigger reason I pushed to subquerry  so I could apply the grouping one time.  If I try to apply in both "legs" (for lack of a better term) of the union query I get an error SQL ERROR #25: Input (UNION) encountered after end of query
0
 

Author Comment

by:djkontroll
ID: 38717167
I tried just Union instead of Union all and removed the Group/Sort and now much closer than I was however the numbers are now wrong?  See below:

SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION
SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
         SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND      STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'BAD%'
AND      ARR.ARRELPHASE = 'REGULAR'

RETURNS:

YEAR      MONTH      TOTAL_PLACED      TOTAL_LOCATED
2012      8      5255102.71      
2012      9      5255102.71      
2012      10      5255102.71      
2012      11      5255102.71      
2012      12      5255102.71      
2012      8      0                        3669947.58
2012      9      0                        3669947.58
2012      10      0                        3669947.58
2012      11      0                        3669947.58
2012      12      0                        3669947.58
0
 
LVL 9

Expert Comment

by:keyu
ID: 38717909
I think SQL join should definatly work for you....

please refer below link might helps you....

http://www.sqlnewsgroups.net/sqlserver/t11142-urgent-slow-join-across-sql-linked-server-intersystems-cache.aspx

if you still wants to follow unian all option i think you should try pivot table option....

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:djkontroll
ID: 38718442
@: keyu

The only issue with join like I was saying above is this is not a SQL database, I am using  ODBC to query an Intersystem's Cache DB.  That said I can't join queries as tables like you can with SQL server or Microsoft Access.  If I could use Access I would but one of the tables I am using in this query has 40 keys (That's the whole purpose of that table to be the center relationship for this DB) and that is beyond the maximum keys Acess allows per table.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38718860
Can you use CASE expressions in your SQL?
0
 

Author Comment

by:djkontroll
ID: 38721266
Yes  I can
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38721359
I think a standard CASE statement can make this much easier (and possibly more efficient as well):


SELECT  
         YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN AFACINIAMT ELSE 0 END) AS TOTAL_PLACED,
         SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN 0 ELSE AFACINIAMT END) AS TOTAL_LOCATED        
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND ((
            STF.STAUDFLDEXTNEW like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'NEWACCT'
     )
  OR
     (
            STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND      STF.STAUDFLDEXTOLD like 'BAD%'
AND      ARR.ARRELPHASE = 'REGULAR'
    ))
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
0
 

Author Comment

by:djkontroll
ID: 38721394
@ ScottPletcher:

Aren't you just the superstar!  Great job!  Thank you for the Xmas present and have a happy new year.

T
0
 
LVL 31

Expert Comment

by:awking00
ID: 38721460
I think the case statement should work with a little tweaking -
SELECT   YEAR(araclstdte) AS YEAR,
         MONTH(araclstdte) AS MONTH,
         SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%'
                  AND  STF.STAUDFLDEXTOLD = 'NEWACCT'
                  THEN AFACINIAMT ELSE 0
             END) AS TOTAL_PLACED,
         SUM(CASE WHEN STF.STAUDFLDEXTNEW NOT like 'BAD%'
                  AND  STF.STAUDFLDEXTOLD like 'BAD%'
                  AND  ARR.ARRELPHASE = 'REGULAR'
                  THEN AFACINIAMT ELSE 0
             END) AS TOTAL_LOCATED
FROM     SQLUser.STFIELDAUD STF,
         SQLUser.ARRELATIONSHIP ARR,
         SQLUser.ARACCOUNT ARA,
         SQLUser.AFACCOUNT AFA
WHERE    STF.STAUDKEY=ARR.ARRELID
AND      ARA.ARACID=ARR.ARRELACID
AND      AFA.AFACKEY=ARR.ARRELAFID
AND      ARR.ARRELCLTID = 'LATX1'
AND      STF.STAUDFLDNAME = 'ARRELSTATUSID'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte);
0
 
LVL 31

Expert Comment

by:awking00
ID: 38721471
Took too long testing and typing :-(
Out of curiosity, do the two queries ScottPletcher's and mine return the same results?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38721548
I can't test it either, since I don't have data, but I can tell you: yes, they do produce the same results, albeit that yours could execute more slowly, depending on table indexes and the sophistication of the SQL optimizer.

Look closely at the first condition in each set of tests: they are mutually exclusive:

STF.STAUDFLDEXTNEW like 'BAD%'
--vs.--
STF.STAUDFLDEXTNEW NOT like 'BAD%'

Thus, you don't need to do additional checks to distinguish between them when adding the amounts.
0
 

Author Comment

by:djkontroll
ID: 38721559
Scott's Runs in 5.09 sec

AWKINGS runs in 6.08
0
 
LVL 31

Expert Comment

by:awking00
ID: 38721651
>>STF.STAUDFLDEXTNEW like 'BAD%'
--vs.--
STF.STAUDFLDEXTNEW NOT like 'BAD%'<<
ScottPletcher, missed that obviously off-setting condition. Thanks for pointing that out.
djkontroll, Thanks for running my query. At least it's good to know it worked.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now