Solved

Errors in my subquery

Posted on 2013-06-18
13
171 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
  • 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
 

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 48

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

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 48

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 48

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 48

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SP to delete duplicates 15 55
Access Join Query 12 48
MS SQL export CSV & schedule It 9 42
Updating ms sql with special characters 8 21
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now