Prev: Automate Scripting of Jobs to a flat file
Next: See each of the steping through few rows to see how data getsappe
From: Frank on 29 Jan 2010 08:38
Hi,

I have a table (#qed_missing_quarters) with the following rows in it:

row_num quarter_end_date
1 2002-09-30 00:00:00.000
2 2002-12-31 00:00:00.000
3 2003-03-31 00:00:00.000
4 2003-06-30 00:00:00.000
5 2003-09-30 00:00:00.000

And I have the following code as a test to traverse across the table:

;WITH qed_missing_values(row_num, quarter_end_date)
AS
(
SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
FROM #qed_missing_quarters WHERE row_num = 1

UNION ALL

SELECT a.row_num + 1, a.quarter_end_date
FROM #qed_missing_quarters AS a
INNER JOIN qed_missing_values AS b
ON a.row_num = b.row_num
)
SELECT * FROM qed_missing_values

However, I always end up with an extra first row, so 6 rows are
returned (the first one twice).
And I can't seem to get rid of the extra row.
Can anyone explain why I get the extra row and how to remove it
please.

Many thanks,
Frank.
From: Simon Whale on 29 Jan 2010 09:05

"Frank" <francis.moore(a)gmail.com> wrote in message
news:b6d40f9c-eb13-4681-b470-8608f699a628(a)k5g2000yqf.googlegroups.com...
> Hi,
>
> I have a table (#qed_missing_quarters) with the following rows in it:
>
> row_num quarter_end_date
> 1 2002-09-30 00:00:00.000
> 2 2002-12-31 00:00:00.000
> 3 2003-03-31 00:00:00.000
> 4 2003-06-30 00:00:00.000
> 5 2003-09-30 00:00:00.000
>
> And I have the following code as a test to traverse across the table:
>
> ;WITH qed_missing_values(row_num, quarter_end_date)
> AS
> (
> SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
> FROM #qed_missing_quarters WHERE row_num = 1
>
> UNION ALL
>
> SELECT a.row_num + 1, a.quarter_end_date
> FROM #qed_missing_quarters AS a
> INNER JOIN qed_missing_values AS b
> ON a.row_num = b.row_num
> )
> SELECT * FROM qed_missing_values
>
> However, I always end up with an extra first row, so 6 rows are
> returned (the first one twice).
> And I can't seem to get rid of the extra row.
> Can anyone explain why I get the extra row and how to remove it
> please.
>
> Many thanks,
> Frank.

have you tried removing the following?

> SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
> FROM #qed_missing_quarters WHERE row_num = 1
>
> UNION ALL

if its required then change the UNION ALL to union

Simon


From: Frank on 29 Jan 2010 10:13
Hi Simon,

Thanks for the response.
I probably didn't explain what I was trying to do with the CTE.
I'm trying to recurse across a table row by row and perform a
computation on each row.
If I remove the UNION ALL or change it to a UNION then the CTE loses
it's recursiveness.

Regards,
Frank.
From: Plamen Ratchev on 29 Jan 2010 10:27
This happens because you join the first row twice. You have to change the predicate in the recursive query to join on
the next row:

;WITH qed_missing_values(row_num, quarter_end_date)
AS
(
SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
FROM #qed_missing_quarters
WHERE row_num = 1

UNION ALL

SELECT a.row_num, a.quarter_end_date
FROM #qed_missing_quarters AS a
INNER JOIN qed_missing_values AS b
ON a.row_num = b.row_num + 1
)
SELECT row_num, quarter_end_date
FROM qed_missing_values;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Frank on 29 Jan 2010 11:41
Plamen,

Thanks, that makes sense now that you've pointed it out.
I think that 'a.row_num = b.row_num + 1' was about the only
combination that I didn't try.

Cheers,
Frank.

 | 
Pages: 1
Prev: Automate Scripting of Jobs to a flat file
Next: See each of the steping through few rows to see how data getsappe