I am reporting off a star-schema data warehouse. I have three datasets that I
use to populate parameters, and one master dataset that uses the values from
all three to populate the report. The way I have set up the master dataset
is:
SELECT *
FROM t_policy_info
WHERE month_key = @.month_key AND
paper_desc = @.paper_desc AND
unit_desc = @.unit_desc
Matching on non-key values is very time-consuming. For the @.paper_desc and
@.unit_desc datasets, I'm only pulling back the descriptions so that they
appear in the dropdowns at the top of the report.
How should I configure the datasets so that the master query matches
WHERE month_key = @.month_key AND
paper_key= @.paper_key AND
unit_key = @.unit_key ?
Should the paper and unit datasets also contain the key fields so that they
can be matched in the master dataset? How do I prevent the user from being
prompted for the keys (which they shouldn't)?
This is really a performance issue, and while I could make it work with
straight SQL, I'm not sure how to implement it with Reporting Services.
Thanks,
MikeHave you looked at the parametere Label Field/ Value Field inputs , one of
them is what is displayed to the user and other is what is passed to the
query.?
When you create your query for the main dataset along with the month also
pass the paper_key and unit_key parameters, as input by the user.
HTH
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:8549E9F8-0F83-40A6-9737-864C4C302045@.microsoft.com...
>I am reporting off a star-schema data warehouse. I have three datasets that
>I
> use to populate parameters, and one master dataset that uses the values
> from
> all three to populate the report. The way I have set up the master dataset
> is:
> SELECT *
> FROM t_policy_info
> WHERE month_key = @.month_key AND
> paper_desc = @.paper_desc AND
> unit_desc = @.unit_desc
> Matching on non-key values is very time-consuming. For the @.paper_desc and
> @.unit_desc datasets, I'm only pulling back the descriptions so that they
> appear in the dropdowns at the top of the report.
> How should I configure the datasets so that the master query matches
> WHERE month_key = @.month_key AND
> paper_key= @.paper_key AND
> unit_key = @.unit_key ?
> Should the paper and unit datasets also contain the key fields so that
> they
> can be matched in the master dataset? How do I prevent the user from being
> prompted for the keys (which they shouldn't)?
> This is really a performance issue, and while I could make it work with
> straight SQL, I'm not sure how to implement it with Reporting Services.
> Thanks,
> Mike
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
joining 2 tables onto one
Hi, I'm trying to do something really simple but am not coming up with the
right answer. I have master table A on which I am trying to outer join
(separately) tables B and C. So it should look like this:
Table A LEFT OUTER JOIN Table B
Table A LEFT OUTER JOIN Table C
I tried doing:
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
But it looks like it's joining C onto B and A, instead of joining them
separately. How do I join them separately?
Thanks.Please elaborate on "looks like it's joining C onto B and A". What do you me
an?
ML
http://milambda.blogspot.com/|||I hate to say this, but I would recommend using the View developer screen,
which allows us to construct queries like in Access. Go to 'Views' and
select 'create a new view'. Add the tables you need. Don't even necessaril
y
save the view, just take the sql and use it in your code. It shouldn't be a
crutch but can be very instructional in how to code joins.
The order you put your tables into the view, and the order inwhich you
connect them will determin how 'clean' the sql it writes will be.
Also, for clarity there is always the dreaded *= syntax. While incredible
easy to read, some here will probably get very upset that I mentioned it.
Its supposed to go away, but its sooo darn easy to read. Don't tell anyone
you heard it from me. There are rumours that it is non-deterministic or
something, I've tested it a million times and had no problems yet.
ML - what is your take on *=, any opinions?
"Ryan D" wrote:
> Hi, I'm trying to do something really simple but am not coming up with the
> right answer. I have master table A on which I am trying to outer join
> (separately) tables B and C. So it should look like this:
> Table A LEFT OUTER JOIN Table B
> Table A LEFT OUTER JOIN Table C
> I tried doing:
> select ...
> FROM Table A
> LEFT JOIN Table B
> on A.Name = B.Name
> LEFT JOIN Table C
> on A.Name = C.Name
> But it looks like it's joining C onto B and A, instead of joining them
> separately. How do I join them separately?
> Thanks.|||I thought I made it very clear, but table B includes a where statement
specifying one condition and table C specifies a contrasting where statement
specifying another condition. I am comparing B and C against the same table
A. So I guess I'm asking how to do is (A intersects B) union with (A
intersects C).
"ML" wrote:
> Please elaborate on "looks like it's joining C onto B and A". What do you
mean?
>
> ML
> --
> http://milambda.blogspot.com/|||May be this is
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
where B.field1 = ' xxx'
or c.field1 = 'yyy'
Regards
Amish|||The *= /=* join syntax still works, I've heard it's deprecated, and I've als
o
heard it isn't. I don't really see what would make it more readable than the
more contemporary qualified outer join syntax, but then again it just may be
in the eye of the beholder. I personally prefer explicit and qualified joins
,
plus I format the query to increase readability.
E.g.:
select ...
from A
inner join B
on B.Key = A.Key
lef join C
on C.Key = B.Key
right join D
on D.Key = A.Key
As for your query, Ryan, please post DDL, sample data and expected results.
It's very difficult to come up with a solution without seeing the actual dat
a.
ML
http://milambda.blogspot.com/|||> The *= /=* join syntax still works, I've heard it's deprecated, and I've alsoed">
> heard it isn't.
In SQL Server 2005, it will not work unless you set the database to compatib
ility level 80 or lower.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0D4C7472-613F-4101-9F7B-E7E12E36F035@.microsoft.com...
> The *= /=* join syntax still works, I've heard it's deprecated, and I've a
lso
> heard it isn't. I don't really see what would make it more readable than t
he
> more contemporary qualified outer join syntax, but then again it just may
be
> in the eye of the beholder. I personally prefer explicit and qualified joi
ns,
> plus I format the query to increase readability.
> E.g.:
> select ...
> from A
> inner join B
> on B.Key = A.Key
> lef join C
> on C.Key = B.Key
> right join D
> on D.Key = A.Key
>
> As for your query, Ryan, please post DDL, sample data and expected results
.
> It's very difficult to come up with a solution without seeing the actual d
ata.
>
> ML
> --
> http://milambda.blogspot.com/|||Good to know. Thank you, Tibor. Not that I've ever used it. :)
ML
http://milambda.blogspot.com/|||Old-style outer joins is a mess and I'm happy MS are finally on the road to
get rid of it. I don't
think I failed up to this point to scare anyone off from using old style out
er joins with below
example. :-)
USE pubs
GO
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanst?ende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7DC3AF71-F65F-485E-BEE8-2BAC34970ECF@.microsoft.com...
> Good to know. Thank you, Tibor. Not that I've ever used it. :)
>
> ML
> --
> http://milambda.blogspot.com/|||Oh, the humanity!
Don't tell me this is by design...? It really is scary.
This actually makes me feel really good for always using the new join
syntax. :)
ML
http://milambda.blogspot.com/
right answer. I have master table A on which I am trying to outer join
(separately) tables B and C. So it should look like this:
Table A LEFT OUTER JOIN Table B
Table A LEFT OUTER JOIN Table C
I tried doing:
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
But it looks like it's joining C onto B and A, instead of joining them
separately. How do I join them separately?
Thanks.Please elaborate on "looks like it's joining C onto B and A". What do you me
an?
ML
http://milambda.blogspot.com/|||I hate to say this, but I would recommend using the View developer screen,
which allows us to construct queries like in Access. Go to 'Views' and
select 'create a new view'. Add the tables you need. Don't even necessaril
y
save the view, just take the sql and use it in your code. It shouldn't be a
crutch but can be very instructional in how to code joins.
The order you put your tables into the view, and the order inwhich you
connect them will determin how 'clean' the sql it writes will be.
Also, for clarity there is always the dreaded *= syntax. While incredible
easy to read, some here will probably get very upset that I mentioned it.
Its supposed to go away, but its sooo darn easy to read. Don't tell anyone
you heard it from me. There are rumours that it is non-deterministic or
something, I've tested it a million times and had no problems yet.
ML - what is your take on *=, any opinions?
"Ryan D" wrote:
> Hi, I'm trying to do something really simple but am not coming up with the
> right answer. I have master table A on which I am trying to outer join
> (separately) tables B and C. So it should look like this:
> Table A LEFT OUTER JOIN Table B
> Table A LEFT OUTER JOIN Table C
> I tried doing:
> select ...
> FROM Table A
> LEFT JOIN Table B
> on A.Name = B.Name
> LEFT JOIN Table C
> on A.Name = C.Name
> But it looks like it's joining C onto B and A, instead of joining them
> separately. How do I join them separately?
> Thanks.|||I thought I made it very clear, but table B includes a where statement
specifying one condition and table C specifies a contrasting where statement
specifying another condition. I am comparing B and C against the same table
A. So I guess I'm asking how to do is (A intersects B) union with (A
intersects C).
"ML" wrote:
> Please elaborate on "looks like it's joining C onto B and A". What do you
mean?
>
> ML
> --
> http://milambda.blogspot.com/|||May be this is
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
where B.field1 = ' xxx'
or c.field1 = 'yyy'
Regards
Amish|||The *= /=* join syntax still works, I've heard it's deprecated, and I've als
o
heard it isn't. I don't really see what would make it more readable than the
more contemporary qualified outer join syntax, but then again it just may be
in the eye of the beholder. I personally prefer explicit and qualified joins
,
plus I format the query to increase readability.
E.g.:
select ...
from A
inner join B
on B.Key = A.Key
lef join C
on C.Key = B.Key
right join D
on D.Key = A.Key
As for your query, Ryan, please post DDL, sample data and expected results.
It's very difficult to come up with a solution without seeing the actual dat
a.
ML
http://milambda.blogspot.com/|||> The *= /=* join syntax still works, I've heard it's deprecated, and I've alsoed">
> heard it isn't.
In SQL Server 2005, it will not work unless you set the database to compatib
ility level 80 or lower.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0D4C7472-613F-4101-9F7B-E7E12E36F035@.microsoft.com...
> The *= /=* join syntax still works, I've heard it's deprecated, and I've a
lso
> heard it isn't. I don't really see what would make it more readable than t
he
> more contemporary qualified outer join syntax, but then again it just may
be
> in the eye of the beholder. I personally prefer explicit and qualified joi
ns,
> plus I format the query to increase readability.
> E.g.:
> select ...
> from A
> inner join B
> on B.Key = A.Key
> lef join C
> on C.Key = B.Key
> right join D
> on D.Key = A.Key
>
> As for your query, Ryan, please post DDL, sample data and expected results
.
> It's very difficult to come up with a solution without seeing the actual d
ata.
>
> ML
> --
> http://milambda.blogspot.com/|||Good to know. Thank you, Tibor. Not that I've ever used it. :)
ML
http://milambda.blogspot.com/|||Old-style outer joins is a mess and I'm happy MS are finally on the road to
get rid of it. I don't
think I failed up to this point to scare anyone off from using old style out
er joins with below
example. :-)
USE pubs
GO
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanst?ende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7DC3AF71-F65F-485E-BEE8-2BAC34970ECF@.microsoft.com...
> Good to know. Thank you, Tibor. Not that I've ever used it. :)
>
> ML
> --
> http://milambda.blogspot.com/|||Oh, the humanity!
Don't tell me this is by design...? It really is scary.
This actually makes me feel really good for always using the new join
syntax. :)
ML
http://milambda.blogspot.com/
Subscribe to:
Posts (Atom)