Problem with WHERE clause

I'm migrating some reports to reporting services, some of the reports use a
bunch of conditional logic to produce the desired results, I'm trying to
eliminate as much of that as possible... Here's my query... At the end of th
e
post is the original sp... My problem is when I use the "@.fdate" parameter
the query is not producing any results, if I use the sp with the same params
I get results so I know something is wrong, I'm just not seeing it, wonderin
g
if someone can help...
Thx... Dan
DECLARE --Report Parameters
@.loc char(06),
@.fdate datetime,
@.dtype char(01),
@.brand char(02)
--SET @.fdate = '07/09/2005'
SET @.dtype = 'E'
SET @.brand = 'MC'
SELECT
ordet.ORDD_ORD_NO AS ordno,
ordet.ORDD_UNIT_NO AS unitno,
ordet.PROD_NO AS prodno,
ord.ord_cus_grp AS cusgrp,
ord.ord_cus_cd AS cuscd,
ordet.ORDD_FLOOR_START_DT AS floorst,
ordet.ORDD_SICKBAY_DT AS sickbay,
ordet.ORDD_DISPATCH_DT AS dispatch,
ordet.ORDD_FLOORED_DT AS floored,
ordet.ORDD_FLOORED_CMNT AS fcomment,
ordet.ORDD_SCHEDULED_LOCATION AS location,
ordet.ORDD_EST_OFFLINE_DT AS eoffline,
ISNULL(ordet.ORDD_BASE_PRICE, 0.00) AS base,
ISNULL(ordet.ORDD_CHASSIS_PRICE, 0.00) AS chassis,
ISNULL(ordet.ORDD_OPTION_PRICE, 0.00) AS options,
cus.cus_name AS cusname,
mod.model_desc AS model,
mm.mm_corp_cd AS brand,
ord.ord_cus_ord AS salescomment
FROM dbo.HR_ORDDET ordet (nolock)
INNER JOIN dbo.ord ord (nolock) on ordet.ORDD_ORD_CO = ord.ord_company and
ordet.ORDD_ORD_NO = ord.ord_no
INNER JOIN dbo.model_master mm (nolock) on ordet.ORDD_UNIT_NO = mm.sku_cd
LEFT OUTER JOIN dbo.customer cus (nolock) on cus.cus_cd = ord.ord_cus_cd
AND cus.cus_grp_cd = ord.ord_cus_grp
LEFT OUTER JOIN dbo.model mod (nolock) on substring(ordet.ORDD_UNIT_NO,5,4)
= mod.model_code
WHERE
((@.dtype IS NULL) OR (@.dtype IS NOT NULL AND @.dtype = 'E' AND
ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) OR (@.dtype IS NOT NULL AND @.dtype =
'F' AND ordet.ORDD_FLOOR_START_DT IS NOT NULL)) AND
((@.loc IS NULL) OR (@.loc IS NOT NULL AND ordet.ORDD_SCHEDULED_LOCATION =
@.loc)) AND
( ordet.ORDD_INVOICED_DT IS NULL ) AND
((@.fdate IS NULL) OR (@.fdate IS NOT NULL AND @.dtype IS NOT NULL AND @.dtype
= 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate) OR (@.fdate IS NOT NULL AND
@.dtype IS NOT NULL AND @.dtype = 'F' AND ordet.ORDD_FLOOR_START_DT >= @.fdate)
)
AND
((@.brand IS NULL) OR (@.brand IS NOT NULL AND @.brand IN ('HR','BC','SC') AND
mm.mm_corp_cd = @.brand) OR (@.brand IS NOT NULL AND @.brand = 'MC' AND
SUBSTRING(ordet.ORDD_UNIT_NO,5,4) > '1500' AND mm.mm_corp_cd = @.brand) OR
(@.brand IS NOT NULL AND @.brand = 'MK' AND SUBSTRING(ordet.ORDD_UNIT_NO,5,4)
<
'1500' AND mm.mm_corp_cd = 'MC'))
ORDER BY
location,
cusname,
model
/*
Begin original procedure...
*/
CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
@.fdate datetime = null,
@.dtype char(01) = null,
@.brand char(02) = null)
as
create table #floor
(ordno int,
unitno char(20),
prodno int,
cusgrp varchar(06),
cuscd varchar(10),
floorst datetime,
sickbay datetime,
dispatch datetime,
floored datetime,
fcomment char(25),
location char(06),
eoffline datetime,
base money,
chassis money,
options money,
cusname varchar(30),
model varchar(40),
brand char(02),
salescomment varchar(20))
if @.loc is not null
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) on dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) on dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null))
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
and dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION = @.loc
end
else
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) ON dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) ON dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null) )
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
end
if @.fdate is not null
begin
if @.dtype = 'E'
begin
delete #floor
where eoffline >= @.fdate
end
else if @.dtype = 'F'
begin
delete #floor
where floorst >= @.fdate
end
end
update #floor
set cusname = b.cus_name
from #floor a (nolock)
inner join customer b (nolock)
on a.cuscd = b.cus_cd
and a.cusgrp = b.cus_grp_cd
update #floor
set model = b.model_desc
from #floor a (nolock)
inner join model b (nolock)
on substring(a.unitno,5,4) = b.model_code
if @.brand is null
begin
select *
from #floor
order by location, cusname, model
end
else if @.brand in ('HR','BC','SC')
begin
select *
from #floor
where brand = @.brand
order by location, cusname, model
end
else if @.brand = 'MC'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) > '1500'
order by location, cusname, model
end
else if @.brand = 'MK'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) < '1500'
order by location, cusname, model
end
GOMaybe I'm missing something, but it looks like the query returns rows
where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
If I have this right, then there's no reason to expect the query and
the procedure to return the same thing. There are probably no
rows where ORDD_EST_OFFLINE_DT >= @.fdate.
See comments inline.
SK
Alien2_51 wrote:

>I'm migrating some reports to reporting services, some of the reports use a
>bunch of conditional logic to produce the desired results, I'm trying to
>eliminate as much of that as possible... Here's my query... At the end of t
he
>post is the original sp... My problem is when I use the "@.fdate" parameter
>the query is not producing any results, if I use the sp with the same param
s
>I get results so I know something is wrong, I'm just not seeing it, wonderi
ng
>if someone can help...
>Thx... Dan
>
>DECLARE --Report Parameters
> @.loc char(06),
> @.fdate datetime,
> @.dtype char(01),
> @.brand char(02)
>--SET @.fdate = '07/09/2005'
>SET @.dtype = 'E'
> SELECT
>
[snip]

> WHERE
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) AND
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate)
>
[snip AND other stuff]
So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate

>CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
> @.fdate datetime = null,
> @.dtype char(01) = null,
> @.brand char(02) = null)
>as
>
>create table #floor
>...
>if @.loc is not null
> begin
> insert #floor
> SELECT ...
> WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not nul
l)
>
>if @.fdate is not null
> begin
> if @.dtype = 'E'
> begin
> delete #floor
> where eoffline >= @.fdate
> end
> end
>
>
>
The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate

>
>|||Simple as that... Works perfect...
Thanks Steve!!
"Steve Kass" wrote:

> Maybe I'm missing something, but it looks like the query returns rows
> where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
> returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
> because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
> If I have this right, then there's no reason to expect the query and
> the procedure to return the same thing. There are probably no
> rows where ORDD_EST_OFFLINE_DT >= @.fdate.
> See comments inline.
> SK
> Alien2_51 wrote:
>
> [snip]
>
> [snip AND other stuff]
> So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
> The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
>