Help with CTE 1

[sparkbyte]

I am trying to write a CTE to control when an insert process runs.

I keep having problems with the IF statement.

WITH
	cteTotals (TTL_Prod, TTL_Dev)
AS
(
	Select 
		(Select Count(*) From MailroomTracking.dbo.tblTrackingTable),
		(Select Count(*) From MailroomTracking_DEV.dbo.tblTrackingTable)
)

--Select Sum(TTL_Prod-TTL_Dev) From cteTotals

--Select  
--	TTL_Prod, 
--	TTL_Dev,
--	Sum(TTL_Prod-TTL_Dev) As New_Rows
--From cteTotals
--Group by TTL_Prod, TTL_Dev

	If (Select Sum(TTL_Prod-TTL_Dev) From cteTotals) > 0 
		Begin 
			Print 'Perform Insert'
		End
	Else
		Print 'No new rows'

Thanks

John Fuhrman

 

[bborissov]

Is there any reason to use CTE?
Why not:

DECLARE @TTL_Prod int
DECLARE @TTL_Dev  int
SET @TTL_Prod = 0
SET @TTL_Dev  = 0

Select @TTL_Prod = Count(*)
       From MailroomTracking.dbo.tblTrackingTable

Select @TTL_Dev  = Count(*)
       From MailroomTracking_DEV.dbo.tblTrackingTable

IF (@TTL_Prod - @TTL_Dev) > 0
   Begin
         Print 'Perform Insert'
   End
ELSE
   Begin
         Print 'Do NOT Perform Insert'
   End

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

I agree with Boris.

BTW, looks like you worked way beyond your midnight...

PluralSight Learning Library

 

[sparkbyte]

Boris, to answer your question as to why. I thought it would be a fairly easy script to start with to see how CTE's work.

Your solution works. Which was very simular to my 1st solution. So why does the CTE version not work whith the same style If??

WITH
	cteTotals (TTL_Prod, TTL_Dev)
AS
(
	Select 
		(Select Count(*) From MailroomTracking.dbo.tblTrackingTable),
		(Select Count(*) From MailroomTracking_DEV.dbo.tblTrackingTable)
)

	If (TTL_Prod - TTL_Dev) > 0 
		Begin 
			Print 'Perform Insert'
		End
	Else
		Begin
			Print 'No new rows'
		End

[red]
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'If'.
[/red]

Thanks

John Fuhrman

 

[markros]

In SQL Server you can not operate with the field's values in the procedural code.

In other words,

IF Field1 - Field2 > Some Value

is invalid syntax in SQL Server.

You can only operate with variables or constants using the IF logic.

In addition, CTE can only be used once after it created and it can only be used in the SET based statement.

PluralSight Learning Library

 

[sparkbyte]

OK, I see now why that won't work as a CTE.

Thanks!!

Thanks

John Fuhrman