0

How to merge multi statement into one select statement.

I Tried but getting error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

below i have mentioned all 4 statements.

Statement 1

SELECT * 
FROM   (SELECT ROW_NUMBER() OVER(
            ORDER BY 
                CASE WHEN ISNUMERIC(ProductCode) = 0 
                     THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) 
                ELSE ProductCode END
              , ProductCode
            ) AS Num1
          , ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName1
        FROM  BASE_Product 
        WHERE IsActive = 1
        ) BASE_Product 
WHERE  Num1 > 0 AND Num1 < (95 + 1)

Statement 2

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num2, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName2 `
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num2 > 95 AND Num2 < ((95*2) + 1)

Statement 3

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num3, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName3
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num3 > (95*2) AND Num3 < ((95*3) + 1)

Statement 4

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num4, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName4
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num4 > (95*3) AND Num4 < ((95*4) + 1)

1 Answer 1

5

I Tried

You should include what you tried in the question - any detail you can give helps us help you understand.

How to merge multi statement into one select statement.

You don't say what output you actually expect so I'm only guessing here. I suspect you want the rows from each of those four queries to be output in one resultset, in which case you need the UNION set operator, see https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql for details.

For a generic example:

SELECT <columns> FROM <tables_or_views>
UNION ALL
SELECT <columns> FROM <tables_or_views>
UNION ALL
-- and so on...
UNION ALL
SELECT <columns> FROM <tables_or_views>
-- note: order by applied at the end to all outputs, not in individual SELECTs
ORDER BY <columns>

If you use UNION instead of UNION ALL and the queries output any duplicate rows, those duplicates will be filtered and only one output. This is basically done the same way as DISTINCT which involves a sort operation (which may not match the sort you have applied via ORDER BY) that for results of any considerable size will involve a dump to tempdb, so unless you know duplicates may be present and want them filtered out it is more efficient to use UNION ALL instead of just plain UNION.

Also unless you need a specific ordering, avoid specifying an ORDER BY clause as that will likely result in dropping the results to tempdb too.

More specifically for the queries you included above I suspect that there is a simpler way (from the query planners PoV so likely more efficient) using single SELECT with appropriate GROUPing and/or window functions instead of several statements UNIONed. You should describe more fully what you are actually trying to get out of this code, and neaten up the included code so it is more readable as I've tried to do a bit with the first example, and someone might help you do that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.