Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 common table expression error

Author  Topic 

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 07:09:48

can anybody pls check what is the error here with this common table expression

WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS
(select Table_Name, Table_Count
from
(
select 'PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ PROD_SUM]
union all
select ' PRODUCTION_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ PRODUCTION_DELAY]
union all
select ' PROD_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ PROD_WASTE]
union all
select ' SAP_ORDER' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_ORDER]
union all
select ' SAP_ORDER_COST' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_ORDER_COST]
union all
select ' SAP_ORDER_TM_CONFIRM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_ORDER_TM_CONFIRM]
union all
select ' SAP_GOODS_MOVE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_GOODS_MOVE]
union all
select ' SAP_NOTIFICATION' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_NOTIFICATION]
union all
select ' SAP_ACTIVITY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_ACTIVITY]
union all
select ' SAP_CAUSE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_CAUSE]
union all
select ' SAP_ITEM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_ITEM]
union all
select ' SAP_MEASU_DOCUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ SAP_MEASU_DOCUM]
union all
select ' INVALID_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ INVALID_DELAY]
union all
select ' INVALID_PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ INVALID_PROD_SUM]
union all
select ' INVALID_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ INVALID_WASTE]
union all
select ' CRIT_ANLY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ CRIT_ANLY]
union all
select ' WORK_HISTORY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ WORK_HISTORY]
union all
select ' WORK_HIST_DETL' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ WORK_HIST_DETL]
union all
select ' COST_METRICS' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[ COST_METRICS]
) I2
)

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-22 : 07:11:43
it is just a guess.
add semicolon first and try it now.
;WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS

if it doesn't helpful,

please show us the error msg u r getting.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-03-22 : 07:21:06
quote:

can anybody pls check what is the error here with this common table expression

Why didn't you post the error you were getting as well?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 07:21:18
nope,still it's showing error as--->
Incorrect syntax near ')'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2010-03-22 : 07:23:31
What's the error message ?

Does all your table name prefix with a space ?

dbo.[_PROD_SUM]


Can you also post the full query ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 07:31:02
My full query is
WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS
(select Table_Name, Table_Count
from
(
select 'PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_SUM]
union all
select ' PRODUCTION_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[KC_PRODUCTION_DELAY]
union all
select ' PROD_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_WASTE]
union all
select ' SAP_ORDER' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER]
union all
select ' SAP_ORDER_COST' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_COST]
union all
select ' SAP_ORDER_TM_CONFIRM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_TM_CONFIRM]
union all
select ' SAP_GOODS_MOVE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_GOODS_MOVE]
union all
select ' SAP_NOTIFICATION' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_NOTIFICATION]
union all
select ' SAP_ACTIVITY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ACTIVITY]
union all
select ' SAP_CAUSE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_CAUSE]
union all
select ' SAP_ITEM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ITEM]
union all
select ' SAP_MEASU_DOCUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_MEASU_DOCUM]
union all
select ' INVALID_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_DELAY]
union all
select ' INVALID_PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_PROD_SUM]
union all
select ' INVALID_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_WASTE]
union all
select ' CRIT_ANLY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[CRIT_ANLY]
union all
select ' WORK_HISTORY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HISTORY]
union all
select ' WORK_HIST_DETL' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HIST_DETL]
union all
select ' COST_METRICS' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[COST_METRICS]
) I2
)

------------------------------
and error message is

Msg 102, Level 15, State 1, Line 43
Incorrect syntax near ')'.
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

413 Posts
Posted - 2010-03-22 : 07:35:39
When you actually query the CTE after declaring it, do you still get the error?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-03-22 : 07:41:07
lol -- yeah -- that works for me also.

I get the syntax error if no query immediately after using the CTE.

Nice catch Frank.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 07:41:38
yes,for above statement i m still getting an error
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-22 : 07:43:15
try this
;WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS
(select Table_Name, Table_Count
from
(
select 'PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_SUM]
union all
select ' PRODUCTION_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[KC_PRODUCTION_DELAY]
union all
select ' PROD_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_WASTE]
union all
select ' SAP_ORDER' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER]
union all
select ' SAP_ORDER_COST' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_COST]
union all
select ' SAP_ORDER_TM_CONFIRM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_TM_CONFIRM]
union all
select ' SAP_GOODS_MOVE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_GOODS_MOVE]
union all
select ' SAP_NOTIFICATION' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_NOTIFICATION]
union all
select ' SAP_ACTIVITY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ACTIVITY]
union all
select ' SAP_CAUSE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_CAUSE]
union all
select ' SAP_ITEM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ITEM]
union all
select ' SAP_MEASU_DOCUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_MEASU_DOCUM]
union all
select ' INVALID_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_DELAY]
union all
select ' INVALID_PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_PROD_SUM]
union all
select ' INVALID_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_WASTE]
union all
select ' CRIT_ANLY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[CRIT_ANLY]
union all
select ' WORK_HISTORY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HISTORY]
union all
select ' WORK_HIST_DETL' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HIST_DETL]
union all
select ' COST_METRICS' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[COST_METRICS]
) I2
)
select * from DataPurgeCountTable2
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 07:47:31
yes it's working ,last select statement (select * from DataPurgeCountTable2) i was missing
also how can I use OPTION (MERGE UNION) at the end like
;WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS
(select Table_Name, Table_Count
from
(
select 'PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_SUM]
union all
select ' PRODUCTION_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[KC_PRODUCTION_DELAY]
union all
select ' PROD_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_WASTE]
union all
select ' SAP_ORDER' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER]
union all
select ' SAP_ORDER_COST' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_COST]
union all
select ' SAP_ORDER_TM_CONFIRM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_TM_CONFIRM]
union all
select ' SAP_GOODS_MOVE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_GOODS_MOVE]
union all
select ' SAP_NOTIFICATION' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_NOTIFICATION]
union all
select ' SAP_ACTIVITY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ACTIVITY]
union all
select ' SAP_CAUSE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_CAUSE]
union all
select ' SAP_ITEM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ITEM]
union all
select ' SAP_MEASU_DOCUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_MEASU_DOCUM]
union all
select ' INVALID_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_DELAY]
union all
select ' INVALID_PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_PROD_SUM]
union all
select ' INVALID_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_WASTE]
union all
select ' CRIT_ANLY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[CRIT_ANLY]
union all
select ' WORK_HISTORY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HISTORY]
union all
select ' WORK_HIST_DETL' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HIST_DETL]
union all
select ' COST_METRICS' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[COST_METRICS]
OPTION (MERGE UNION)
)
I2
)
select * from DataPurgeCountTable2
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-22 : 07:55:51
<<how can I use OPTION (MERGE UNION) at the end like>>
try this.

with cte(a,b) as
(
select '1','2'
union all
select '1','3'
union all
select '1','2' union all
select '1','2'
union all
select '1','2'
union all
select '1','2'

)
select * from cte where b=3
OPTION (MERGE JOIN);
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 08:15:10
excellant !!! it's working thanks haroon2k9
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-22 : 08:17:08
quote:
Originally posted by qutesanju

excellant !!! it's working thanks haroon2k9


Welcome.
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 08:21:43
also can I use Is exists in order to improve performance better
like below

;WITH DataPurgeCountTable2 (Table_Name, Table_Count) AS
(select Table_Name, Table_Count
from
(
if exists(select 'PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_SUM] )
union all
select ' PRODUCTION_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[KC_PRODUCTION_DELAY]
union all
select ' PROD_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[PROD_WASTE]
union all
select ' SAP_ORDER' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER]
union all
select ' SAP_ORDER_COST' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_COST]
union all
select ' SAP_ORDER_TM_CONFIRM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ORDER_TM_CONFIRM]
union all
select ' SAP_GOODS_MOVE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_GOODS_MOVE]
union all
select ' SAP_NOTIFICATION' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_NOTIFICATION]
union all
select ' SAP_ACTIVITY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ACTIVITY]
union all
select ' SAP_CAUSE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_CAUSE]
union all
select ' SAP_ITEM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_ITEM]
union all
select ' SAP_MEASU_DOCUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[SAP_MEASU_DOCUM]
union all
select ' INVALID_DELAY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_DELAY]
union all
select ' INVALID_PROD_SUM' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_PROD_SUM]
union all
select ' INVALID_WASTE' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[INVALID_WASTE]
union all
select ' CRIT_ANLY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[CRIT_ANLY]
union all
select ' WORK_HISTORY' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HISTORY]
union all
select ' WORK_HIST_DETL' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[WORK_HIST_DETL]
union all
select ' COST_METRICS' as 'Table_Name' ,count (*) as 'Table_Count' from dbo.[COST_METRICS]
OPTION (MERGE UNION)
)
I2
)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-03-22 : 08:34:36
If you just want the number of rows in some specific tables (and only if those tables exist) wouldn't it be easier to query the system tables?

(Unless this is SQL 2000 were the system tables were rarely accurate in this regard!)
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 08:57:39
pls give an example for this
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2010-03-22 : 09:46:02
quote:
Originally posted by Kristen
(Unless this is SQL 2000 were the system tables were rarely accurate in this regard!)


You do know that CTE is not supported in SQL 2000 right ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts
Posted - 2010-03-22 : 09:55:01
i m using sql 2005
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-03-22 : 10:18:02
I think KHTan's comment was aimed at me - he likes to point out how old I am whenever the chance arises
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-22 : 11:30:34
quote:
Originally posted by Kristen

I think KHTan's comment was aimed at me - he likes to point out how old I am whenever the chance arises

But thats a fact isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site