UNION in SQL Server

[Anakim]

Here's some code which works like a watch with no hands

SELECT c.member_no, c.mo_ord_no
     ,c.cu_name --,'Nads' 
     , oh.ord_date
     , oh.ord_date

 FROM mcodcld c, mcodooh oh 
  WHERE c.mo_ord_no = oh.mo_ord_no
union all
SELECT c.member_no, c.mo_ord_no
     , c.cu_name, --'Nads' 
     , bh.ord_date
     , bh.ord_date

  FROM mcodcld c, mcodboh bh 
  WHERE c.mo_ord_no = bh.mo_ord_no 
union all
SELECT c.member_no, c.mo_ord_no
     , c.cu_name --,'Nads'
     , dh.ord_date
     , dh.desp_date
  FROM mcodcld c, mcoddoh dh 
  WHERE c.mo_ord_no = dh.mo_ord_no

I get a message about not being able to start another thread. Now if I comment out any one of the 'c.cu_name' and substitute in the fixed string of 'Nads' it works. Any pair combination also work without any amendments. I'm stumped on this one, can anybody shed any light?

[monte96]

I don't think you need that 'all' after UNION. I use union queries all the time and don't use it.

[Anakim]

Yeah I only had 'All' in there to try and cover all possibilities. I get same smetty error if I take the 'all' out.

I can actually take the 'cu_name' out and replace it with another field from same table and get the same thing. Even interrogated the data - thought praps duplicates were the cause but that's not the case.

Have to say this is doing my swede in. Now breaking the thing into two blocks - first pair in a union into a temp table, then join the temp to the remaining block. Bleugh that's nasty but it's the only way I can see round this!

[Anakim]

Hmmm Guess none of you have encountered anything like this then?

[Ianpbaker]

your not trying to fire this off into one recordset in vb are you as if remember correctly, ADO doesn't handle unions at all

hope this helps

ian

[Anakim]

No it's actually from a SPROC (stored procedure) but thanks for that info as I may have tried something similar from VB.

[monte96]

ADO most certainly does handle UNION queries. (I use them all the time)

The query is passed to the server and compiled on the fly, so presuming that the server (I'm assuming SQL here) supports UNION queries, it should work fine and return it in one recordset. UNION simply appends the results from one query to the end of the last one unless you include an Order By clause specifying the ordinal number of the field to be sorted by.


Did you run each subquery separately first? Then join them? Did you have any errors then?

[Edited by monte96 on 10-20-2000 at 11:50 AM]

[Anakim]

Monte - Each query works fine on it's own and any pair work fine together and, as originally stated, any one of the cu_name fields can be substituted for a string and then all 3 queries are fine.

I gave up in the end and used a pair of the queries to populate a temp table and then join the third query on this.

Bizarre is what this is. For your info the whole SQL script is within a SPROC on SQL Server 7, with the SPROC being called from VBScript but where it's called from is neither here nor there as just running the queries in Query Analyzer will fail.