October 26, 2011 at 4:21 am
Hi All,
I have been asked to alter one of our reports so that the end users can select whether to include certain data. The data can only be extracted if i make a change to one of the Joins we have within the stored procedure, is it possible to put an IF or CASE statement into the code to tell it which Join to use. Please see the example below:
(Case When @parameter = 'Yes' THEN
INNER JOIN Table1 On Table1.ColumnA = Table2.ColumnA
ELSE
INNER JOIN Table2 On Table2.ColumnA = Table3.ColumnA
End)
October 26, 2011 at 4:32 am
One of two options comes into my mind:
* Dynamic SQL
* Convert to left outer join instead, and use case statement to select from either of these two with a proper where clause, eg:
select
case when isT2=1 then t2.col
when isT3=1 then t3.col
end as Col
from
t1
left outer join t2 on (t1.t1id = t2.t1id)
left outer join t3 on (t1.t1id = t3.t1id)
where
(isT2=1 and t2.col is not null) or
(isT3=1 and t3.col is not null)
October 26, 2011 at 4:37 am
Hi,
Not sure if you have tried using Left Join instead of inner join.
You can put an expression in the report cell to check for the valid value from either Table2.Name or Table3.name.
Hope this helps...
October 26, 2011 at 4:38 am
clarmatt73 (10/26/2011)
Hi All,I have been asked to alter one of our reports so that the end users can select whether to include certain data. The data can only be extracted if i make a change to one of the Joins we have within the stored procedure, is it possible to put an IF or CASE statement into the code to tell it which Join to use. Please see the example below:
(Case When @parameter = 'Yes' THEN
INNER JOIN Table1 On Table1.ColumnA = Table2.ColumnA
ELSE
INNER JOIN Table2 On Table2.ColumnA = Table3.ColumnA
End)
1. You could use dynamic sql
2. Change to LEFT JOINs with a corresponding WHERE and you can use a switch, kinda like this:
LEFT JOIN Table2 On @parameter = 'Yes' AND Table2.ColumnA = Table3.ColumnA
3. If table 1 and table 2 are "small enough" and have the same join(s) to the rest of the query, then you could use IF to populate a temp table from one or the other, then use the temp table in the query
4. Best of all - write two separate queries and choose between them.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2011 at 4:49 am
Just re-read my post, the example did not quite show how the joins are written. Please see below:
(Case When @parameter = 'Yes' THEN
INNER JOIN Table2 ON Table2.DebtCode=Table1.DebtCode AND Table2.DebtorLinkId=ISNULL(TransactionDebtorLinkId,(SELECT MIN(DebtDebtorID) FROM Table2 AS MinDebtorCode WHERE MinDebtorCode.DebtCode=Table2.DebtCode))
ELSE
(INNER JOIN Table2 ON Table2.DebtCode=Table1.DebtCode AND Table2.DebtDebtorId=(SELECT MIN(DebtDebtorID) FROM Table2 AS MinDebtorCode WHERE MinDebtorCode.DebtCode=Table2.DebtCode))
End)
Would your solution still work for this as i am actually using the same table for the join?
Apologies if this isn't clear.
October 26, 2011 at 4:55 am
Seems like the query for some kind of reporting. Although I don't like it all that much, I tend to end up with dynamic sql in such cases:
declare @sql nvarchar(max);
set @sql = 'select * from ';
set @sql = 'sys.objects';
exec sp_executesql @sql
In this way, you can generate the exact code that you need. By experience, this approach has some characteristics:
* Faster development than a lot of queries
* (Normally) better performance than a single huge query taking everything into account
* More difficult to performance tune, since each change may affect multiple queries.
October 26, 2011 at 5:16 am
clarmatt73 (10/26/2011)
Just re-read my post, the example did not quite show how the joins are written. Please see below:(Case When @parameter = 'Yes' THEN
INNER JOIN Table2 ON Table2.DebtCode=Table1.DebtCode AND Table2.DebtorLinkId=ISNULL(TransactionDebtorLinkId,(SELECT MIN(DebtDebtorID) FROM Table2 AS MinDebtorCode WHERE MinDebtorCode.DebtCode=Table2.DebtCode))
ELSE
(INNER JOIN Table2 ON Table2.DebtCode=Table1.DebtCode AND Table2.DebtDebtorId=(SELECT MIN(DebtDebtorID) FROM Table2 AS MinDebtorCode WHERE MinDebtorCode.DebtCode=Table2.DebtCode))
End)
Would your solution still work for this as i am actually using the same table for the join?
Apologies if this isn't clear.
Can you post the whole query? There's plenty of scope for improvement, and the improved query may well lend itself better to a switchable output.
INNER JOIN (SELECT DebtCode, MIN_DebtDebtorID = MIN(DebtDebtorID) FROM Table2 GROUP BY DebtCode) MinDebtorCode ON MinDebtorCode.DebtCode = Table1.DebtCode
-- or CROSS APPLY()
-- IS TransactionDebtorLinkId in Table1 or Table2?
INNER JOIN Table2 ON Table2.DebtCode = Table1.DebtCode
AND ((TransactionDebtorLinkId IS NOT NULL AND Table2.DebtorLinkId = TransactionDebtorLinkId) OR Table2.DebtorLinkId = MinDebtorCode.MIN_DebtDebtorID
INNER JOIN Table2 ON Table2.DebtCode = Table1.DebtCode
AND Table2.DebtDebtorId = MinDebtorCode.MIN_DebtDebtorID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2011 at 5:32 am
This is the from clause for the query:
FROM [Transaction] Payments
INNER JOIN Debt ON Debt.DebtCode = Payments.TransactionDebtCode
IF @JointDebtor = 'Yes'
(INNER JOIN DebtDebtor ON DebtDebtor.DebtCode=Debt.DebtCode AND DebtDebtor.DebtorLinkId=ISNULL(TransactionDebtorLinkId,(SELECT MIN(DebtDebtorID) FROM DebtDebtor AS MinDebtorCode WHERE MinDebtorCode.DebtCode=DebtDebtor.DebtCode)))
Else
(INNER JOIN DebtDebtor ON DebtDebtor.DebtCode=Debt.DebtCode AND DebtDebtor.DebtDebtorId=(SELECT MIN(DebtDebtorID) FROM DebtDebtor AS MinDebtorCode WHERE MinDebtorCode.DebtCode=DebtDebtor.DebtCode))
I know the IF won't work however i have included it to illustrate what i am trying to do
October 26, 2011 at 5:59 am
clarmatt73 (10/26/2011)
This is the from clause for the query:FROM [Transaction] Payments
INNER JOIN Debt ON Debt.DebtCode = Payments.TransactionDebtCode
IF @JointDebtor = 'Yes'
(INNER JOIN DebtDebtor ON DebtDebtor.DebtCode=Debt.DebtCode AND DebtDebtor.DebtorLinkId=ISNULL(TransactionDebtorLinkId,(SELECT MIN(DebtDebtorID) FROM DebtDebtor AS MinDebtorCode WHERE MinDebtorCode.DebtCode=DebtDebtor.DebtCode)))
Else
(INNER JOIN DebtDebtor ON DebtDebtor.DebtCode=Debt.DebtCode AND DebtDebtor.DebtDebtorId=(SELECT MIN(DebtDebtorID) FROM DebtDebtor AS MinDebtorCode WHERE MinDebtorCode.DebtCode=DebtDebtor.DebtCode))
I know the IF won't work however i have included it to illustrate what i am trying to do
IF won't work because it's for conditional execution of statements. This should work:
SELECT *
FROM [Transaction] Payments
INNER JOIN Debt ON Debt.DebtCode = Payments.TransactionDebtCode
INNER JOIN (
SELECT
seqDebtDebtorID = ROW_NUMBER() OVER(PARTITION BY DebtCode ORDER BY DebtDebtorID),
* --<<--- change this to list of columns required for output
FROM DebtDebtor
) dd ON dd.DebtCode = Debt.DebtCode
AND ((@JointDebtor <> 'Yes' AND dd.seqDebtDebtorID = 1)
OR ((TransactionDebtorLinkId IS NULL AND dd.seqDebtDebtorID = 1) OR dd.DebtorLinkId = TransactionDebtorLinkId))
- however, it's untested and it's darned difficult to figure out at a glance what it's supposed to do. I'd recommend not using it, and instead writing two statements distinguished by an IF.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2011 at 6:38 am
Thanks for that, i've tested the code and it works fine.
Thank you to everyone for their help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply