February 27, 2014 at 10:27 pm
Hi All,
I have got couple of tables and parameters each.
#test
#test1
@Extract
@Desc
Now based on the parameters value i want to change the join condition.
CREATE TABLE #test
(
id INT NOT NULL ,
txt VARCHAR(10) NULL
)
CREATE TABLE #test1
(
id INT NOT NULL ,
txt VARCHAR(10) NULL
)
--Insert statement
INSERT INTO #test
SELECT 1, 'TEST'
UNION
SELECT 2,'TEST'
UNION
SELECT 3,'TST'
UNION
SELECT 4, 'TEST'
UNION
SELECT 5,'TEST'
UNION
SELECT 6,'TST'
--Insert Statements
INSERT INTO #test1
SELECT 1, 'TEST'
UNION
SELECT 2,'TEST'
UNION
SELECT 3,'TST'
UNION
SELECT 4, 'TEST'
UNION
SELECT 5,'TEST'
UNION
SELECT 6,'TST'
--Conditions
If the @Extract is Yes then
i want the data from the #test table which matches with #test1 table on id field and #test1.txt = @desc else all the data from #test.
Eg: If @Extract = 'Yes'
#test.id = @test1.id and #test1.txt = @desc
Result
idtxt
1TEST
2TEST
4TEST
5TEST
If @Extract <> 'Yes'
#test.id
idtxt
1TEST
2TEST
3TST
4TEST
5TEST
6TST
I tried below query it works fine when @extract = 'Yes' but not the other way .. i am getting way too many records
DECLARE@Extract VARCHAR(10)
DECLARE @desc VARCHAR(10)
SET @desc = 'TEST'
SET @Extract = 'Yes'
SELECT t.* FROM #test t
INNER JOIN #test1 t1 ON t.id = (CASE WHEN @Extract IS NOT NULL THEN t1.id ELSE t.id END)
AND t1.txt = (CASE WHEN @Extract IS NOT NULL THEN @desc END)
Thanks in advance.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2014 at 10:50 pm
Have you considered using an IF ... ELSE block?
if @Extract = 'Yes'
BEGIN
select stuff
END
ELSE
BEGIN
select other stuff
END
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 27, 2014 at 11:02 pm
The logic already had an IF conditions and its a new functionality which i need to integrate into the solution so dont want to add up if else clause for the existing IF ELSE clauses . That select statement i provided is already inside an IF condition.
can we not achieve using single statement ?
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 28, 2014 at 8:48 am
I tried below query it works fine when @extract = 'Yes' but not the other way .. i am getting way too many records
DECLARE @Extract VARCHAR(10)
DECLARE @desc VARCHAR(10)
SET @desc = 'TEST'
SET @Extract = 'Yes'
SELECT t.* FROM #test t
INNER JOIN #test1 t1 ON t.id = (CASE WHEN @Extract IS NOT NULL THEN t1.id ELSE t.id END)
AND t1.txt = (CASE WHEN @Extract IS NOT NULL THEN @desc END)
That is because when @Extract is null you are getting a cross join.
Here is what your query would look like when @Extract IS NULL
SELECT t.* FROM #test t
INNER JOIN #test1 t1 ON t.id = t.id
Do you see the issue now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2014 at 3:08 am
Late to the party but how 'bout something like this?
DECLARE @Extract VARCHAR(3) = 'No'
,@desc VARCHAR(10) = 'TEST';
SELECT a.txt
FROM #test a
JOIN #test1 b ON a.id = b.id AND
ISNULL(NULLIF(@Extract, 'Yes'), @desc) = ISNULL(NULLIF(@Extract, 'Yes'), b.txt);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply