Free text parameter search displays null values

  • 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.

  • pwalter83 (2/26/2016)


    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 = '')

    The above is the answer to your question. Rows are included if (at least) one of three conditions is met: either the text matches, or the data is NULL or the data is an empty string.

    If what you want is to include matches if a search string is given and include everything if the search string is empty, then change this to:

    CONSIGNEE_DUNS LIKE '%' + @conduns + '%' OR @conduns IS NULL

    Oh, and if the table is big, then this search will be incredibly slow.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (2/26/2016)


    pwalter83 (2/26/2016)


    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 = '')

    The above is the answer to your question. Rows are included if (at least) one of three conditions is met: either the text matches, or the data is NULL or the data is an empty string.

    If what you want is to include matches if a search string is given and include everything if the search string is empty, then change this to:

    CONSIGNEE_DUNS LIKE '%' + @conduns + '%' OR @conduns IS NULL

    Oh, and if the table is big, then this search will be incredibly slow.

    Thanks very much for your help !

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply