COALESCE and subquery

Hello,
I want to use a subquery within my main query but only dependant on a
parameter's value. I can do this with dynamic SQL but want to try to avoid
this
Here is mySQL without the parameter
SELECT Id
FROM tblAccounts a
WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
Now if I have a parameter called @.chk_account and it evaluates to NULL - I
don't want to run the subquery at all.
Using dynamic sql would go something like:
DECLARE @.SQL varchar(1000)
SET @.SQL = 'SELECT Id FROM tblAccounts a'
IF @.chk_account IS NOT NULL
BEGIN
SET @.SQL = @.SQL +
' WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
END
EXEC(@.SQL)
I thought maybe I could use the COALESCE function in some way?
DanWhy did you give the same attribute different names? There is no such
thing as an"id" -- it has to be some particular kind of identifier.
And stop using those redudant "tbl-" prefixes; there is one and only
one data strucutre in SQL; this is not an OO language. Read ISO-11179
for details:
SELECT account_id
FROM Accounts AS A
WHERE EXISTS
(SELECT * FROM Sales AS S
WHERE a.account_id = S.account_id)
OR @.chk_account IS NULL;|||Dan,
Depending on where this is used, one or another
solution may be more efficient. Here are a couple alternatives
to Joe's suggestion:
select Id
from tblAccounts a
where @.chk_account is not null
and exists (
select * from tblSales s
where a.Id = s.AccountId
)
union all
select Id
from tblAccounts a
where @.chk_account is null
--
if @.chk_account is null
select Id from tblAccounts a
else
select Id from tblAccounts a
where exists (...)
Steve Kass
Drew University
dan-cat wrote:

>Hello,
>I want to use a subquery within my main query but only dependant on a
>parameter's value. I can do this with dynamic SQL but want to try to avoid
>this
>Here is mySQL without the parameter
>SELECT Id
>FROM tblAccounts a
>WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>Now if I have a parameter called @.chk_account and it evaluates to NULL - I
>don't want to run the subquery at all.
>Using dynamic sql would go something like:
>DECLARE @.SQL varchar(1000)
>SET @.SQL = 'SELECT Id FROM tblAccounts a'
>IF @.chk_account IS NOT NULL
>BEGIN
>SET @.SQL = @.SQL +
>' WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>END
>EXEC(@.SQL)
>I thought maybe I could use the COALESCE function in some way?
>Dan
>
>
>
>
>|||Thanks CELKO for pointing out my naming conventions - you're right I come
from an OO language background and need to shake out of the habit.
Steve: I understand in your first selection how you are using @.chk_account
IS NOT NULL to filter the records correctly. However is the query still
processing the sub-query when it doesn't need to?
To get round this in your second solution you use if... else... to define
the SQL before beforehand
Are you saying its a toss-up between processing the if...else... arguments
or including the sub-query in the SQL and filtering down the result with
@.chk_account.
Dan
"Steve Kass" wrote:

> Dan,
> Depending on where this is used, one or another
> solution may be more efficient. Here are a couple alternatives
> to Joe's suggestion:
> select Id
> from tblAccounts a
> where @.chk_account is not null
> and exists (
> select * from tblSales s
> where a.Id = s.AccountId
> )
> union all
> select Id
> from tblAccounts a
> where @.chk_account is null
> --
> if @.chk_account is null
> select Id from tblAccounts a
> else
> select Id from tblAccounts a
> where exists (...)
> Steve Kass
> Drew University
>
> dan-cat wrote:
>
>|||Dan,
I can't say whether or not the query processor will recognize that it does
not have to process the exists clause when @.chk_account is null, so the
only thing you can do is try and see what happens. The if - then choice
may be good for an ad hoc query, but I believe that if you use it in a
stored procedure, the optimizer will try to generate a single query plan
that includes both queries. Depending on the first actual parameter it
receives, it may not produce the best plan.
I wish I could say more, but it's not cut and dried, as far as I know.
SK
dan-cat wrote:
>Thanks CELKO for pointing out my naming conventions - you're right I come
>from an OO language background and need to shake out of the habit.
>Steve: I understand in your first selection how you are using @.chk_account
>IS NOT NULL to filter the records correctly. However is the query still
>processing the sub-query when it doesn't need to?
>To get round this in your second solution you use if... else... to define
>the SQL before beforehand
>Are you saying its a toss-up between processing the if...else... arguments
>or including the sub-query in the SQL and filtering down the result with
>@.chk_account.
>Dan
>
>"Steve Kass" wrote:
>
>