Conditionally Send Email Subscription

Hi All,
I've written reports and deployed them with data-driven subcriptions. I
have a new requirement and would like to know if there is a solution I
can impliment to conditionally send an email to a recipient "ONLY" if
the report to this specific recipient contains data. If I'm correct,
the data-driven query will send an email to each in the result set of
the query whether the report contains data or not.
Am I on the right track and is this possible?
Thank in advance!
Kind regards - FredHi Igor,
> One solution would be if you can implement your data-driven (DD) query in a
> way that returns a user only if data for this user exists. Alternative is
> writing a custom delivery - then you have a complete control over the send
> logic. Otherwise you're right: email is sent to each in the result set of
> the DD query whether the report contains data or not.
I'll most liekly go with the DD query up front as a better solution.
Just wanted to see if there was something I'm missing. Thanks for your
input.
Regards - Fred

Conditional query results

Hello everybody,

After several attempts of writing the query, I had to post my requirement in the forum.

Here is what I have, what I need and what I did.

Table A
Col1Col2
1Nm1
2Nm2
3Nm3

Table B
Col1Col2
10100
20200

Table C
Col1 (A.Col1)Col2 (B.Col1)
110
210

Table D
Col1 (A.Col1)Col2
1Value1
2Value2

I need results based on below criteria,

1.
Criteria - B.Col2 = 100
Resultset
A.Col1D.Col1
1Value1
2Value2

2.
Criteria - B.Col2 =""
A.Col1D.Col1
1Value1
2Value2
3NULL

3.
Criteria - B.Col2 =200
Empty resultset

Here is the query I tried, but looks its not working. Probably there is a better way to do this.

DDL and DML statements:
create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))

insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')

insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)

insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)

insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')

select
a.a1
, d.d2
from #tab1 a
left join #tab3 b
on a.a1 = b.c1
left join #tab2 c
on b.c2 = c.b1
left join #tab4 d
on a.a1 = d.d1
where
c.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 d
where a.a1 = d.d1
and c.b2 = [100 or 200 or ''] )

The

above query works well to give results for Criteria 1 and Criteria 2,

but doesn't return for ''. I couldn't manage cracking the solution. I

shall try once again, but meanwhile if anyone could help me in this,

that would be great.

Thanks.

Change the select query as follow as...

1. You should convert your Integer Column into Varchar to compare with '',

2. You should apply the Isnull function to match the ''

I hope it will work for you...

select
a.a1, d.d2
from #tab1 a
left join #tab3 b on a.a1 = b.c1
left join #tab2 c on b.c2 = c.b1
left join #tab4 d on a.a1 = d.d1
where
Isnull(Convert(varchar,c.b2),'') in (100 ,200,'')
or exists
(select 1 from #tab4 d where a.a1 = d.d1 and Isnull( Convert(varchar,c.b2),'') in (100, 200 , ''))