February 26, 2016 at 3:45 am
Hi,
In the report I have created, there is an option for the user to enter optional free text for searching. The problem is the report displays matching rows for the related parameter value but at the same time also shows other rows with NULL values. The report has other optional free text search parameters and 3 dropdown parameters as well.
I have used the following code in the where clause in SP for the free text search:
(CONSIGNEE_DUNS LIKE '%' + @conduns + '%') OR (CONSIGNEE_DUNS IS NULL) OR (CONSIGNEE_DUNS = '')
Please also find the screenshot from the report attached. Also, please find the SQL stored procedure that runs the report below.
ALTER PROCEDURE [dbo].[sp_IMPORT_Cust_Handling]
@FROM_DT DATETIME,
@TO_DT DATETIME,
@shipduns NVARCHAR(4000),
@conduns NVARCHAR(4000),
@notduns NVARCHAR(4000),
@shipname NVARCHAR(4000),
@conname NVARCHAR(4000),
@notname NVARCHAR(4000),
@ITfromfacility NVARCHAR(4000),
@ITtofacility NVARCHAR(4000),
@trade NVARCHAR(4000),
@contype NVARCHAR(4000),
@bltype NVARCHAR(4000)
AS
SELECT DISTINCT --MG_BILL_OF_LADING.BL_ID,
MG_BILL_OF_LADING.BL_NUM
,MG_BILL_OF_LADING.POR_LOCATION_CD
,MG_BILL_OF_LADING.POL_LOCATION_CD
,MG_BILL_OF_LADING.TRANSHIPMENT_PORT_CD
,MG_BILL_OF_LADING.POD_LOCATION_CD
,MG_BILL_OF_LADING.PDL_LOCATION_CD
,MG_BILL_OF_LADING.POI_LOCATION_CD
,MG_BILL_OF_LADING.BL_OK_FLG
,MG_BILL_OF_LADING.DELETED_FLG
,MG_BILL_OF_LADING.DELETED_DT
,MG_BILL_OF_LADING.CREATE_DT
,MG_BILL_OF_LADING.SC_NUM
,MG_BILL_OF_LADING.TRADE_CD
,SHIPPER
,CONSIGNEE
,NOTIFY
,SHIPPER_DUNS
,CONSIGNEE_DUNS
,NOTIFY_DUNS
,SHIPPER_CUSTNAME
,CONSIGNEE_CUSTNAME
,NOTIFY_CUSTNAME
,(MG_BL_CONTAINER.CONTAINER_PREFIX + MG_BL_CONTAINER.CONTAINER_NUM + MG_BL_CONTAINER.CONTAINER_CHECK) as Container_Info
,MG_BL_CONTAINER.CONTAINER_PREFIX
,MG_BL_CONTAINER.CONTAINER_NUM
,MG_BL_CONTAINER.CONTAINER_CHECK
,MG_BL_CONTAINER.OUTER_PACKAGE_CNT
,MG_BL_CONTAINER.GROSS_WGT
,MG_BL_CONTAINER.CONTAINER_TYPE_CD
,MG_BL_CONTAINER.CONTAINER_SIZE_CD
,MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD
,(MG_BL_ITINERARY.VESSEL_CD + MG_BL_ITINERARY.VOYAGE_CD + MG_BL_ITINERARY.LEG_CD) as VessVoyLeg
,MG_BL_ITINERARY.VESSEL_CD
,MG_BL_ITINERARY.VOYAGE_CD
,MG_BL_ITINERARY.LEG_CD
,MG_BL_ITINERARY.FROM_LOCATION_CD
,MG_BL_ITINERARY.ETD_DT
,MG_BL_ITINERARY.ETA_DT
,MG_BL_ITINERARY.TO_LOCATION_CD
,CASE WHEN MG_BL_CONTAINER.CONTAINER_SIZE_CD = '20' THEN 1 WHEN MG_BL_CONTAINER.CONTAINER_SIZE_CD IN ('40','45') THEN 2 END AS TEU,
MG_BL_ITINERARY.FROM_FACILITY_CD,
MG_BL_ITINERARY.TO_FACILITY_CD,
VS.ARRIVAL_ACTUAL_DT,
MG_BILL_OF_LADING.BL_TYPE_CD,
MG_BL_CONTAINER.CONTAINER_HEIGHT_CD,
MG_BL_CONTAINER.EQUIPMENT_TYPE_CD
FROM MG_BILL_OF_LADING
INNER JOIN MG_BL_CONTAINER
ON MG_BILL_OF_LADING.BL_ID = MG_BL_CONTAINER.BL_ID
LEFT JOIN (
SELECT DISTINCT
BL_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN NAME ELSE NULL END) AS [SHIPPER],
MAX(CASE WHEN COMPANY_ROLE_CD = 'CN' THEN NAME ELSE NULL END) AS [CONSIGNEE],
MAX(CASE WHEN COMPANY_ROLE_CD = 'NP' THEN NAME ELSE NULL END) AS [NOTIFY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN DUNS ELSE NULL END) AS [SHIPPER_DUNS],
MAX(CASE WHEN COMPANY_ROLE_CD = 'CN' THEN DUNS ELSE NULL END) AS [CONSIGNEE_DUNS],
MAX(CASE WHEN COMPANY_ROLE_CD = 'NP' THEN DUNS ELSE NULL END) AS [NOTIFY_DUNS],
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN COMPANY_NAME ELSE NULL END) AS [SHIPPER_CUSTNAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'CN' THEN COMPANY_NAME ELSE NULL END) AS [CONSIGNEE_CUSTNAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'NP' THEN COMPANY_NAME ELSE NULL END) AS [NOTIFY_CUSTNAME]
FROM MG_BL_PARTY
JOIN MG_COMPANY
ON MG_BL_PARTY.COMPANY_ID = MG_COMPANY.COMPANY_ID
WHERE COMPANY_ROLE_CD IN ('SH','CN','NP')
AND ORDER_SEQ_NBR = 1
GROUP BY BL_ID
)MGP
ON MG_BILL_OF_LADING.BL_ID = MGP.BL_ID
INNER JOIN MG_BL_ITINERARY
ON MG_BILL_OF_LADING.BL_ID = MG_BL_ITINERARY.BL_ID
AND MG_BILL_OF_LADING.POD_LOCATION_CD = MG_BL_ITINERARY.TO_LOCATION_CD
INNER JOIN MG_VESSEL_PARTICULAR
ON MG_BILL_OF_LADING.VESSEL_CD = MG_VESSEL_PARTICULAR.VESSEL_CD
INNER JOIN MG_VSLVOY_HEADER VH
ON MG_BL_ITINERARY.VESSEL_CD = VH.VESSEL_CD
AND MG_BL_ITINERARY.VOYAGE_CD = VH.VOYAGE_NUM
AND MG_BL_ITINERARY.LEG_CD = VH.LEG_CD
INNER JOIN MG_VSLVOY_PORT_CONTROL PC
ON VH.VSLVOY_HEADER_ID = PC.VSLVOY_HEADER_ID
AND MG_BL_ITINERARY.TO_CALL_SEQ = PC.PORT_SEQUENCE_NBR
INNER JOIN MG_VSLVOY_SCHEDULE VS
ON PC.VSLVOY_SCHEDULE_ID = VS.VSLVOY_SCHEDULE_ID
AND MG_BL_ITINERARY.TO_LOCATION_CD = VS.PORT_CD
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'CONT'
AND MG_BILL_OF_LADING.POD_LOCATION_CD LIKE 'TR%'
AND MG_BILL_OF_LADING.DELETED_FLG = 'N'
AND ((VS.ARRIVAL_ACTUAL_DT > = @FROM_DT AND VS.ARRIVAL_ACTUAL_DT < = @TO_DT) AND
((MG_BILL_OF_LADING.TRADE_CD IN (SELECT ITEM FROM DBO.SPLIT(@trade,',')) OR MG_BILL_OF_LADING.TRADE_CD IS NULL OR @trade = '(NULL)') AND (MG_BL_CONTAINER.CONTAINER_TYPE_CD IN (SELECT ITEM FROM DBO.SPLIT(@contype,',')) OR MG_BL_CONTAINER.CONTAINER_TYPE_CD IS NULL OR @contype = '(NULL)') AND (MG_BILL_OF_LADING.BL_TYPE_CD IN (SELECT ITEM FROM DBO.SPLIT(@bltype,',')) OR MG_BILL_OF_LADING.BL_TYPE_CD IS NULL OR @bltype = '(NULL)' )))
AND ((SHIPPER_CUSTNAME LIKE '%' + @shipname + '%') OR (SHIPPER_CUSTNAME IS NULL) OR (SHIPPER_CUSTNAME = ''))-- or @shipname IS NULL OR SHIPPER_CUSTNAME = ' ')
AND ((CONSIGNEE_CUSTNAME LIKE '%' + @conname + '%') OR (CONSIGNEE_CUSTNAME IS NULL) OR (CONSIGNEE_CUSTNAME = '')) -- or @conname IS NULL OR CONSIGNEE_CUSTNAME = ' ')
AND ((NOTIFY_CUSTNAME LIKE '%' + @notname + '%') OR (NOTIFY_CUSTNAME IS NULL) OR (NOTIFY_CUSTNAME = '')) --or @notname IS NULL OR NOTIFY_CUSTNAME = ' ')
AND ((SHIPPER_DUNS LIKE '%' + @shipduns + '%') OR (SHIPPER_DUNS IS NULL) OR (SHIPPER_DUNS = '')) -- or @shipduns IS NULL OR SHIPPER_DUNS = ' ')
AND ((CONSIGNEE_DUNS LIKE '%' + @conduns + '%') OR (CONSIGNEE_DUNS IS NULL) OR (CONSIGNEE_DUNS = '')) --or @conduns IS NULL OR CONSIGNEE_DUNS = ' ')
AND ((NOTIFY_DUNS LIKE '%' + @notduns + '%') OR (NOTIFY_DUNS IS NULL) OR (NOTIFY_DUNS = '')) --or @notduns IS NULL OR NOTIFY_DUNS = ' ')
AND ((MG_BL_ITINERARY.FROM_FACILITY_CD LIKE '%' + @ITfromfacility + '%') OR (MG_BL_ITINERARY.FROM_FACILITY_CD IS NULL) OR (MG_BL_ITINERARY.FROM_FACILITY_CD = '')) -- or @ITfromfacility IS NULL OR MG_BL_ITINERARY.FROM_FACILITY_CD = ' ')
AND ((MG_BL_ITINERARY.TO_FACILITY_CD LIKE '%' + @ITtofacility + '%') OR (MG_BL_ITINERARY.TO_FACILITY_CD IS NULL) OR (MG_BL_ITINERARY.TO_FACILITY_CD = ''))-- or @ITtofacility IS NULL OR MG_BL_ITINERARY.TO_FACILITY_CD = ' ')
ORDER BY Container_Info asc, VessVoyLeg asc, MG_BILL_OF_LADING.CREATE_DT asc
Can somebody please advise on how to sort this issue out. Thanks.
February 26, 2016 at 6:41 am
Duplicate post.
Replies here please: http://www.sqlservercentral.com/Forums/FindPost1764628.aspx
-- Gianluca Sartori
February 26, 2016 at 6:50 am
You have an OR condition. It's saying return rows that match OR where CONSIGNEE_DUNS is null.
(CONSIGNEE_DUNS LIKE '%' + @conduns + '%') OR (CONSIGNEE_DUNS IS NULL) OR (CONSIGNEE_DUNS = '')
What you should be doing is testing if the value of the Parameter is empty. It's the parameter that is optional.
i.e. (@conduns = '' OR CONSIGNEE_DUNS LIKE '%' + @conduns + '%')
Here you are saying if the parameter is empty ignore (short-circuit) the condition OR if it's not empty only return the row if it matches.
February 29, 2016 at 7:36 am
Get me? (2/26/2016)
You have an OR condition. It's saying return rows that match OR where CONSIGNEE_DUNS is null.
(CONSIGNEE_DUNS LIKE '%' + @conduns + '%') OR (CONSIGNEE_DUNS IS NULL) OR (CONSIGNEE_DUNS = '')What you should be doing is testing if the value of the Parameter is empty. It's the parameter that is optional.
i.e.
(@conduns = '' OR CONSIGNEE_DUNS LIKE '%' + @conduns + '%')Here you are saying if the parameter is empty ignore (short-circuit) the condition OR if it's not empty only return the row if it matches.
Thanks very much for resolving this issue, it worked perfectly !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply