March 12, 2007 at 2:41 pm
Is it possible or is there a way to do an alternate join
for example
if an input parm is NOTpresent i want to do
LEFT
OUTER JOIN TRADE_ALLOCATION TA ON TR.TRADE_ID = TA.TRADE_ID
if an input parm is Present i want to do
LEFT OUTER JOIN TRADE_ALLOCATION TA ON TR.TRADE_ID = TA.TRADE_ID AND TA.FUND_ID = @PARM1
is there a way to do this with out 2 separate queries
This is part of fairly large join of 8 tables, but this is the only statement that would need to be modified.
thanks
ben
March 12, 2007 at 2:46 pm
If @Parm1 is NULL when not supplied, then the following will accomplish your query in 1 statement:
LEFT OUTER JOIN TRADE_ALLOCATION TA
ON TR.TRADE_ID = TA.TRADE_ID AND
TA.FUND_ID = ISNULL(@PARM1, TA.FUND_ID)
The caveat here is that use of the IsNull() function may cause the optimiser not to use indexes. Check the query plan.
March 13, 2007 at 3:17 am
The ISNULL function might not work. A better option will be Coalesce:
LEFT OUTER JOIN TRADE_ALLOCATION TA
ON TR.TRADE_ID = TA.TRADE_ID AND
TA.FUND_ID = COALESCE(@PARM1, TA.FUND_ID)
March 13, 2007 at 3:35 am
Sorry I got confused with the ISNULL function in SSIS. Both functions will work.
March 13, 2007 at 7:36 am
thank you guys for getting back to me so quickly it is appreciated.
the statement
isnull(@FUND_ID, TA.FUND_ID)) does not actually eliminate non-wanted rows from being returned is that correct ???
... what i found was that when @Fund_id is set to '1001' the column Fund_ID contains rows with Fund_id containing 1001 and rows that are not Fund_id 1001 are set to Null... is this correct. ??. then i should use the Where clause should be used to eliminate the NULL valued rows... Is this correct ???
thanks again ben
March 15, 2007 at 9:39 am
Ben
Read up in Books Online about what the ISNULL and COALESCE functions do. In this instance they are equivalent.
If @FUND_ID is not null, the last line of your query will effectively be
AND TA.FUND_ID = @FUND_ID
and if @FUND_ID is null, it will effectively be
AND TA.FUND_ID = TA.FUND_ID
John
March 16, 2007 at 5:43 am
Be careful with such query designs. @FUND is a
variable, and its value would be known at runtime only, so SQL can not assume anything about its value and would try to create a generalized plan.
e.g. Assume that, If the TA.FUND_ID = @FUND_ID
clause is present, SQL optimizer choose index seek and when TA.FUND_ID = @FUND_ID is not present then optimizer chooses index scan.
Think of a situation where first run of this query was done with no @FUND parameter. so the plan in cache would be of a scan. so subsequent plans would also choose same plan from cache and suffer.
possible options:
-> Dynamic SQL (be carefull for SQL injections)
-> Multiple queries (requires extra coding, but better solution)
-> create Procedure with recompile or in SQL 2005 use statement level recompile. (puts an extra overhead on SQL for compilation)
-> if majority of your proc calls will be used for a type of value (null or not null), consider using OPTIMIZED FOR clause in SQL 2005.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply