SQL SubQuery Error

Question

I would like to retrieve the data in the following sample.The following SQL will return two records.

SELECT -1 AS NUM
FROM TABLE
WHERE COMP_CODE = 'TEST'
  AND (DETL_REMK = 'Rest Day'
       OR SHFT_CODE = 'WK_PH')
  AND RSRV_DATE_1 IS NOT NULL
  AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
  AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID,
         RSRV_DATE_1

I would like to show the below.

ORG UNIT          EMPE_ID        FAM_NAME       TMS_TYPE     Qty
-----------------------------------------------------------------
''                 ''              ''            Used         -1
''                 ''              ''            Used         -1

So, I 'm trying to do like that the SQL statement. But I got the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

How to construct the right SQL. Please help me. Thanks in advance.

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE,
       ISNULL(CAST(
                  (SELECT -1 AS NUM
                   FROM TABLE
                   WHERE COMP_CODE = 'TEST'
                     AND (DETL_REMK = 'Rest Day'
                          OR SHFT_CODE = 'WK_PH')
                     AND RSRV_DATE_1 IS NOT NULL
                     AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
                     AND EMPE_ID = 'TEST'
                   GROUP BY EMPE_ID, RSRV_DATE_1)AS NVARCHAR(MAX)),0) QTY

Tanner · Accepted Answer · 2014-03-03 11:21:03Z

4

If you're just hardcoding the values outside the subquery, you don't need a subquery to return the 2 rows, just do this:

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 AS QTY
FROM TABLE
WHERE COMP_CODE = 'TEST'
  AND (DETL_REMK = 'Rest Day'
       OR SHFT_CODE = 'WK_PH')
  AND RSRV_DATE_1 IS NOT NULL
  AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
  AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID,
         RSRV_DATE_1

The issue here is you're not referencing any columns from your table, just using it to produce an amount of rows. Please share the full issue and code, as I'm sure there is more to this that what you're asking.

answered Mar 3, 2014 at 11:21

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StanislavL · Accepted Answer · 2014-03-03 11:20:44Z

1

You can try this one instead

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 as QTY
union all
SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 as QTY

answered Mar 3, 2014 at 11:20

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Comments

Vasan · Accepted Answer · 2014-03-03 11:25:30Z

0

You would need to add Top 1 to your subquery, from the error you are getting, looks like the subquery is returning more than 1 row:

SELECT Top 1 -1 AS NUM
FROM TABLE
WHERE COMP_CODE = 'TEST'
AND (DETL_REMK = 'Rest Day',
OR SHFT_CODE = 'WK_PH')
AND RSRV_DATE_1 IS NOT NULL
AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID, RSRV_DATE_1

answered Mar 3, 2014 at 11:25

Vasan

3751 silver badge12 bronze badges

Collectives™ on Stack Overflow

SQL SubQuery Error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related