How to clone this query s result below itself?

Question

How to clone this query s result below itself?

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

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

pietlinden SSC Guru Points: 63621 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: 405 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!

jbeclapez SSC-Addicted Points: 405 More actions · Answer 3

Or do I misunderstand... when you mean the bottom part of the query?

jbeclapez SSC-Addicted Points: 405 More actions · Answer 4

Thanks. I think i got it working! Thanks to your help!