CODEWITHSUNDEEP
sqlsql-serverwith-clause
ranger
ranger

Reputation: 35

SQL Need to use OPTION clause inside WITH clause

I need to write a query where result from one SQL query is reused to get the another result. I need to return both as combined result in output. Result from tableA need to be reused inside tableb. Both tablea and tableb should be returned together. I don't want to use temp table here.

I need OPTION (FORCE ORDER, LOOP JOIN) to be applied to both tableA separately and tableB separately. But inside With Clause OPTION (FORCE ORDER, LOOP JOIN) cannot be added. Adding it outside with while selecting will not apply OPTION (FORCE ORDER, LOOP JOIN) as excepted, as I need the Option clause to be applied separately while table a and table b are queried.

WITH TableA AS (
    SELECT A.ID, A.Value, B.Value AS BValue
    FROM SomeTableA A
    CROSS APPLY (
        SELECT TOP 1 B.Value
        FROM SomeTableB B
        WHERE B.ID = A.ID
        ORDER BY B.ID DESC
    ) B
    OPTION (FORCE ORDER, LOOP JOIN)
), 
TableB AS (
    SELECT TOP (10) B.ID, B.Value, A.Value AS AValue
    FROM SomeTableB B
    CROSS APPLY (
        SELECT TOP 1 A.Value
        FROM SomeTableA A
        WHERE A.ID = B.ID
        ORDER BY A.ID DESC
    ) A
    WHERE B.ID < (SELECT MAX(ID) FROM TableA)
    ORDER BY B.ID DESC
    OPTION (FORCE ORDER, LOOP JOIN)
)
SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB;

This code is not working as OPTION (FORCE ORDER, LOOP JOIN) is added inside WITH, Is there any alternative option

  1. I don't want to use any temp table
  2. I want to reuse table A to query table B
  3. I want OPTION (FORCE ORDER, LOOP JOIN) to be applied separately while tableA and tableB are queried

Please add your suggestions

Upvotes: 0

Views: 113

Answers (0)

Related Questions