Solved

# MULTIPLE CTE PROBLEM

Posted on 2013-05-13
182 Views
Hi All,

I have below code :

WITH ATTENDDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

WITH OVERTIMEDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

SELECT * FROM ATTENDDATA

UNION ALL

SELECT * FROM OVERTIMEDATA

What should I do ?

Thank you
0
Question by:emi_sastra

LVL 7

Accepted Solution

Ross Turner earned 500 total points
What should I do ?

what are you trying to do ?

Using Multiple Selects:
http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

i noticed your syntax is wrong between the cte's
if you add a comma and drop the with on the second cte it should graft.

Also it might worth adding a marker so a least on the end result you can see what data has come from which table.

you could do this on your select e.g

SELECT *,'ATTENDDATA' as [Type] FROM ATTENDDATA

UNION ALL

SELECT *,'OVERTIMEDATA' as [Type] FROM OVERTIMEDATA

then with your end result you can see what comes from where.

if it not working try below

``````WITH ATTENDDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
),
OVERTIMEDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

SELECT * FROM ATTENDDATA

UNION ALL

SELECT * FROM OVERTIMEDATA
``````
0

LVL 1

Author Closing Comment

Hi RossTurner,

Great. It works.

Thank you very much for your help.
0

## Featured Post

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.