multiple resultsets: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Multi-Value Parameter Problem

I'm creating a report that selects data from one table. I'm trying to
add a parameter that will allow the user to select the data based on a
selection from one column (SpindleName). I've added this column in the
report parameters, checked the multi-value box, set the available
values to "from query" to the dataset & field, set the default values
to "from query" to the same dataset & field. When I execute the query,
I get asked for the SpindleName, but the box contains no values other
than "NULL" and "BLANK". If I type the spindle name into the box, I
get the results. Why isn't the list being populated with the values
from the spindlename column ?
Here is the query:
SELECT StationName, SpindleName, PartId, TimeStamp
FROM dbo.Readings
WHERE SpindleName IN (@.SpindleName)
I'm using SQL 2005 Express & VS 2005
Any advice would be appreciated.
Thank-you
JeffWhat is the source query for the parameter?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||I may be wrong but it seems to me there is some confusion here. Several
things:
1. Unless you have cascading parameters (the list to select from parameter B
is dependent on the selection from parameter A) then you should have a query
that returns the value you want to select from.
2. Default means a single value. If you point to a dataset with multiple
values I don't know what it does except that is incorrect.
So, for instance by my take on this you want to this.
Have two datasets. One dataset that returns the list of SpindleName. The
other that retrieves the data you desire.
Select distinct SpindleName from Readings
Set both your label and value for the SpindleName parameter to the field
SpindleName from the above query.
Then your second query you have below will return what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||For your point #2, if your parameter's default value points to a dataset
("From Query") with multiple values, they will show up with all of them
selected in the drop down list. This is desirable in some cases.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23P5CZRGrIHA.4376@.TK2MSFTNGP06.phx.gbl...
>I may be wrong but it seems to me there is some confusion here. Several
>things:
> 1. Unless you have cascading parameters (the list to select from parameter
> B is dependent on the selection from parameter A) then you should have a
> query that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvica1@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>> I'm creating a report that selects data from one table. I'm trying to
>> add a parameter that will allow the user to select the data based on a
>> selection from one column (SpindleName). I've added this column in the
>> report parameters, checked the multi-value box, set the available
>> values to "from query" to the dataset & field, set the default values
>> to "from query" to the same dataset & field. When I execute the query,
>> I get asked for the SpindleName, but the box contains no values other
>> than "NULL" and "BLANK". If I type the spindle name into the box, I
>> get the results. Why isn't the list being populated with the values
>> from the spindlename column ?
>> Here is the query:
>> SELECT StationName, SpindleName, PartId, TimeStamp
>> FROM dbo.Readings
>> WHERE SpindleName IN (@.SpindleName)
>> I'm using SQL 2005 Express & VS 2005
>> Any advice would be appreciated.
>> Thank-you
>> Jeff
>|||On May 2, 10:53=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I may be wrong but it seems to me there is some confusion here. Several
> things:
> 1. Unless you have cascading parameters (the list to select from parameter= B
> is dependent on the selection from parameter A) then you should have a que=ry
> that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvi...@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>
> > I'm creating a report that selects data from one table. I'm trying to
> > add a parameter that will allow the user to select the data based on a
> > selection from one column (SpindleName). I've added this column in the
> > report parameters, checked the multi-value box, set the available
> > values to "from query" to the dataset & field, set the default values
> > to "from query" to the same dataset & field. When I execute the query,
> > I get asked for the SpindleName, but the box contains no values other
> > than "NULL" and "BLANK". If I type the spindle name into the box, I
> > get the results. Why isn't the list being populated with the values
> > from the spindlename column ?
> > Here is the query:
> > SELECT StationName, SpindleName, PartId, TimeStamp
> > FROM dbo.Readings
> > WHERE SpindleName IN (@.SpindleName)
> > I'm using SQL 2005 Express & VS 2005
> > Any advice would be appreciated.
> > Thank-you
> > Jeff- Hide quoted text -
> - Show quoted text -
Thanks Bruce - it worked perfectly!
Regards
Jeff

Wednesday, March 28, 2012

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.
|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row number

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row numbersql

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner jo
in
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get s
ome
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resourc
e
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/defaul...kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get
some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resou
rce
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, look
s
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was ab
le
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BT
W,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if thi
s
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However
if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveK
Could you please post your join query ?
-jens Suessmeyer.
|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.
|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default...b;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>
|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?
|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default.aspx?scid=kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:
> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:
> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table IDENTITY

Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?

That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?

No, identity fields are only unique within the table.

Monday, March 26, 2012

Multi-Select Error, easy question

I am trying to show/hide a table based on a multi-select parameter in VS 2005
beta 2.
In the parameter I have the following:
Data Type: String
Multi-value - checked
Label: General Info
Value: GeneralInfo
In the properties for the report I have the following under Visibility:
=IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
When I execute the report and choose the only parameter value I get the
following. (once I get this to work I will add additional parameters)
Error:
Processing Error
"The Hidden expression for the table 'Table_Header' contains an error:
Overload resolution failed because no Public '=' can be called with these
arguments:
Public Shared Operator =(a As String, b As String) As Boolen':
Argument matching parameter 'a' connot convert from 'Object()' to 'String'.
Thanks!!!
--
Thank You!Once you mark a parameter as "multi-value", the .Value property will return
an object[] with all selected values. If only one value is selected, it will
be an object array of length = 1. Object arrays cannot be directly compared
with Strings.
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>I am trying to show/hide a table based on a multi-select parameter in VS
>2005
> beta 2.
> In the parameter I have the following:
> Data Type: String
> Multi-value - checked
> Label: General Info
> Value: GeneralInfo
> In the properties for the report I have the following under Visibility:
> =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> When I execute the report and choose the only parameter value I get the
> following. (once I get this to work I will add additional parameters)
>
> Error:
> Processing Error
> "The Hidden expression for the table 'Table_Header' contains an error:
> Overload resolution failed because no Public '=' can be called with these
> arguments:
> Public Shared Operator =(a As String, b As String) As Boolen':
> Argument matching parameter 'a' connot convert from 'Object()' to
> 'String'.
> Thanks!!!
> --
> Thank You!|||Robert, thanks my man, thanks for taking the time to respond. I'll try this
out first thing tomorrow.
Thanks!
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Robert, thanks for your reply. Very valuable information for this report and
for my future reports.
You wrote, "Object arrays cannot be directly compared with strings." Do you
know how I could do this 'indirectly'?
Let's say the user selects 'GeneralInfo' and 'Contact Info' in the
multi-select. Do you know of a way to evaluate their selection so I can take
action on it? (such as visability)
Thanks again for your help.
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Hi Robert, don't worry about replying again. I think I figured it out. I
need to split it after I join it, right?
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Yes. The Join() will create a string from a multi dimensional object array.
The Split() function is the inverse function; it splits the string into a
multi dimensional array.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:05BDA0D3-9D01-4C62-A844-99FE9F2BD791@.microsoft.com...
> Hi Robert, don't worry about replying again. I think I figured it out. I
> need to split it after I join it, right?
> --
> Thank You!
>
> "Robert Bruckner [MSFT]" wrote:
>> Once you mark a parameter as "multi-value", the .Value property will
>> return
>> an object[] with all selected values. If only one value is selected, it
>> will
>> be an object array of length = 1. Object arrays cannot be directly
>> compared
>> with Strings.
>> To access individual values of a multi value parameter you can use
>> expressions like this:
>> =Parameters!MVP1.IsMultiValue
>> boolean flag - tells if a parameter is defined as multi value
>> =Parameters!MVP1.Count
>> returns the number of values in the array
>> =Parameters!MVP1.Value(0)
>> returns the first selected value
>> =Join(Parameters!MVP1.Value)
>> creates a space separated list of values
>> =Join(Parameters!MVP1.Value, ", ")
>> creates a comma separated list of values
>> =Split("a b c", " ")
>> to create a multi value object array from a string (this can be used
>> e.g. for drillthrough parameters, subreports, or query parameters)
>> See also MSDN:
>> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
>> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
>> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>> >I am trying to show/hide a table based on a multi-select parameter in VS
>> >2005
>> > beta 2.
>> >
>> > In the parameter I have the following:
>> > Data Type: String
>> > Multi-value - checked
>> > Label: General Info
>> > Value: GeneralInfo
>> >
>> > In the properties for the report I have the following under Visibility:
>> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
>> >
>> > When I execute the report and choose the only parameter value I get the
>> > following. (once I get this to work I will add additional parameters)
>> >
>> >
>> > Error:
>> > Processing Error
>> > "The Hidden expression for the table 'Table_Header' contains an error:
>> > Overload resolution failed because no Public '=' can be called with
>> > these
>> > arguments:
>> > Public Shared Operator =(a As String, b As String) As Boolen':
>> > Argument matching parameter 'a' connot convert from 'Object()' to
>> > 'String'.
>> >
>> > Thanks!!!
>> >
>> > --
>> > Thank You!
>>

Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

MultiRow select to SingleRow output?

Here is what I want to do.

I have a customer table with say id, firstname, lastname. I also have another table called phone number that contains multiple phone numbers based on the customer id (id, customerid, phonenumber).

Ok I want to output a record like this

firstname, lastname, phone1, phone2, phone3

I need one record per customer with as many phone numbers as they may have, I could always limit the numbers to 3 or better yet return a null if there are less then the predefined number of phone numbers.

If I try a simple join I get multiline output which is no good for me.

SELECT firstname, lastname, phonenumber
FROM customer, phone
WHERE phone.customerid = customer.id

Thanks.---------------
create function dbo.PhoneString(@.customerid int)
returns varchar(500)
as
begin
declare @.PhoneString varchar(500)

set @.PhoneString = ''

select @.PhoneString = @.PhoneString + phonenumber + ', '
from PhoneNumberTable
where customerid = @.customerid

set @.PhoneString = left(@.PhonesString, len(@.PhoneString)-1)

return @.PhoneString
end
---------------
select firstname, lastname, dbo.PhoneString(ID) from customers

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Alessandro" <giumalex@.tiscali.it> wrote in message news:<g8tkc.30640$eR5.16238@.tornado.fastwebnet.it>...
> Hi, here is the problem...
>...
> I can't realize how to write this 'simple' problem... can anyone help me?
> Many thanks,
> Alex

Hi Alex.. to work with SQL , query and paramter look at this site :

Http://www.RealTimeInformatica.it/Stany

Bye Bye ...

ps: anche in Italiano !;)

Friday, March 23, 2012

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Multipurpose Foreign Key

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?
Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?

I started writing a sproc (see "Beginnings of sproc," below), which
will definitely work, once I get it set up, but when I do these sorts
of things, I later come to find that there was a straight SQL way to
do it. So, is there a straight SQL way to do this, building joins with
CASEs, or something like that?

Thanks,
Jamie

## Structure (simplified) ##

[USER]
userID
unitID
role -- values can be 'B' or 'C' referring to [base] or [command] tbl

[BASE]
ID
NAME

[COMMAND]
ID
NAME

## Beginnings of a sproc ##

GO
USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'getSessionInfo' AND type = 'P')
DROP PROCEDURE getSessionInfo
GO
USE myDB
GO
CREATE PROCEDURE getSessionInfo
@.userID varchar(50),
@.password varchar(50)
AS

DECLARE @.myUnitType varchar(2);

SELECT @.myUnitType = unitType
FROM
[user]
WHERE userID = @.userID
AND [password] = @.password

... blah blah blahJamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?

It is certainly not the plain standard design. And the method has the
apparent advantage that you can use FOREIGN-KEY constraint to enforce
the integrity, but you need to rely on triggers.

But since I know very little of your business problem, I am hesitant
to label the design as outright bad, or even poor. What I can say, is
that had I had the problem, I would definitely have looked into a solution
that would have permitted me to use DRI, but that would definitely have
been a case of fitting the solution to the tool.

> Question2: If this is okay DB design (or, if I can't change this DB),
> how do I perform a join?

Depends a little on the output, but say you want user and name of
base or command:

SELECT u.name, u.role, rolename = coalece(b.name, c.name)
FROM users u
LEFT JOIN base b ON u.role = 'B'
AND u.unitid = b.unitid
LEFT JOIN command c ON u.role = 'C'
AND u.unitid = c.unitid

An alternative is to introduce a basecommand table, to gather common
information, but I don't know enough about your business problem to
say whether this is a good idea or not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jamie Jackson wrote:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?
<snip
Yes. A table represents something. In your case a User, a Base or a
Command. In your design you specify whether a User can have a relation
with a Base or not. If it can have a relation, then this means there is
a foreign key column (or a relation table) for this specific relation.
For Command you make the same decision.

So in your case, User should have one column with a foreign key
constraint to Base, and another column with a foreign key constraint to
Command. You make the relations optional by allowing NULL values in the
foreign key columns.

If this is an Object Oriented design (for example, when Command extends
from Base), then you are in trouble, because OO and RDBMS don't map very
well.

Hope this helps,
Gert-Jan|||Thanks, Erland, that SQL was a nice tutorial on filtered joins and the
coalesce function, neither of which I've ever used.

Thanks,
Jamie

On Wed, 16 Jul 2003 21:54:51 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Jamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
>> I've got a field [user.unitID] that can be a foreign key on the table
>> [Base] *OR* the table [Command] depending on the value of [User.role]
>> (see "Structure," below).
>>
>> Question 1: Is this poor DB design?
>It is certainly not the plain standard design. And the method has the
>apparent advantage that you can use FOREIGN-KEY constraint to enforce
>the integrity, but you need to rely on triggers.
>But since I know very little of your business problem, I am hesitant
>to label the design as outright bad, or even poor. What I can say, is
>that had I had the problem, I would definitely have looked into a solution
>that would have permitted me to use DRI, but that would definitely have
>been a case of fitting the solution to the tool.
>> Question2: If this is okay DB design (or, if I can't change this DB),
>> how do I perform a join?
>Depends a little on the output, but say you want user and name of
>base or command:
>
> SELECT u.name, u.role, rolename = coalece(b.name, c.name)
> FROM users u
> LEFT JOIN base b ON u.role = 'B'
> AND u.unitid = b.unitid
> LEFT JOIN command c ON u.role = 'C'
> AND u.unitid = c.unitid
>An alternative is to introduce a basecommand table, to gather common
>information, but I don't know enough about your business problem to
>say whether this is a good idea or not.

Multiply Rows with T - SQL

I need to multiply rows of a table with each other. I found a function like this

exp(sum(ln(floatfield1))).

But I can not find the ln function. Is there any other possibility ?

Thanks for any help.

Uwe

in function ? is this for MS SQL Server ?

Post your table structure, some sample data and the expected result. We should be able to help you to come up with the query|||

Uwe, LOG is the natural logarithm function in SQL Server. Also, see some of the previous discussions of this question: http://groups.google.com/groups/search?q=aggregate+product+kass+sqlserver Steve Kass Drew University Uwe Helmer@.discussions.microsoft.com wrote:
> I need to multiply rows of a table with each other. I found a function
> like this
>
> exp(sum(ln(floatfield1))).
>
> But I can not find the ln function. Is there any other possibility ?
>
>
>
> Thanks for any help.
>
> Uwe
>
>

|||

In your SQL statment

exp(sum(LOG(floatfield1))).

In SQL

Constant e (2.71828182845905…) based Natural log is set as LOG and 10 based is written as LOG10.

Some examples:

LOG ( e ) = 1.0

EXP( LOG( n ) ) = n.

10 ^ LOG10(n) = n

This seelect statement

SELECT LOG (EXP (5))

returns 5.

multiplue UI Report Tables using exact same dataset ?

Is there an easier way to manage the table UI element on the report. For
example in my report I have a table for the header info and the data, the
data uses maybe 10 columns and we could be perhapps adding more columns
(fields) to it in the future. The header uses 4 columns. It's a pain
because everytime I add a column to the main report details I have to go up
and fix the header and merge the appropriate cells to get it back to 4 cells
in the header. Is there not a way to use multiple table elements but they
both use the same dataset,. When I say same dataset, I dont mean a copy of
the same dataset, I want it to use the same dataset without the query
running twice for each table.
You guys know what I mean?
Possible ?Sorry, I'm not sure what you mean!
Is the table header a seperate table from the 'data' table?
Are they using the same dataset or not?
If the header and data are in fact seperate tables, then why are you
doing it like that, you seem to be making extra work for yourself!
If two tables use the same dataset the query would only be run once.
Chris
D Witherspoon wrote:
> Is there an easier way to manage the table UI element on the report.
> For example in my report I have a table for the header info and the
> data, the data uses maybe 10 columns and we could be perhapps adding
> more columns (fields) to it in the future. The header uses 4
> columns. It's a pain because everytime I add a column to the main
> report details I have to go up and fix the header and merge the
> appropriate cells to get it back to 4 cells in the header. Is there
> not a way to use multiple table elements but they both use the same
> dataset,. When I say same dataset, I dont mean a copy of the same
> dataset, I want it to use the same dataset without the query running
> twice for each table.
> You guys know what I mean?
> Possible ?

Multiples in a select statement

Is it possible to write a single select statement that would pull the following from a table named tblPayments.

I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.

tblPayments
ClientID
PaymentDate
PaymentAmount

Thanks for any suggestions.

create table p (ClientID int, PaymentDate datetime, PaymentAmount numeric(10,2))

insert into p
select 0,'1/1/2004',100 union all
select 0,'1/2/2004',80 union all
select 0,'1/3/2004',110 union all
select 1,'1/5/2004',87 union all
select 1,'1/12/2004',180 union all
select 1,'1/13/2004',10

select
d.clientID,
d.firstPmtDate,
p1.PaymentAmount,
d.lastPmtDate,
p2.PaymentAmount,
p3.PaymentDate maxPmtDate,
d.maxPmt,
d.ttlPmt
from
(
select
clientID,
min(paymentdate) firstPmtDate,
max(paymentDate) lastPmtDate,
max(PaymentAmount) maxPmt,
sum(PaymentAmount) ttlPmt
from p
--where PaymentDate between @.startDate and @.endDate
group by clientid
) d
join p p1 on p1.clientid = d.clientid and p1.paymentdate = d.firstPmtDate
join p p2 on p2.clientid = d.clientid and p2.paymentdate = d.lastPmtDate
join p p3 on p3.clientid = d.clientid and p3.PaymentAmount = d.maxPmt
--where p3.PaymentDate between @.startDate and @.endDate

|||Thank you, but this does not appear to return the amounts associated with each last and first payment dates. That is where I run into problems. I could be reading it bad?

Thanks again,|||Did you run the query in QA with the sample data?|||Ok, It worked in QA. I'm having some trouble understanding how you did this. How can I learn to understand this type of process?

Thanks for any suggestions,|||I'm having trouble converting this to my data. Is there anything specific I should know to do this?

Thanks again,|||What are you having trouble with specifically?|||Well, I get no errors but there is no data being returned.

Thanks,|||To demonstrate the query, I created a table called p, which represents your table tblPayments, it is an example. Then the table p is aliased as p1, p2,p3 where the joins are occuring to simplify the columns assignments. The alias d is used to declare the derived table which perform the aggregate functions to obtain max,min,etc...

If you want to use this query for you purposes you can change the table assignment of p in the from and join clauses to tblPayments.

Does that help to clarify?|||Yes, and that is what I did and I know there is data but none is being returned. This very puzzling.

Thanks again,|||I had not changed the table name in the joins and had listed my table AS p in the From clause. Now I get data, but I get many records for each client. I was hoping to end up with one row for each client. Any thoughts on this?

Thanks again,|||Can you post the revised query you are using..|||Here it is. Thanks,

select

d.Client_ID,

d.firstPmtDate,

p1.AmountPaid,

d.lastPmtDate,

p2.AmountPaid,

p3.PaymentDate maxPmtDate,

d.maxPmt,

d.ttlPmt

from

(

select

Client_ID,

min(paymentdate) firstPmtDate,

max(paymentDate) lastPmtDate,

max(AmountPaid) maxPmt,

sum(AmountPaid) ttlPmt

from tblPayments

--where PaymentDate between @.startDate and @.endDate

group by Client_ID

) d

join tblPayments p1 on p1.Client_ID = d.Client_ID and p1.paymentdate = d.firstPmtDate

join tblPayments p2 on p2.Client_ID = d.Client_ID and p2.paymentdate = d.lastPmtDate

join tblPayments p3 on p3.Client_ID = d.Client_ID and p3.AmountPaid = d.maxPmt

--where p3.PaymentDate between @.startDate and @.endDate|||It is somewhat difficult to fully understand the data behavior w/o seeing the data and variations that occur. If the rows are displaying duplicate data for each client you can modify the main select to select distinct which will limit dups from the results set.

Hope that helps.sql

Multiple-Parents

Can I have two different parents for the same child table in a dataset. The columns for which the relationships exist are the same:

For ex:

TABLE1

ID DATA

TABLE2

ID DATA

TABLE3

ID TABLETYPE TABLEID DATA

When TableTYPE is table1, TABLEID should be assigned ID from TAble1
and when TableTYPE is table2, TABLEID should be assigned ID from TABLE2

Is that possible. How can i get this behavior. The whole purpose of relationships is not constraint checking as much as assigning right ID values in a dataset.

HananielI think you should create a "dummy" parent table, let's call it "D", having 1-1 relationships to Table1 and Table2 and then you should link Table3 to table "D"

Something like that:

Table D: - has a field D_Id, of "autonumber" type

Table1 has a 1-1 relationship with table D on D_Id

Table2 has a 1-1 relationship with table D on D_Id

D has a 1-M relationship with Table3, on D_Id as primary key (Table3 is the "Slave" table)

Maro

Originally posted by Hananiel
Can I have two different parents for the same child table in a dataset. The columns for which the relationships exist are the same:

For ex:

TABLE1

ID DATA

TABLE2

ID DATA

TABLE3

ID TABLETYPE TABLEID DATA

When TableTYPE is table1, TABLEID should be assigned ID from TAble1
and when TableTYPE is table2, TABLEID should be assigned ID from TABLE2

Is that possible. How can i get this behavior. The whole purpose of relationships is not constraint checking as much as assigning right ID values in a dataset.

Hananiel

multiple-column subquery

Hello all, I've got this table in a database at work, and unfortunately I'm just a temp hire so there's no persuading them to switch off access 2002 databases and move to something like sql2000 or mysql. but this is irrelevant.

I've got a table like so:

r=region
s=sku
p=period
a=amount

r s p a
1 1 2 1
1 1 1 3
1 1 3 2
1 2 3 2
1 2 2 5
1 2 3 4
2 1 ....

what I need is for each region+sku combination (so like

"SELECT DISTINCT region, sku FROM table GROUP BY region, sku"

but this is not actually my code) I need to find the record that shipped most recently (max(period)) and then within that, I need to find the most shipment (max(amount))

the question is how do I do this? I can first do a query to

"SELECT DISTINCT region, sku, max(period) FROM table GROUP BY region, sku"

and this gets my max(period), but I have to drop the amount column for this to work, because puttingin max(amount) would pull out the max amount for ALL region+sku combinations, not the max(amount) within the max(period).

I can include amount in my field selection but sql requires for a group by that if I'm not using an aggregate function on a column, but I want to select it, it has to be listed in the group by fields. This can't happen because grouping by amount prevent me from selecting the max period within the region+sku combination.

So then I could do it like so:

SELECT DISTINCT region,sku, max(amount), period FROM table GROUP BY region, sku, period

and this is my best so far, because it gets me the distinct periods within the region+sku combination and the max amount FOR each period ...

this is where I'm lost

I tried a parent query to select from this current subquery the max(period), but for obvious reasons I then have to drop my amount column again, because again group by requires I include amount in either an aggregate function or the group by conditions, and grouping by amount eliminates my ability to select the max(period)

I've had other ideas, but I've written enough, and I imagine someone should have a clue for me.

thanks so much, I'm very willing to provide more information

john h.nevermind, I got it! right when I hit submit it came to me ...

select the distinct region+sku combos, and find the max period for that ... so we'd have one record per region+sku and the only other field would be the maxperiod ... then inner join that query with the original table ON r=r, s=s, and p=p ... this will then pull all the amounts for that max period, and we can just do the max amount for that distinct region+sku+period combo, and that leaves us with the max of the original four fields, and I can then do one more inner join with my original table where r=r, s=s, p=p, and a=a to pull all the other fields I didnt' tell you all about :-P

thanks to whoever wasted their time reading me blabber!|||This should do it

SELECT p.region, p.sku, p.period, a.amount
FROM
(SELECT region, sku, MAX(period) period
FROM table
GROUP BY region, sku) p,
(SELECT region, sku, period, MAX(amount) amount
FROM table
GROUP BY region, sku, period) a
WHERE p.region = a.region
AND p.sku = a.sku
AND p.period = a.period;

Incidentally you don't need the distinct statments because the group by part returns distinct records.|||Looks we crossed in the post. Hope that helped anyway.|||brilliant, your code was a lot better ... mine was ... so many nested sql statements!

oh well, that's what happens when you have brain farts

thanks for your help, although ... when I read your post I'd already used my code to then insert the 9014 records either statement would produce into a different table ...

who says one-time-use code has to be efficient, right?

cheers, thanks

Wednesday, March 21, 2012

Multiple WHEREs in a query?

I want a query ro select the number of wins and the number of games played for a team. The pertinent columns are all in the same table and I was trying to get the information all in one query. Is something like this possible?
SELECT COUNT(*) AS Wins
FROM tbl_Schedule
WHERE Winner = 'IND'
SELECT COUNT(*) AS GamesPlayed
FROM tbl_Schedule
WHERE HomeID = 'IND' OR VisitorID = 'IND'
How would I merge the two, or can I even do that?SELECT
'Wins' = (SELECT COUNT(*) FROM tbl_Schedule WHERE Winner = 'IND'),
'GamesPlayed' = (SELECT COUNT(*) AS GamesPlayed FROM tbl_Schedule WHERE HomeID = 'IND' OR VisitorID = 'IND')|||Oh, very cool. I had no idea you could format them like that. Thanks for the help!

Multiple WHERE replacement??

Hello,
I have a table relationship that connects a list of Amenities provided by
each Property, such as:
Properties
--
PropertyID int
PropertyName nvarchar(100)
Amenities
--
AmenityID int
AmenityName nvarchar(50)
PropertyAmenities
--
PropertyID int,
AmenityID int
I want to be able to pull a list of Properties that have a certiain
Amenities, such as this static query:
SELECT PropertyID, PropertyName
FROM Properties p
WHERE 1 = 1
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 1)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 2)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 3)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 4)
QUESTION:
What is the best way to setup an efficient Stored Procedure to run this
query, based on a dynamic list of AmenityID's? I'm thinking that there's a
better way, than to dynamically build these WHERE clauses and pass them to
the SQL Server.
I might pass seven integer values on one execution, and three on another.
Thank you for any light you can shed on this.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Vito D" wrote:

> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>
>|||Since you didnt post DDL, I didn't build the tables and test this, but give
it a shot.
This is basically relational division, which you can find better examples of
if you search for posts by CELKO in this news group, or just do a google
search. I have nto really worked with this much, but I think this should
work.
Combine this with dynamic SQL to build your in clause (or populate a temp
table with the values for your in clause).
This code assumes that you have PKs on properties(PropertyID),
PropertyAmenities (PropertyID, AmenityID) and Amenities(AmenityID).
SELECT prop.PropertyID, prop.PropertyName, count(1)
FROM Properties prop
inner join PropertyAmenities propamen
on propamen.PropertyID = prop.PropertyID
where propamen.AmenityID in (1,2,3,4)
having count(1) = (select count(1)
from Amenities amen
where amen.AmenityID in (1,2,3,4)
)
"Vito D" <VdeleteDeCarlo@.ThePulse.remove.com> wrote in message
news:uVEt8btbGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>

Friday, March 30, 2012

Multi-Value Parameter Problem

Wednesday, March 28, 2012

Multitable Select

Multitable Select

Multi-Table Linked Query?

Multi-Table Linked Query?

Multi-Table Linked Query?

Multi-Table IDENTITY

Monday, March 26, 2012

Multi-Select Error, easy question

Multi-Row update trigger

MultiRow select to SingleRow output?

multiquery in a store procedure...

Friday, March 23, 2012

multiquery in a store procedure...

Multipurpose Foreign Key

Multiply Rows with T - SQL

multiplue UI Report Tables using exact same dataset ?

Multiples in a select statement

Multiple-Parents

multiple-column subquery

Wednesday, March 21, 2012

Multiple WHEREs in a query?

Multiple WHERE replacement??

multiple resultsets

Blog Archive

About Me