?
Solved

Errors in my subquery

Posted on 2013-06-18
13
Medium Priority
?
180 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
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 

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 2000 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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