Prev: Connection Status
Next: Schema comparison of two databases
From: tshah on 17 Jul 2008 12:21
Hello all,

I am trying to write a simple CTE query in SQL server 2005 as below:

WITH TPAIPCTE(PIN, PINCount)
AS
(
SELECT PIN, COUNT(PIN)
FROM DR_DetailRecordIP
WHERE TaxYear = '05'
AND Installment = '1'
GROUP BY PIN
)

The abvoe quey generates the following exception:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

Does anyone know, what I am doing wrong? Is there any setting which I need
to enabled before running CTE queries?

Thanks,

tshah.


From: Bob on 17 Jul 2008 12:26
You do have to select from the CTE:

;WITH TPAIPCTE(PIN, PINCount)
AS
(
SELECT PIN, COUNT(PIN)
FROM DR_DetailRecordIP
WHERE TaxYear = '05'
AND Installment = '1'
GROUP BY PIN
)
SELECT *
FROM TPAIPCTE

"tshah" wrote:

> Hello all,
>
> I am trying to write a simple CTE query in SQL server 2005 as below:
>
> WITH TPAIPCTE(PIN, PINCount)
> AS
> (
> SELECT PIN, COUNT(PIN)
> FROM DR_DetailRecordIP
> WHERE TaxYear = '05'
> AND Installment = '1'
> GROUP BY PIN
> )
>
> The abvoe quey generates the following exception:
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near ')'.
>
> Does anyone know, what I am doing wrong? Is there any setting which I need
> to enabled before running CTE queries?
>
> Thanks,
>
> tshah.
>
>
From: Plamen Ratchev on 17 Jul 2008 12:31
You are missing the statement that uses the CTE definition query (just like
you use a derived table in the outer query):

WITH TPAIPCTE(PIN, PINCount)
AS
(
SELECT PIN, COUNT(PIN)
FROM DR_DetailRecordIP
WHERE TaxYear = '05'
AND Installment = '1'
GROUP BY PIN
)
SELECT PIN, PINCount
FROM TPAIPCTE;

See for more details the following article:
http://msdn.microsoft.com/en-us/library/ms190766.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: tshah on 17 Jul 2008 12:52
Thanks Bob, Thanks Plamen.

It did resolve the issue. I wasn't aware of the fact that I need to run
SELECT statement following the CTE query.

Regards,

tshah.

"Plamen Ratchev" wrote:

> You are missing the statement that uses the CTE definition query (just like
> you use a derived table in the outer query):
>
> WITH TPAIPCTE(PIN, PINCount)
> AS
> (
> SELECT PIN, COUNT(PIN)
> FROM DR_DetailRecordIP
> WHERE TaxYear = '05'
> AND Installment = '1'
> GROUP BY PIN
> )
> SELECT PIN, PINCount
> FROM TPAIPCTE;
>
> See for more details the following article:
> http://msdn.microsoft.com/en-us/library/ms190766.aspx
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>
 | 
Pages: 1
Prev: Connection Status
Next: Schema comparison of two databases