Return All Values if Filter Item is Left Blank
Posted on 2013-05-20
I would like to update a Crystal report with the below subselect. I have added the following line of code 'and WL.scode = '#sCode#'' to the query below. When a user enters a value for wl.scode (on the report filter), the code works properly. However, when a user does not enter a value for wl.scode (on the report filter), the report lists nothing (rather than listing ALL wl.scodes that apply). How do I write the code to include ALL wl.scodes if a users leaves the wl.scode field blank on the report filter? Please feel free to ask additional questions if I am not explaining myself clearly (I openly admit to being a self-taught SQL novice)! Thanks in advance for any and all suggestions!
select CAST(WLG.iBedroomSize as char(1)) + ' Bedroom' as wlglist,
from WlGenDet WLGD, WlGen WLG, WlWaitList WL
where WLGD.hGen = WLG.hMy
and WL.hProperty = #hProp#
and WL.scode = '#sCode#'
and WLG.hWaitList = WL.hMy
and WLG.bListArchived = 0