I am building a report off of a poorly-designed table and need to compare a single parameter against 4 possible values, each stored in a separate field in the database (hence, poorly designed).
The values I have are N, L, R or C. Each of their respective columns contains either the letter after which the column is named, or null.
I've been tasked with adding a parameter for selecting the values individually. so far, there has been no request for a multi-value selection.
I've built the parameter and hard-coded the available values. The default is set for null, but doesn't default to "All" as the other parms in the report do currently.
I've added a clause in the where of the query that if the parameter is = to FieldL or Field R, etc. that it should be included in the report.
When the report runs, it brings back data for all of the 4 values, not just the one selected.
How can I resolve this issue?
Can you post your query? In the meantime, perhaps something like this would work:
--setup sample data DECLARE @paramType char(1); SET @paramType = 'L'; DECLARE @data TABLE ( N char(1) ,L char(1) ,R char(1) ,C char(1) ,Rowdata int ); INSERT @data(N,L,R,C,Rowdata) VALUES (NULL,NULL,NULL,'C',345) ,(NULL,NULL,'R',NULL,123) ,(NULL,'L',NULL,NULL,234) ,('N',NULL,NULL,NULL,789) ,(NULL,'L',NULL,NULL,999); --query with COALESCE SELECT Rowdata FROM @data WHERE COALESCE(N,L,R,C) = @paramType
This is the current version...
Customer."CustomerNumber", Customer."CustomerOrganizationName", Agency."AgencyNumber",
Agency."AgencyLegalName", AgencyIn."EORenewalDate", AgencyIn."FidelityBondInsuranceRenewalDate",
Location."LocationNumber", Location."LocationAddress1", Location."LocationCityName",
Location."LocationStateCode", Location."IsRemittingOffice", vwOtherU."OtherUW_List", Customr2."StateCode",
Customr2."AgentIdentificationNo", Region."RegionName", vwSalesR."RepName", rptWatch."StartHistory",
rptWatch."LastAuditDate", rptWatch."ExcludeNewAgent", rptWatch."AgentSplit", rptWatch."MTDGross",
rptWatch."MTDNet", rptWatch."YTDGross", rptWatch."YTDNet", isnull(rptWatch."N",'') as N,
isnull(rptWatch."R",'') as R ,isnull(rptWatch."L",'') as L,isnull(rptWatch."C",'') as C,
rptWatch.WatchCodes", rptWatch."YTDNetPerLiab", CustSTyp."CustSubTypeCode"
dbo.Customer Customer inner join dbo.Agency Agency on Customer."CustomerID" = Agency."CustomerID"
left join "dbo"."AgencyInsurance" AgencyIn on Agency."AgencyID" = AgencyIn."AgencyId"
inner join dbo.Location Location on Agency."AgencyID" = Location."AgencyID"
left join "dbo"."vwOtherUWCode" vwOtherU on Agency."AgencyID" = vwOtherU."AgencyID"
inner join dbo.CustomerAccount Customr2 on Location."LocationID" = Customr2."LocationID"
inner join dbo.Region Region on Customr2."RegionID" = Region."RegionID"
inner join dbo.vwSalesReps vwSalesR on Customr2."AgencyRepID" = vwSalesR."RepID"
inner join "dbo"."rptWatchCodes" rptWatch on Customr2."CAID" = rptWatch."CAID"
inner join "dbo"."CustomerSubType" CustSTyp on Customr2."CustomerSubTypeID" = CustSTyp."CustSubTypeID"
((rptWatch."SaveAs" = @month and Customr2."CustomerTypeID" <> 4 and Customer."CustomerNumber" <> '10102'
and Customr2."RegionID" not in (10,11) and Customr2."AgencyStatusID" = 1))
and not (n is null and r is null and l is null and c is null) and excludenewagent=@excludenewagent
and customer.CustomerID = Coalesce(@CustomerNumber, customer.CustomerID)
and @WatchCode = rptWatch.R or @WatchCode = rptWatch.L or @WatchCode = rptWatch.N or @WatchCode = rptWatch.C
Region."RegionName", Customer."CustomerOrganizationName", Agency."AgencyNumber", Location."LocationCityName",
The line in bold print is how I was trying to compare the parameter to the values in the individual fields. There is another field in the database (WatchCodes) which is a concatenation of all of the values by record (for example: N,L,C or R,L. That would place the values into one field for comparison, but then it is an issue of parsing the values.
What is the best way for me to jump? (off the bridge is not the right answer....)
I'm curious to know if making that where clause part of the join statement instead of putting it in the where clause would make a difference:
inner join "dbo"."rptWatchCodes" rptWatch on (Customr2."CAID" = rptWatch."CAID" AND (rptWatch.N = @WatchCode OR rptWatch.L = @WatchCode OR rptWatch.R = @Watchcode OR rtpWatch.C = @WatchCode))
Ok, I tried putting the code in the join clause, but I'm getting an error about the variable already being declared.
I removed the parameter from the report and verified no other reference to the parameter in the query, but the error persists.
Am I missing something?