Prev: Bug? Problem with query on linked server
Next: How to optimize "Inserted Scan" in trigger code
From: axapta on 4 Jul 2008 10:54
Hi,
How can I contruct a statement like such which may return many values
(@type)

select @type = "select type FROM hlsoccupancy
WHERE hlscaseno = @hlscaseno = AND seqno = @seqno"


I then need to feed this value into other queries as such:
if @type = 'abc' then

Do I use a transaction or what?
Your help much appreciated.

From: Plamen Ratchev on 4 Jul 2008 11:08
If your query returns a single value for the WHERE filter, then you can use
something like this:

SELECT @type = [type]
FROM hlsoccupancy
WHERE hlscaseno = @hlscaseno
AND seqno = @seqno;

If there could be multiple values in the result set you have to decide how
to handle it. One way is to take the MAX value:

SELECT @type = MAX([type])
FROM hlsoccupancy
WHERE hlscaseno = @hlscaseno
AND seqno = @seqno;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: axapta on 4 Jul 2008 11:22
Hi Plamen,
I need to process each value that returns.

Maybe if I explained the data this may help.

The data concerns placing people in accomodations. They could in effect be
placed in many diffferent accomodation types: hostel, b&b, hotel etc.

If a person has been placed in 3 different accomodation types, the query
will return 3 rows. for example 1 for hostel, 1 for b&b and 1 for hotel.

Each accommodation type addresses are held in different tables hence once I
get the value back for each type, I will need to process this against the
relevant tables to bring back the full address.

I will need to loop around the returned recordset... transaction??
Thanks

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:DC1B6BB6-07D9-4427-90E5-A19B15E2EDA3(a)microsoft.com...
> If your query returns a single value for the WHERE filter, then you can
> use something like this:
>
> SELECT @type = [type]
> FROM hlsoccupancy
> WHERE hlscaseno = @hlscaseno
> AND seqno = @seqno;
>
> If there could be multiple values in the result set you have to decide how
> to handle it. One way is to take the MAX value:
>
> SELECT @type = MAX([type])
> FROM hlsoccupancy
> WHERE hlscaseno = @hlscaseno
> AND seqno = @seqno;
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

From: Plamen Ratchev on 4 Jul 2008 11:50
Please post the structure of all relevant tables and sample data. There
should be no reason to use loop. You can join on multiple tables and based
on the type select the correct address (or use subqueries). But it is hard
to suggest anything without seeing your tables and relations.

Transactions have nothing to do with what you are trying to accomplish here.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: axapta on 4 Jul 2008 13:09
Psuedocode as follows: Please remember that this is the structure of what I
am after. I need the SQL that supports this structure.
declare @hlscase
declare @type
declare @seqno

SELECT @type = [type] ' I want to return the type column from the
hlsoccupany table where records
FROM hlsoccupancy 'match the passed in parameters @hlscaseno and @seqno
WHERE hlscaseno = @hlscaseno
AND seqno = @seqno;


(Can return more than ONE record) then process as follows:

if @type= 'hlshotel' or @type = 'vsh' then

if @type = 'vsh' then

h_tablename =
'vsh';

h_tablename_room
= 'vshroom';

else

h_tablename =
'hlshotel';

h_tablename_room
= 'hotelroom';

endif;

select

squeeze(varchar(hr.number)+squeeze(hr.letter)+',
'+h.name+', '+h.addr1+', '+h.addr2+', '+h.postcode) as
h_address

from

:h_tablename h,

:h_tablename_room
hr

where

h.hotelno =
:tbl.h_accomno and

h.hotelno =
hr.hotelno and

hr.number =
:tbl.h_roomno and

hr.letter =
:tbl.h_letter;



h_assoccode = '';

h_tenurecode = 'BB';



elseif @type = 'hostel' then

select

squeeze('UNIT '
+varchar(hu.number)+squeeze(hu.letter)+', '+h.name+', '+h.addr1+',
'+h.addr2+', '+h.postcode) as h_address

from

hostel h,

hostelunit hu

where

h.hostelno =
:tbl.h_accomno and

h.hostelno =
hu.hostelno and

hu.number =
:tbl.h_roomno and

hu.letter =
:tbl.h_letter;



h_assoccode = '';

h_tenurecode = 'HOS';



elseif @type = 'hal' then



select

assoccode
as h_assoccode,

squeeze(propname+'
'+varchar(number)+squeeze(letter)+' '+addr1+', '+addr2+', '+addr3+',
'+postcode) as h_address

from

hal

where

halno =
:tbl.h_accomno;



h_tenurecode = 'AST';



elseif @type = 'rdgs' then



select

assoccode
as h_assoccode,

squeeze(propname+'
'+varchar(number)+squeeze(letter)+' '+addr1+', '+addr2+', '+addr3+',
'+postcode) as h_address

from

rdgs

where

rdgsno =
:tbl.h_accomno;



h_tenurecode = 'RDGS';



elseif @type = 'hra' or @type = 'psl'
then



select

squeeze(flat + '
' + ifnull(char(number),'') + letter + ' ' +haddr1+', '+haddr2+',
'+haddr3+', '+postcode) as h_address

from

counciladdr

where

propid =
:tbl.h_propid;



h_assoccode = '';

h_tenurecode =
uppercase(tbl.h_type);



endif;



tbl.address = h_address;

tbl.tenurecode = h_tenurecode;

tbl.h_assoccode = h_assoccode;




This is based on UNIX SQL but you should be able to work out what is
happening by the structure. Hope this helps.



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:325B9791-0B84-42BE-BB1E-5EC0B27F0A31(a)microsoft.com...
> Please post the structure of all relevant tables and sample data. There
> should be no reason to use loop. You can join on multiple tables and based
> on the type select the correct address (or use subqueries). But it is hard
> to suggest anything without seeing your tables and relations.
>
> Transactions have nothing to do with what you are trying to accomplish
> here.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

 |  Next  |  Last
Pages: 1 2 3
Prev: Bug? Problem with query on linked server
Next: How to optimize "Inserted Scan" in trigger code