SQL Select within Sub Query

Question

Hi My query is as follows, and contains a subquery within it:

ALTER PROCEDURE [dbo].[POBalance] @PONumber NVARCHAR(50) 
AS
BEGIN
    SELECT CASE 
            WHEN X.STATUS = 'False'
                THEN (  SELECT A.Description
                            ,C.qty AS POqty
                            ,B.Qty AS PDQty
                            ,CASE 
                                WHEN A.partialflag = 'false'
                                    THEN '0'
                                ELSE A.qty
                                END AS Balance
                            ,A.Unit
                            ,A.Unitprice
                            ,A.Partialflag
                        FROM tblPOdetails AS A
                        INNER JOIN tblPDdetails AS B ON A.id = B.id
                        INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                        WHERE A.PONo = @PONumber
                        )
            ELSE (  SELECT A.Description
                        ,C.qty AS POqty
                        ,B.Qty AS PDQty
                        ,C.qty AS Balance
                        ,A.Unit
                        ,A.Unitprice
                        ,A.Partialflag
                    FROM tblPOdetails AS A
                    INNER JOIN tblPDdetails AS B ON A.id = B.id
                    INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                    WHERE A.PONo = @PONumber)
            END
    FROM tblPOHeader AS X
    WHERE x.PONo = @PONumber
END

The error i get is :

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

What is wrong with my query..it seems my sub query is OK.

THanks in Advance

score 2 · Answer 1 · answered May 20 '15 at 15:25

You can not select table in case clausule. You can do it just like that (I didn't test it):

    SELECT A.Description
                                ,C.qty AS POqty
                                ,B.Qty AS PDQty
                                ,CASE 
                                    WHEN A.partialflag = 'false'
                                        THEN '0'
                                    ELSE A.qty
                                    END AS Balance
                                ,A.Unit
                                ,A.Unitprice
                                ,A.Partialflag
                            FROM tblPOdetails AS A
                            INNER JOIN tblPDdetails AS B ON A.id = B.id
                            INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                            INNER JOIN tblPOHeader X ON x.PONo = A.PONo
                            WHERE A.PONo = @PONumber and X.STATUS = 'False'

    UNION ALL

    SELECT A.Description
                            ,C.qty AS POqty
                            ,B.Qty AS PDQty
                            ,C.qty AS Balance
                            ,A.Unit
                            ,A.Unitprice
                            ,A.Partialflag
                            FROM tblPOdetails AS A
                            INNER JOIN tblPDdetails AS B ON A.id = B.id
                            INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                            INNER JOIN tblPOHeader X ON x.PONo = A.PONo
                            WHERE A.PONo = @PONumber and X.STATUS <> 'False'

But I think tah X.STATUS is bit - if yes you should do it just like that:

    SELECT A.Description
                                ,C.qty AS POqty
                                ,B.Qty AS PDQty
                                ,CASE 
                                    WHEN A.partialflag = 'false'
                                        THEN '0'
                                    ELSE A.qty
                                    END AS Balance
                                ,A.Unit
                                ,A.Unitprice
                                ,A.Partialflag
                            FROM tblPOdetails AS A
                            INNER JOIN tblPDdetails AS B ON A.id = B.id
                            INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                            INNER JOIN tblPOHeader X ON x.PONo = A.PONo
                            WHERE A.PONo = @PONumber and X.STATUS = 0

    UNION ALL

    SELECT A.Description
                            ,C.qty AS POqty
                            ,B.Qty AS PDQty
                            ,C.qty AS Balance
                            ,A.Unit
                            ,A.Unitprice
                            ,A.Partialflag
                            FROM tblPOdetails AS A
                            INNER JOIN tblPDdetails AS B ON A.id = B.id
                            INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                            INNER JOIN tblPOHeader X ON x.PONo = A.PONo
                            WHERE A.PONo = @PONumber and X.STATUS <> 0

Your answer will actually run both queries, instead of just the one that he needs data from. — Radu Gheorghiu, May 20 '15 at 15:30
I think I must run both quries becouse I do not now if select * from tblPOHeader where PONo = @PONumber return one or more than one row. If it returns one row only - you are right. — Arkadiusz, May 20 '15 at 15:34
Hi @RaduGheorghiu I Only need one of those in the case statement if Status (bit) = false then i will get the result if not then i will get the another result. — Daryl, May 20 '15 at 15:38
Hi @Arkadiusz the query of select * from tblPOHeader where PO=@PONumber will only return 1 row because that is primary — Daryl, May 20 '15 at 15:42
If it returns only one row you should check status on start and run only one query in the way that @RaduGheorghiu wrote before — Arkadiusz, May 20 '15 at 15:48

score 1 · Accepted Answer · answered May 20 '15 at 15:25

Are you actually looking for this?

ALTER PROCEDURE [dbo].[POBalance] @PONumber NVARCHAR(50)
AS
BEGIN
    DECLARE @Status NVARCHAR(MAX)

    SELECT @Status = X.STATUS
    FROM tblPOHeader AS X WHEREx.PONo = @PONumber

    IF @Status = 'False'
    BEGIN
        SELECT A.Description
            ,C.qty AS POqty
            ,B.Qty AS PDQty
            ,CASE 
                WHEN A.partialflag = 'false'
                    THEN '0'
                ELSE A.qty
                END AS Balance
            ,A.Unit
            ,A.Unitprice
            ,A.Partialflag
        FROM tblPOdetails AS A
        INNER JOIN tblPDdetails AS B ON A.id = B.id
        INNER JOIN tblpodetailshistory AS C ON A.id = C.id
        WHERE A.PONo = @PONumber
    END
    ELSE
    BEGIN
        SELECT A.Description
            ,C.qty AS POqty
            ,B.Qty AS PDQty
            ,C.qty AS Balance
            ,A.Unit
            ,A.Unitprice
            ,A.Partialflag
        FROM tblPOdetails AS A
        INNER JOIN tblPDdetails AS B ON A.id = B.id
        INNER JOIN tblpodetailshistory AS C ON A.id = C.id
        WHERE A.PONo = @PONumber
    END
END

This code will store and then check the value of @Status and based on if the value is False or not then it will return the result set of one of the queries.

What if "SELECT @Status = X.STATUS FROM tblPOHeader AS X WHEREx.PONo = @PONumber" return more than one row ..... — Max, May 20 '15 at 15:36

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

You cannot select multiple fields for one expression.Your alias makes it more visible.You select more than one value and give only one alias name!! Please refer this

Only One Expression in case

And this one too

Another one here

edited May 23 '17 at 11:58

Community

1
1

answered May 20 '15 at 15:18

Recursive

954
7
12

score 0 · Answer 4 · answered May 20 '15 at 15:51

When

select * from tblPOHeader where PONo = @PONumber

returns only one row you can do something like that

    declare @STATUS nvarchar(200) = ( select STATUS from tblPOHeader where PONo = @PONumber)
    if @STATUS = 'False'
    SELECT A.Description
                            ,C.qty AS POqty
                            ,B.Qty AS PDQty
                            ,CASE 
                                WHEN A.partialflag = 'false'
                                    THEN '0'
                                ELSE A.qty
                                END AS Balance
                            ,A.Unit
                            ,A.Unitprice
                            ,A.Partialflag
                        FROM tblPOdetails AS A
                        INNER JOIN tblPDdetails AS B ON A.id = B.id
                        INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                        WHERE A.PONo = @PONumber 

    ELSE
        SELECT A.Description
                        ,C.qty AS POqty
                        ,B.Qty AS PDQty
                        ,C.qty AS Balance
                        ,A.Unit
                        ,A.Unitprice
                        ,A.Partialflag
                        FROM tblPOdetails AS A
                        INNER JOIN tblPDdetails AS B ON A.id = B.id
                        INNER JOIN tblpodetailshistory AS C ON A.id = C.id
                        WHERE A.PONo = @PONumber

SQL Select within Sub Query

4 Answers4