April 22, 2008 at 2:51 pm
It doing the insert regardles of a empty record set. I am a bit confused now, in my brain I would think it needs to look something like this:
IF @LineFilter = ('Only Line 1')
(SELECT * FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndDt)
ELSE -- This means the record set was empty so insert the row consultant row but it doesn't do the insert.
BEGIN INSERT INTO #DlFiltered (ConsultantID, ConsultantName)
(Select consultantid, firstname + ' ' + Lastname as ConsultantName
FROM consultant Where ConsultantID = @COnsultantID)
END
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 22, 2008 at 2:59 pm
Try this:
IF @LineFilter = ('Only Line 1')
BEGIN
(SELECT * FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndDt)
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO #DlFiltered (ConsultantID, ConsultantName)
Select consultantid, firstname + ' ' + Lastname as ConsultantName
FROM consultant Where ConsultantID = @COnsultantID
SELECT * FROM #DLFiltered
END
END
END
April 22, 2008 at 3:21 pm
Lets see if I can explain the what I am seeing ( I am now getting two records set back, one that is empty (expected) and one with all the rows from#DLFiltered.
The #DLFiltered table does have rows in it but those rows do not meet the selection filter:
SELECT * FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndD
So ther recordset come back empty although the #DlFiltered table is not empty.
So when this executes:
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO #DlFiltered (ConsultantID, ConsultantName)
Select consultantid, firstname + ' ' + Lastname as ConsultantName
FROM consultant Where ConsultantID = @COnsultantID
SELECT * FROM #DLFiltered
END
It is returning all the rows in #DLFiltered plus 4 inserted rows with the consulant ID and Name.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 23, 2008 at 1:16 pm
I think I almost have this. Current code:
IF @LineFilter = ('Only Line 1')
BEGIN
SELECT DIstinct * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @StartPeriodDate AND @EndPeriodDate
Or (COnsultantID IS NULL)
IF @LineFilter = ('Only Line 2')
BEGIN
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
OR (COnsultantID IS NULL)
IF @LineFilter = ('Only Line 3')
BEGIN
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 3
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
IF @LineFilter = ('Lines 1 and 2')
BEGIN
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
OR (COnsultantID IS NULL)
IF @LineFilter = ('Lines 1, 2, and 3')
BEGIN
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 3
AND EffectiveDate BETWEEN @StartPeriodDate AND @EndPeriodDate
OR (COnsultantID IS NULL)
END
END
END
END
END
ELSE Select Distinct OrigConsID, ORigConsName
FROM #DlFiltered WHERE ConsultantID IS NULL
What is happening is it is picking up the first filter (Only Line 1) and ignoring the subsequent filters such as Line 1, 2, and 3. So if the parameter for Lines 1, 2, and 3 is passed it the script is going directly to the ELSE. What am I doing wrong?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 23, 2008 at 1:22 pm
You need to use 'IF .. ELSE IF ... ELSE IF ... ELSE'
IF @LineFilter = ('Only Line 1')
SELECT DIstinct * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @StartPeriodDate AND @EndPeriodDate
Or (COnsultantID IS NULL)
ELSE IF @LineFilter = ('Only Line 2')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
OR (COnsultantID IS NULL)
ELSE IF @LineFilter = ('Only Line 3')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 3
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
ELSE IF @LineFilter = ('Lines 1 and 2')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
OR (COnsultantID IS NULL)
ELSE IF @LineFilter = ('Lines 1, 2, and 3')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 3
AND EffectiveDate BETWEEN @StartPeriodDate AND @EndPeriodDate
OR (COnsultantID IS NULL)
ELSE Select Distinct OrigConsID, ORigConsName
FROM #DlFiltered WHERE ConsultantID IS NULL
Dave Novak
April 23, 2008 at 2:15 pm
This almost works but it's ignoring the last ELSE:
ELSE Select Distinct OrigConsID, ORigConsName
FROM #DlFiltered WHERE ConsultantID IS NULL
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 24, 2008 at 7:06 am
What is being passed into the variable '@LineFilter' that it is ignore the final else?
April 24, 2008 at 10:02 am
it seems like it's inserting a blank row for each of the Filtere statements that it passes through. So I end up with for empty rows instead of just one.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply