Prev: CONMON Database please
Next: Date Questions
From: axapta on 5 Jul 2008 13:27
Thanks Plamen!! With your help I've been able to get this to work; and I've
learnt a lot too from your postings/comments!!

Thank YOU!!
"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:FE1FBD08-849A-4E0C-A3BC-7D704BB3ECCC(a)microsoft.com...
> You changed the requirements. Your initial post had a single column in the
> result set. The CASE function cannot return multiple columns. On top of
> that you have subqueries return different number of columns. A result set
> must contain a consistent set of columns.
>
> You can try UNION ALL for each subquery, here is example, with the first
> two. Note the added dummy column in the second query to guarantee
> consistent result set. And I am not even sure what that RIGHT OUTER JOIN
> does there, so just copied your code.
>
> SELECT o.starteddate,
> o.propid,
> COALESCE(cast(hr.number as varchar(5)) + ', ') +
> COALESCE( hr.letter + ', ') +
> COALESCE(h.name + ', ')+
> h.addr1 + ' ' +
> h.addr2 + ' ' +
> h.postcode as address
> FROM vsh AS h
> JOIN vshroom AS hr
> ON h.hotelno = hr.hotelno
> JOIN hlsoccupancy AS o
> ON hr.number = o.roomno
> AND hr.letter = o.letter
> WHERE o.hlscaseno = @hlscaseno
> AND o.seqno = @seqno
> AND h.hotelno = o.accomno
> AND EXISTS (SELECT *
> FROM hlsoccupancy
> WHERE hlscaseno = @hlscaseno
> AND seqno = @seqno
> AND [type] = 'vsh')
> UNION ALL
> SELECT o.starteddate,
> 0, -- missing column !!!!
> 'Unit' + COALESCE(CAST(hu.number AS varchar(5)) + ', ') +
> COALESCE (hu.letter + ', ') +
> COALESCE (h.name + ', ') + h.addr1 + ' ' +
> h.addr2 + ' ' + h.postcode AS address
> FROM hostel AS h
> INNER JOIN hostelunit AS hu
> ON h.hostelno = hu.hostelno
> RIGHT OUTER JOIN hlsoccupancy AS o
> ON h.hostelno = o.accomno
> AND hu.number = o.roomno
> AND hu.letter = o.letter
> WHERE o.hlscaseno = @hlscaseno
> AND o.seqno = @seqno
> AND h.hostelno = hu.hostelno
> ANS h.hostelno = o.accomno
> AND EXISTS (SELECT *
> FROM hlsoccupancy
> WHERE hlscaseno = @hlscaseno
> AND seqno = @seqno
> AND [type] = 'hlshotel')
> UNION ALL ...
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

 | 
Pages: 1
Prev: CONMON Database please
Next: Date Questions