April 10, 2015 at 8:26 am
Hi,
I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
WHERE ((MG_BL_ITINERARY.VESSEL_CD in (@vessel) or MG_BL_ITINERARY.VESSEL_CD IS NULL OR @vessel = '(NULL)') and (MG_BL_ITINERARY.VOYAGE_CD in (@voyage) or MG_BL_ITINERARY.VOYAGE_CD IS NULL or @voyage = '(NULL)') and (MG_BL_ITINERARY.LEG_CD in (@leg) or MG_BL_ITINERARY.LEG_CD IS NULL or @leg = '(NULL)' ))
Could someone please advise what I need to change in the WHERE clause to rectify this error ?
Thanks.
April 10, 2015 at 8:45 am
pwalter83 (4/10/2015)
Hi,I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
WHERE ((MG_BL_ITINERARY.VESSEL_CD in (@vessel) or MG_BL_ITINERARY.VESSEL_CD IS NULL OR @vessel = '(NULL)') and (MG_BL_ITINERARY.VOYAGE_CD in (@voyage) or MG_BL_ITINERARY.VOYAGE_CD IS NULL or @voyage = '(NULL)') and (MG_BL_ITINERARY.LEG_CD in (@leg) or MG_BL_ITINERARY.LEG_CD IS NULL or @leg = '(NULL)' ))
Could someone please advise what I need to change in the WHERE clause to rectify this error ?
Thanks.
First, your code won't work as noted below:
SELECT
mbi.ETA_DT,
mbi.TO_LOCATION_CD
FROM
dbo.MG_BL_ITINERARY mbi
WHERE
((mbi.VESSEL_CD IN (@vessel) OR -- This won't work
mbi.VESSEL_CD IS NULL OR
@vessel = '(NULL)') AND
(mbi.VOYAGE_CD IN (@voyage) OR -- This won't work
mbi.VOYAGE_CD IS NULL OR
@voyage = '(NULL)') AND
(mbi.LEG_CD in (@leg) OR -- This won't work
mbi.LEG_CD IS NULL OR
@leg = '(NULL)' )
);
The following tweaks will make it work (hopefully, since I can't test it as you didn't provide any DDL, sample data, or expected results):
SELECT
mbi.ETA_DT,
mbi.TO_LOCATION_CD
FROM
dbo.MG_BL_ITINERARY mbi
WHERE
((mbi.VESSEL_CD IN (SELECT Item FROM dbo.DelimitedSplit8K(@vessel,',')) OR
mbi.VESSEL_CD IS NULL OR
@vessel = '(NULL)') AND
(mbi.VOYAGE_CD IN (SELECT Item FROM dbo.DelimitedSplit8K(@voyage,',')) OR
mbi.VOYAGE_CD IS NULL OR
@voyage = '(NULL)') AND
(mbi.LEG_CD in (SELECT Item FROM dbo.DelimitedSplit8K(@leg,',')) OR
mbi.LEG_CD IS NULL OR
@leg = '(NULL)' )
);
For it to work you will also need the code attached to the following article:
April 10, 2015 at 8:54 am
Furthermore, you have a non-optimal query form.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2015 at 9:51 am
Lynn Pettis (4/10/2015)
pwalter83 (4/10/2015)
Hi,I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
WHERE ((MG_BL_ITINERARY.VESSEL_CD in (@vessel) or MG_BL_ITINERARY.VESSEL_CD IS NULL OR @vessel = '(NULL)') and (MG_BL_ITINERARY.VOYAGE_CD in (@voyage) or MG_BL_ITINERARY.VOYAGE_CD IS NULL or @voyage = '(NULL)') and (MG_BL_ITINERARY.LEG_CD in (@leg) or MG_BL_ITINERARY.LEG_CD IS NULL or @leg = '(NULL)' ))
Could someone please advise what I need to change in the WHERE clause to rectify this error ?
Thanks.
First, your code won't work as noted below:
SELECT
mbi.ETA_DT,
mbi.TO_LOCATION_CD
FROM
dbo.MG_BL_ITINERARY mbi
WHERE
((mbi.VESSEL_CD IN (@vessel) OR -- This won't work
mbi.VESSEL_CD IS NULL OR
@vessel = '(NULL)') AND
(mbi.VOYAGE_CD IN (@voyage) OR -- This won't work
mbi.VOYAGE_CD IS NULL OR
@voyage = '(NULL)') AND
(mbi.LEG_CD in (@leg) OR -- This won't work
mbi.LEG_CD IS NULL OR
@leg = '(NULL)' )
);
The following tweaks will make it work (hopefully, since I can't test it as you didn't provide any DDL, sample data, or expected results):
SELECT
mbi.ETA_DT,
mbi.TO_LOCATION_CD
FROM
dbo.MG_BL_ITINERARY mbi
WHERE
((mbi.VESSEL_CD IN (SELECT Item FROM dbo.DelimitedSplit8K(@vessel,',')) OR
mbi.VESSEL_CD IS NULL OR
@vessel = '(NULL)') AND
(mbi.VOYAGE_CD IN (SELECT Item FROM dbo.DelimitedSplit8K(@voyage,',')) OR
mbi.VOYAGE_CD IS NULL OR
@voyage = '(NULL)') AND
(mbi.LEG_CD in (SELECT Item FROM dbo.DelimitedSplit8K(@leg,',')) OR
mbi.LEG_CD IS NULL OR
@leg = '(NULL)' )
);
For it to work you will also need the code attached to the following article:
Thanks a lot, that worked perfectly !!. Sorry I marked my own post as solution by mistake and can't seem to unmark it now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply