From: "Robert Comer" 

Thanks Ellen, I'll give it a try!

I'm not so sure the original programmer didn't mess up in some other way
that causes this error. (or the error isn't a good description of what the
problem really is.)  I wonder if loading a newer version of SQL Server on
my test server would make any difference in the error message...

> P.S.  I'm writing this from Montreal, this is the first time I've checked
in in
> several days... I feel bad that I wasn't there when you first asked your
> question.

Don't worry about it, it's a slow moving project (read: no extra budget for
it) anyway.

I appreciate the help!

- Bob Comer


"Ellen K"  wrote in message
news:509841.e20b14{at}harborwebs.com...
> I'm here via Dale's mirror, got stuck in sleep disconnect and now can't
get
> back to your message, but here's my reply:
>
> If you mean it all has to look like one table, that's easy, just make a
derived
> table out of it:
> SELECT x.* FROM
>   (SELECT w.WRKNO
>   , w.RELNO
>   , c.CO_ID
>   , c.LOTID
>   , s.COLOR
>   , CONVERT(char(6), w.WRKNO) + '  ' + c.LOTID + '  ' + SFC_Colors.COLOR)
AS
> LISTCOL
>   FROM WorkOrder w
>   INNER JOIN CombOrder c
>   ON w.WO_ID = c.WO_ID
>   LEFT JOIN SFC_Colors s
>   ON c.COLOR_ID = s.COLOR_ID
>   WHERE w.ACTIVE = 1
>   AND c.ACTIVE = 1) x
> ORDER BY x.WRKNO, x.LOTID
>
> (You can't put the ORDER BY in the definition of a derived table.)
>
> Note that a derived table is something SQL Server really likes and can use
very
> efficiently, it's not at all the same thing as a subquery in terms of
> performance.
>
> Whether you can open a cursor on it is another question though.
Personally I
> don't use cursors so I'm not knowledgable about their ins and outs.
Cursors
> are considered not really "SQL-ish" (for lack of a better
word) and are
also
> reputed to have a really bad effect on performance.  Are you sure a cursor
is
> the best way to solve your problem?
>
> P.S.  I'm writing this from Montreal, this is the first time I've checked
in in
> several days... I feel bad that I wasn't there when you first asked your
> question.

--- BBBS/NT v4.01 Flag-5