Is there a better way to do this?

I have a stored proc that returns information depending on the
particular column slelected by a passed in parameter.
For Instance:
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN T1.Rep2
WHEN @.RepType = 3 THEN T1.Rep3
END = @.Rep
The problem is if I need to join another table that may need to use the
"Rep" columns. Right now, I end up repeating the case statement
wherever I need to compare "Rep". Is there a better way to do this?
SteveYes there is a better solution: normalize the design. Having Rep1...RepN doe
s
not satisfy First Normal Form and is the reason your having difficulty with
your
query. Add a table something akin to:
Create Table Table1Reps
(
Table1Id Int References Table1(Id)
, RepId Int References ?(?)
, Constraint PK_Table1Reps Primary Key (Table1Id, RepId)
)
Now your query is significantly easier:
Select F1...Fn
From Table1 As T1
Join Table2 As T2
On T1.Id = T2.Id
Join Table1Reps As T1Reps
On T1Reps.Table1Id = T1.Id
And T1Reps.RepId = @.Rep
If there really are "types" of reps, then simply add a column to the Table1R
eps
table.
HTH
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116878895.468831.52980@.g49g2000cwa.googlegroups.com...
>I have a stored proc that returns information depending on the
> particular column slelected by a passed in parameter.
> For Instance:
>
> Passed in parameters: @.Rep = 876 @.RepType = 2
> SELECT *
> FROM Table1 T1
> INNER JOIN Table2 T2
> ON T1.Id = T2.Id
> WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
> The problem is if I need to join another table that may need to use the
> "Rep" columns. Right now, I end up repeating the case statement
> wherever I need to compare "Rep". Is there a better way to do this?
>
> Steve
>|||>> Right now, I end up repeating the case statement wherever I need to
Represent rep_types as a single attribute and your queries would be much
simpler. It might also help in enforcing integrity constraints depending on
your business requirements.
Anith|||On 23 May 2005 13:08:15 -0700, steve wrote:

>I have a stored proc that returns information depending on the
>particular column slelected by a passed in parameter.
>For Instance:
>
>Passed in parameters: @.Rep = 876 @.RepType = 2
>SELECT *
>FROM Table1 T1
>INNER JOIN Table2 T2
>ON T1.Id = T2.Id
>WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
>The problem is if I need to join another table that may need to use the
>"Rep" columns. Right now, I end up repeating the case statement
>wherever I need to compare "Rep". Is there a better way to do this?
Hi Steve,
You could use a derived table:
SELECT Column list
FROM (SELECT Column list
, CASE @.RepType
WHEN 1 THEN Rep1
WHEN 2 THEN Rep2
WHEN 3 THEN Rep3
END AS Rep
FROM Table1) AS T1
INNER JOIN Table2 AS T2
ON T2.Id = T2.Id
WHERE T1.Rep = @.Rep
However, you might also consider normalizing your data - this query
rs of a repeating group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for the quick response. I have thought about normalizing
this data, there is a condition that has prevented me from doing this.
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN ISNULL(T1.Rep2, T1.Rep1)
WHEN @.RepType = 3 THEN ISNULL(T1.Rep3, T1.Rep1)
END = @.Rep
If the data is normalized, it would take a second query to lookup an
alternate rep. I suppose my other option is to pre-populate the null
values, if necessary. BTW, what is the performance hit on a query like
this?
Steve|||<snip>

> If the data is normalized, it would take a second query to lookup an
> alternate rep. I suppose my other option is to pre-populate the null
> values, if necessary. BTW, what is the performance hit on a query like
> this?
There are other ways of solving the problem of which you speak. The solution
depends greatly on the logic used to substitute a Rep. Regardless, normalizi
ng
the data is still the best solution for this problem.
Thomas|||Looks like a pair of outer joins would do it but that's just guesswork
without a better spec. Normalize the data, that's a given, then post a
fuller description of the problem: DDL, sample data, required results.
David Portas
SQL Server MVP
--|||
David Portas wrote:
> Looks like a pair of outer joins would do it but that's just guesswork
> without a better spec. Normalize the data, that's a given, then post a
> fuller description of the problem: DDL, sample data, required results.
> --
> David Portas
> SQL Server MVP
> --
Okay, so let's say I do normalize the data into a table. Like this:
Store# RepType Value
1 1 765
1 2 546
2 1 345
2 3 876
How exactly do I tell that Store #2 does not have a Rep of Type 2, and
substitute it with the value from RepType 1' All that I can see
happening is that Store #2 gets dropped in the join regardless of inner
or outer.
Steve|||Is RepType = 1 *always* substituted if a given RepType does not exist? If th
at
is the following would work:
Select Stores.[Store#]
, Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
From Stores
Left Join StoreReps As SR
On SR.[Store#] = Stores.[Store#]
And SR.RepValue = @.Rep
And SR.RepType = @.RepType
Left Join (
Select SR1.[Store#], SR1.RepValue
From StoreReps As SR1
Where SR1.RepType = 1
) As SubstituteReps
On Stores.[Store#] = SubstituteReps.[Store#]
This query assumes that each Store can only have one rep of a given type. Ye
s,
this requires a subquery, but if there is an index on Store# and RepType, th
is
will be very fast.
BTW, I'd avoid using a "#" in a column name. Doing so generally requires tha
t
you encase the column name in square brackets making it more of a pain to co
de
against.
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
>
> David Portas wrote:
>
> Okay, so let's say I do normalize the data into a table. Like this:
> Store# RepType Value
> 1 1 765
> 1 2 546
> 2 1 345
> 2 3 876
>
> How exactly do I tell that Store #2 does not have a Rep of Type 2, and
> substitute it with the value from RepType 1' All that I can see
> happening is that Store #2 gets dropped in the join regardless of inner
> or outer.
>
> Steve
>|||
Thomas Coleman wrote:
> Is RepType = 1 *always* substituted if a given RepType does not exist? If
that
> is the following would work:
> Select Stores.[Store#]
> , Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
> From Stores
> Left Join StoreReps As SR
> On SR.[Store#] = Stores.[Store#]
> And SR.RepValue = @.Rep
> And SR.RepType = @.RepType
> Left Join (
> Select SR1.[Store#], SR1.RepValue
> From StoreReps As SR1
> Where SR1.RepType = 1
> ) As SubstituteReps
> On Stores.[Store#] = SubstituteReps.[Store#]
> This query assumes that each Store can only have one rep of a given type.
Yes,
> this requires a subquery, but if there is an index on Store# and RepType,
this
> will be very fast.
> BTW, I'd avoid using a "#" in a column name. Doing so generally requires t
hat
> you encase the column name in square brackets making it more of a pain to
code
> against.
>
> Thomas
>
> "steve" <stevem2112@.gmail.com> wrote in message
> news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
You are correct in assuming that RepType 1 will always be the
substitute, however it is only a substitute for RepType 2. The rest of
the reptypes are not substituted at all. If they are null, they are
null.