asked on

Errors in my subquery

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'

David Kroll

(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.

Metalteck

ASKER

In the subquery, can I make it a left or right outer join?
If so, how would I do that?

David Kroll

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,

Metalteck

ASKER

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,

David Kroll

(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.

PortletPaul

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.

PortletPaul

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

PortletPaul

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

Nguyen Doan Tien

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Metalteck

ASKER

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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial