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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy