Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

1.00/5 (1 vote)

See more:

i am getting error ,mentioned in question

SQL

With cte1 As
(
    SELECT
        IsNull(l.I_Amt - Sum(R.Cheque_Amount), l.I_Amt) as Previous_Balance, 
        l.Im_ID as ID
    FROM
        tbl_IB_Cus_Libility l
        LEFT JOIN tbl_Received R ON R.LI_ID = l.LI_ID
    WHERE
        l.Im_ID = 14 
    AND 
        (l.I_Status = 1 OR l.I_Status Is Null) 
    AND
        L.L_Delid Is Null
    GROUP BY
        l.Im_ID,
        l.I_Amt
),
cte As
(
    SELECT 
        (
            SELECT
                IsNull(l.i_amt-Sum(r.cheque_amount), l.i_amt), 
                l.im_id 
            FROM
                tbl_ib_cus_libility l 
                LEFT JOIN tbl_received R ON r.li_id = l.li_id 
            WHERE
                l.im_id=14 
            AND
                (l.i_status = 1 OR l.i_status IS NULL) 
            AND
                l.l_delid IS NULL 
            AND
                l.li_id < 
                ( 
                    SELECT Max(sub.li_id) 
                    FROM tbl_ib_cus_libility AS sub
                ) 
          GROUP BY
              l.im_id,
              l.i_amt 
        ) as Balance,
        Previous_Balance,
        ID
    FROM
        cte1
)
SELECT
    Sum(Previous_Balance) as GrandTotal,
    (Balance) as Running_Total,
    ID
FROM
    cte
GROUP BY
    Balance,
    id

What I have tried:

I tried a lot to resolve the issue but not getting done, that why I am asking here for solution.

Posted 18-Jul-20 0:16am

akhter86

Updated 19-Jul-20 23:44pm

Richard Deeming

v2

Add a Solution

Comments

Sandeep Mewara 19-Jul-20 6:49am

It's little difficult to read the query and what you are trying to achieve. What is clear though is the error you get. Have a look at this explaining error and solve for it: http://www.sql-server-helper.com/error-messages/msg-116.aspx

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-07-19T23:44:00

Quote:

SQL

SELECT 
    (
        SELECT
            IsNull(l.i_amt-Sum(r.cheque_amount), l.i_amt), 
            l.im_id 
        FROM
            tbl_ib_cus_libility l 
            LEFT JOIN tbl_received R ON r.li_id = l.li_id 
        WHERE
            l.im_id=14 
        AND
            (l.i_status = 1 OR l.i_status IS NULL) 
        AND
            l.l_delid IS NULL 
        AND
            l.li_id < 
            ( 
                SELECT Max(sub.li_id) 
                FROM tbl_ib_cus_libility AS sub
            ) 
      GROUP BY
          l.im_id,
          l.i_amt 
    ) as Balance,

The error is caused by this line. You are selecting two columns and trying to return them in a single column.

The GROUP BY clause also suggests that you're expecting this to return multiple rows. That won't work - you can only return a single row containing a single column.

It's not clear exactly what you're trying to achieve here.