Solved

Errors in my subquery

Posted on 2013-06-18
13
177 Views
Last Modified: 2013-06-28
I'm trying to create a subquery, but I keep on getting the error " Only one expression can be specified in the select list when the subquery is not introduced with EXISITS."

Any clues as to what I'm doing wrong?

SELECT
       aux.MATRIX_CAT,
       aux.MX_VALUE,
       gla.COMPANY,
       gla.ACCT_UNIT,
       gla.ACCOUNT,
       gla.SUB_ACCOUNT,
       gla.FISCAL_YEAR,
       
       (Select fbd.DB_AMOUNT_04,
               fbd.CR_AMOUNT_04
        from FBDETAIL fbd
        WHERE gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY) as BUDGET,
       
       gla.DB_AMOUNT_01,
       gla.CR_AMOUNT_01,  
       gla.DB_AMOUNT_02,
       gla.CR_AMOUNT_02,
       gla.DB_AMOUNT_03,
       gla.CR_AMOUNT_03,
       gla.DB_AMOUNT_04,
       gla.CR_AMOUNT_04,
       gla.DB_AMOUNT_05,
       gla.CR_AMOUNT_05,
       gla.DB_AMOUNT_06,
       gla.CR_AMOUNT_06,
       gla.DB_AMOUNT_07,
       gla.CR_AMOUNT_07,
       gla.DB_AMOUNT_08,
       gla.CR_AMOUNT_08,
       gla.DB_AMOUNT_09,
       gla.CR_AMOUNT_09,
       gla.CR_AMOUNT_10,
       gla.DB_AMOUNT_10,
       gla.DB_AMOUNT_11,
       gla.CR_AMOUNT_11,
       gla.DB_AMOUNT_12,
       gla.CR_AMOUNT_12

FROM GLAMOUNTS gla
    JOIN GLNAMES gln
      ON gla.COMPANY = gln.COMPANY
     AND gla.ACCT_UNIT = gln.ACCT_UNIT

    JOIN AUMXVALUE aux
      ON gln.OBJ_ID = aux.OBJ_ID
     

WHERE gla.FISCAL_YEAR in (year(getdate()),year(getdate())-1)
AND   gla.ACCT_UNIT = '03200'
AND   aux.MX_VALUE = 'ANES'
AND   aux.MATRIX_CAT ='BUSINESSTYPE'
0
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 39257435
(Select fbd.DB_AMOUNT_04,
               fbd.CR_AMOUNT_04
        from FBDETAIL fbd
        WHERE gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY) as BUDGET,

You can't select two fields in a subquery.  You'll have to split those into two.
0
 

Author Comment

by:metalteck
ID: 39257470
In the subquery, can I make it a left or right outer join?
If so, how would I do that?
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39257488
I'm not sure exactly what you mean.  Do you mean use joins to retrieve those fields instead of using a subquery? Seems it would be easier to just do:

(Select fbd.DB_AMOUNT_04
        from FBDETAIL fbd
        WHERE gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY) as BUDGETDB,
(Select  fbd.CR_AMOUNT_04
        from FBDETAIL fbd
        WHERE gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY) as BUDGETCR,
0
Stressed Out?

Watch some penguins on the livecam!

 

Author Comment

by:metalteck
ID: 39257517
What I'm trying to do is get a specific account value from FBDETAIL even though there isn't a value in the gla table. I'm ok with all of the fields being blank that pertain to the gla table, but as long as the FBDETAIL value displays.

I got this code working, but commented out the join.

Is there a way I can do this?    

  (Select fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04 as Month4
        from FBDETAIL fbd
 --     right outer join GLAMOUNTS gla
        where gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY
       and fbd.FISCAL_YEAR = gla.FISCAL_YEAR) as BUDGET,
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39257574
(Select fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04
        from FBDETAIL fbd
        where gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY
       and fbd.FISCAL_YEAR = gla.FISCAL_YEAR) as BUDGET,

GLAMOUNTS is the main table in your query.  If the record doesn't exist there, there won't be a record to use that subquery on anyway.  If you're adding DB and CR, then the above should work.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258191
You CANNOT use 2 fields in a "correlated subquery" within the select clause, such a subquery can ONLY return A single value (i.e. not only must this be one column, it must return one row each time it is used!)

You could use a join within the from clause instead of the current "correlated subquery" you are now using within the select clause.

A LEFT JOIN is the closest equivalent of what you are doing now, BUT, introducing a join might produce more rows - this I cannot predict as I don't know your data model.
SELECT
      aux.MATRIX_CAT
    , aux.MX_VALUE
    , gla.COMPANY
    , gla.ACCT_UNIT
    , gla.ACCOUNT
    , gla.SUB_ACCOUNT
    , gla.FISCAL_YEAR
    
    , fbd.DB_AMOUNT_04 AS BUDGET_debit    
    , fbd.CR_AMOUNT_04 AS BUDGET_credit    
        
    , gla.DB_AMOUNT_01
    , gla.CR_AMOUNT_01
    , gla.DB_AMOUNT_02
    , gla.CR_AMOUNT_02
    , gla.DB_AMOUNT_03
    , gla.CR_AMOUNT_03
    , gla.DB_AMOUNT_04
    , gla.CR_AMOUNT_04
    , gla.DB_AMOUNT_05
    , gla.CR_AMOUNT_05
    , gla.DB_AMOUNT_06
    , gla.CR_AMOUNT_06
    , gla.DB_AMOUNT_07
    , gla.CR_AMOUNT_07
    , gla.DB_AMOUNT_08
    , gla.CR_AMOUNT_08
    , gla.DB_AMOUNT_09
    , gla.CR_AMOUNT_09
    , gla.CR_AMOUNT_10
    , gla.DB_AMOUNT_10
    , gla.DB_AMOUNT_11
    , gla.CR_AMOUNT_11
    , gla.DB_AMOUNT_12
    , gla.CR_AMOUNT_12
FROM GLAMOUNTS gla
INNER JOIN GLNAMES gln ON gla.COMPANY = gln.COMPANY
    AND gla.ACCT_UNIT = gln.ACCT_UNIT
INNER JOIN AUMXVALUE aux ON gln.OBJ_ID = aux.OBJ_ID

LEFT JOIN FBDETAIL fdb  ON gla.ACCOUNT = fbd.ACCOUNT
            AND gla.ACCT_UNIT = fbd.ACCT_UNIT
            AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
            AND gla.COMPANY = fbd.COMPANY

WHERE gla.FISCAL_YEAR IN ( year(getdate()) , year(getdate()) - 1  )
    AND gla.ACCT_UNIT = '03200'
    AND aux.MX_VALUE = 'ANES'
    AND aux.MATRIX_CAT = 'BUSINESSTYPE'

Open in new window

As a general principle, using "correlated subqueries" within the select clause is less efficient than combining the wanted data through the from clause.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258200
>>What I'm trying to do is get a specific account value from FBDETAIL even though there isn't a value in the gla table. I'm ok with all of the fields being blank that pertain to the gla table, but as long as the FBDETAIL value displays.
oops - this I didn't see before.

If this is true, then you need to "flip over" the table relationships, like so
FROM FBDETAIL
LEFT JOIN GLAMOUNTS ...

What you are doing now WILL NOT meet this: "as long as the FBDETAIL value displays"
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258220
sorry - there are further issues to address, it's not just flipping the join.

IF some information can exist in FBDETAIL that has no corresponding information in GLAMOUNTS then you also need to re-consider your where clause too. Right now your where clause would not permit any non-matching FBDETAIL records to be displayed (something I should have considered earlier I have to admit).

This is a possible approach, the join conditions need review
SELECT
      aux.MATRIX_CAT
    , aux.MX_VALUE
    , fdb.COMPANY
    , fdb.ACCT_UNIT
    , fdb.ACCOUNT
    , fdb.SUB_ACCOUNT
    , gla.FISCAL_YEAR
    
    , fbd.DB_AMOUNT_04 AS BUDGET_debit
    , fbd.CR_AMOUNT_04 AS BUDGET_credit

    , fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04 as Month4
        
    , gla.DB_AMOUNT_01, gla.CR_AMOUNT_01, gla.DB_AMOUNT_02, gla.CR_AMOUNT_02
    , gla.DB_AMOUNT_03, gla.CR_AMOUNT_03, gla.DB_AMOUNT_04, gla.CR_AMOUNT_04
    , gla.DB_AMOUNT_05, gla.CR_AMOUNT_05, gla.DB_AMOUNT_06, gla.CR_AMOUNT_06
    , gla.DB_AMOUNT_07, gla.CR_AMOUNT_07, gla.DB_AMOUNT_08, gla.CR_AMOUNT_08
    , gla.DB_AMOUNT_09  gla.CR_AMOUNT_09, gla.CR_AMOUNT_10, gla.DB_AMOUNT_10
    , gla.DB_AMOUNT_11, gla.CR_AMOUNT_11, gla.DB_AMOUNT_12, gla.CR_AMOUNT_12
FROM FBDETAIL fdb
LEFT JOIN GLAMOUNTS gla  ON fdb.ACCOUNT = gla.ACCOUNT
                        AND fdb.ACCT_UNIT = gla.ACCT_UNIT
                        AND fdb.SUB_ACCOUNT = gla.SUB_ACCOUNT
                        AND fdb.COMPANY = gla.COMPANY
                        AND gla.FISCAL_YEAR IN ( year(getdate()) , year(getdate()) - 1  )
LEFT JOIN GLNAMES gln    ON fdb.COMPANY = gln.COMPANY
                        AND fdb.ACCT_UNIT = gln.ACCT_UNIT
                        AND aux.MX_VALUE = 'ANES'
                        AND aux.MATRIX_CAT = 'BUSINESSTYPE'
LEFT JOIN AUMXVALUE aux  ON gln.OBJ_ID = aux.OBJ_ID
WHERE fdb.ACCT_UNIT = '03200'

Open in new window

0
 
LVL 4

Expert Comment

by:Nguyen Doan Tien
ID: 39261484
Your problem is here:
(Select [b]fbd.DB_AMOUNT_04,
               fbd.CR_AMOUNT_04[/b]
        from FBDETAIL fbd
        WHERE gla.ACCOUNT = fbd.ACCOUNT
       AND gla.ACCT_UNIT = fbd.ACCT_UNIT
       AND gla.SUB_ACCOUNT = fbd.SUB_ACCOUNT
       AND gla.COMPANY = fbd.COMPANY) as BUDGET,

Open in new window


You cannot get two fields by using only one alias "BUDGET". That's why you need to sum: fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04 before using alias.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39261490
while the above is true, correcting just that subquery will not solve the overall problem:
get a specific account value from FBDETAIL even though there isn't a value in the gla table

solving that requires changing the from table and join structure
0
 

Author Comment

by:metalteck
ID: 39275783
Hey Guys,

First off I want to thank you for all of your comments. You have been a great help.
I've been doing some research saw that there is a master table and I need to perform left joins to this table to get all the information I want. Portlet Paul your insight really helped me as I was trying to solve this issue. I've attached my new code. I just have one last question, and if I have to publish this as a new question, I will.

I have 2 Fiscal Years in this new code: FBFISCAL_YR and GLFISCALYR.
I see that in the example below the value of 2012 matches to both fields.
When it comes to value of 2013 in GLFISCALYR, FBFISCAL_YR duplicates it as 2012 although there is no value for 2013 in the FBDETAIL table.
Is there a way I can make this null or 0 when the values don't match?
Monthly-Code-v2.txt
Monthly-Example.docx
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39276695
mmmm, yes - I think, something like this using coalesce (see ref 2. below)
COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null
, coalesce( fbd.FISCAL_YEAR, gla.FISCAL_YEAR, year(getdate()) ) as fisc_yr_from_3

or, swapping that order if gla.FISCAL_YEAR is of higher priority

, coalesce( gla.FISCAL_YEAR, fbd.FISCAL_YEAR, year(getdate()) ) as fisc_yr_from_3

I see in that screenshot that both columns can be NULL, not sure what you want to do in such cases, but in the above I have used the current calendar year the third parameter.

just realised I don't know which dbms you use, but here are 2 references
1. http://msdn.microsoft.com/en-us/library/ms190349.aspx
2. http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm

By the way, the query looks much better now, use of the GLMASTER looks much more appropriate. :)
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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