?
Solved

Return All Values if Filter Item is Left Blank

Posted on 2013-05-20
3
Medium Priority
?
277 Views
Last Modified: 2013-05-21
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,
  WL.sDesc            WLsDesc,
  WLGD.hWlApplic      WLGDhWlApplic,
  WLGD.dtApplied      WLGDdtApplied,
  WLGD.iWLPosition      WLGDiWLPosition

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
0
Comment
Question by:melikins
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39182056
If the UI is a textbox, then something like...

WHERE ...
  and (WL.scode = '#sCode#' OR WL.scode IS NULL)

If the UI is a combo box, I usually create a UNION query to populate it with an ALL, something like...
SELECT -1 as id, '< ALL >' as scode, 0 as sort_order
UNION ALL 
SELECT id, scode, 1 as sort_order
FROM YourTable
ORDER BY sort_order, scode

Open in new window

Then if the user selects < ALL >, handle it in your T-SQL

WHERE ...
  and (WL.scode = '#sCode#' OR WL.scode  = -1)
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39182070
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39182200
check this.
select CAST(WLG.iBedroomSize as char(1)) + ' Bedroom' as wlglist,
  WL.sDesc            WLsDesc,
  WLGD.hWlApplic      WLGDhWlApplic,
  WLGD.dtApplied      WLGDdtApplied,
  WLGD.iWLPosition      WLGDiWLPosition

from WlGenDet WLGD, WlGen WLG, WlWaitList WL
where WLGD.hGen = WLG.hMy 
  and WL.hProperty = coalesce(nullif(#hProp#,''),WL.hProperty)
  and WL.scode = coalesce(nullif('#sCode#',''),WL.scode)
  and WLG.hWaitList  = WL.hMy
  and WLG.bListArchived = 0

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question