By etrupja


2016-09-29 12:10:24 8 Comments

I have an written a recursive SQL query which returns some int values. The SQL query looks like below:

;WITH GroupHIERARCHY(ID)  
    AS  (  SELECT ID   
    FROM tFirstTable te  
    WHERE te.LevelID <> 0   
    AND GroupID =-1
    UNION ALL  
    SELECT t.ElementID  
    FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
    WHERE t.TypeID=tSecondTable.TypeID  
    AND GroupHIERARCHY.ID= t.GroupID)

    SELECT ID FROM GroupHIERARCHY

This would return some integer values. (Works fine) What I want to do is that I want to write a query like below:

Select * from tExampleTable 
WHERE FirstParameter IN (IntegerValuesHere) OR SecondParameter IN (IntegerValuesHere)

Where, IntegerValuesHere are the values I get from the recursive query.

The query now would look like:

Select * FROM tExampleTable 
    WHERE FirstParameter IN (
        ;WITH GroupHIERARCHY(ID)  
        AS  (  SELECT ID   
        FROM tFirstTable te  
        WHERE te.LevelID <> 0   
        AND GroupID =-1
        UNION ALL  
        SELECT t.ElementID  
        FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
        WHERE t.TypeID=tSecondTable.TypeID  
        AND GroupHIERARCHY.ID= t.GroupID)

        SELECT ID FROM GroupHIERARCHY
        ) 

    OR SecondParameter IN (
        ;WITH GroupHIERARCHY(ID)  
        AS  (  SELECT ID   
        FROM tFirstTable te  
        WHERE te.LevelID <> 0   
        AND GroupID =-1
        UNION ALL  
        SELECT t.ElementID  
        FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
        WHERE t.TypeID=tSecondTable.TypeID  
        AND GroupHIERARCHY.ID= t.GroupID)

        SELECT ID FROM GroupHIERARCHY
    )

But, I get an error which says that:

Incorrect syntax near ';' and Incorrect syntax near ')'

First, for the ; in front of WITH. Second, for the ) before OR. What am I missing?

2 comments

@Deepak Sharma 2016-09-29 12:24:42

use data from different cte like this.

;WITH GroupHIERARCHY(ID)  
AS  (  

    SELECT ID   
    FROM tFirstTable te  
    WHERE te.LevelID <> 0   
    AND GroupID =-1
    UNION ALL  
    SELECT t.ElementID  
    FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
    WHERE t.TypeID=tSecondTable.TypeID  
    AND GroupHIERARCHY.ID= t.GroupID
),
GroupHIERARCHY1(ID)  
AS  (  

    SELECT ID   
    FROM tFirstTable te  
    WHERE te.LevelID <> 0   
    AND GroupID =-1
    UNION ALL  
    SELECT t.ElementID  
    FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
    WHERE t.TypeID=tSecondTable.TypeID  
    AND GroupHIERARCHY.ID= t.GroupID

)


SELECT ID FROM GroupHIERARCHY
Select * FROM tExampleTable 
WHERE FirstParameter IN (
select Id from GroupHIERARCHY
) 

OR SecondParameter IN (


SELECT ID FROM GroupHIERARCHY1
)

@Giorgos Betsos 2016-09-29 12:13:39

You cannot nest a CTE like you are trying to do. You can use it like this:

;WITH GroupHIERARCHY(ID)  
    AS  (  SELECT ID   
    FROM tFirstTable te  
    WHERE te.LevelID <> 0   
    AND GroupID =-1
    UNION ALL  
    SELECT t.ElementID  
    FROM tFirstTable AS t, tSecondTable,GroupHIERARCHY  
    WHERE t.TypeID=tSecondTable.TypeID  
    AND GroupHIERARCHY.ID= t.GroupID)    
Select * 
from tExampleTable 
WHERE FirstParameter IN (SELECT ID FROM GroupHIERARCHY) OR 
      SecondParameter IN (SELECT ID FROM GroupHIERARCHY)

@etrupja 2016-09-29 12:14:57

This CTE is inside another CTE, what should I do in this case?

@Giorgos Betsos 2016-09-29 12:17:36

@eg16 You can simply nest them placing the one after the other, like: ;With FirstCTE AS (), SecondCTE AS ( SELECT * FROM FirstCTE JOIN .. ) ...

Related Questions

Sponsored Content

46 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

37 Answered Questions

27 Answered Questions

17 Answered Questions

[SOLVED] Select columns from result set of stored procedure

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 550679 View
  • 233 Score
  • 2 Answer
  • Tags:   mysql sql

6 Answered Questions

[SOLVED] How to create a table from select query result in SQL Server 2008

Sponsored Content