How to clone this query s result below itself?

Question

Post reply

How to clone this query s result below itself?

jbeclapez

SSC-Addicted

Points: 402

November 21, 2021 at 4:47 pm

#3955338

Hello,

I have an SQL query that is working - see below.

I would like to have this query cloned below itself using probably a UNION ALL.

so if I assume that the query is QueryA and I would like to clone it as QueryB, I would like to do sometihng like :

Select * from QueryA

Union all

Select * from QueryB

Here is the normal query.

WITH hierar
     (
          chid,
          chname,
          generation,
          parentid
     )
     AS
     (
            SELECT chid,
                   dtpID,
                   0,
                   parentid
            FROM   dbo.rl AS firtgeneration
            WHERE  firtgeneration.parentid = 25165887
            AND    firtgeneration.dimid = 24
            AND    firtgeneration.dtpID = 0
            UNION ALL
            SELECT     nextgeneration.chid,
                       nextgeneration.dtpID,
                       parent.generation + 1,
                       parent.chid
            FROM       dbo.rl AS nextgeneration
            INNER JOIN hierar        AS parent
            ON         nextgeneration.parentid = parent.chid
     )


      SELECT Cast('DQUOTE'+Member4Parent.NAME+'DQUOTE' AS VARCHAR(20)) AS Field1 ,
           Cast('DQUOTE'+Member4Child.NAME+'DQUOTE' AS  VARCHAR(20)) AS Field2 ,
           Cast('' AS VARCHAR(20)) AS Field3


FROM       hierar
INNER JOIN dbo.member Member4Parent
ON         hierar.parentid = Member4Parent.mbid
INNER JOIN dbo.member Member4Child
ON         hierar.chid = Member4Child.mbid
WHERE      Member4Parent.dtpID = 0
AND        Member4Child.dtpID = 0
AND        Member4Parent.dimid = 24
AND        Member4Child.dimid = 24

My SQL skills are not advanced enough to do that to provide such an advance syntax. I know that for some of you it might be simple. Please could you provide me the ending result.

Thanks for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63198 More actions · Answer 1

You would have to modify the bottom part of the query...

SELECT Field1, Field2, Field3
FROM
(SELECT Cast('DQUOTE'+Member4Parent.NAME+'DQUOTE' AS VARCHAR(20)) AS Field1 ,
           Cast('DQUOTE'+Member4Child.NAME+'DQUOTE' AS  VARCHAR(20)) AS Field2 ,
           Cast('' AS VARCHAR(20)) AS Field3
           1 AS sortOrder
FROM       hierar
INNER JOIN dbo.member Member4Parent
ON         hierar.parentid = Member4Parent.mbid
INNER JOIN dbo.member Member4Child
ON         hierar.chid = Member4Child.mbid
WHERE      Member4Parent.dtpID = 0
AND        Member4Child.dtpID = 0
AND        Member4Parent.dimid = 24
AND        Member4Child.dimid = 24
UNION ALL
SELECT Cast('DQUOTE'+Member4Parent.NAME+'DQUOTE' AS VARCHAR(20)) AS Field1 ,
           Cast('DQUOTE'+Member4Child.NAME+'DQUOTE' AS  VARCHAR(20)) AS Field2 ,
           Cast('' AS VARCHAR(20)) AS Field3
           2 AS sortOrder
FROM       hierar
INNER JOIN dbo.member Member4Parent
ON         hierar.parentid = Member4Parent.mbid
INNER JOIN dbo.member Member4Child
ON         hierar.chid = Member4Child.mbid
WHERE      Member4Parent.dtpID = 0
AND        Member4Child.dtpID = 0
AND        Member4Parent.dimid = 24
AND        Member4Child.dimid = 24) un
ORDER BY un.Field1, un.Field2, un.Field3, un.SortOrder

jbeclapez SSC-Addicted Points: 402 More actions · Answer 2

Thanks for your reply!

I do not really understand your result as it seems totally different, with new fields and all. I know how to do a normal basic UNION but the problem with my query was the WITH statement at the begining... it confuses me.

Could you please start with my query, as at least i could directly kind of reuse it.

Thanks again!