Posted by Roy Harvey on 02/23/07 12:43

On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pipe.jack@gmail.com> wrote:

> Roy,
>
>I had different output when I ran your query:
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
>
>It looked like select statement output with 17 rows (17 columns in the
>table) like the above. No UPDATE or SET function.

I assume you changed the column names to all be _NAME, rather than the
query actually returning that.

The missing UPDATE and SET is what I would expect if the query was
written to filter out the first column. The specification said every
column, so I did not write the query to allow for that. In the first
CASE the first WHEN test would have to change from the simple:

WHEN C.colid = 1

To something like:

WHEN C.colid = (SELECT MIN(x.colid) FROM syscolumns as X WHERE X.id =
O.id AND <whatever filtering was used in the outer WHERE clause>)

Likewise the subquery in the last CASE would have to add the same
tests to match the WHERE clause.

Roy Harvey
Beacon Falls, CT

[Back to original message]