Hi,
In the report I am working on there are 2 parameters - the first is a drop down list showing countries and other one is a user entry textbox.
The requirement is to show the matching data based on the country and the value entered in the textbox.
however, if no value is selected from the drop down list then it is mandatory for the user to enter value in the textbox and then the data should be displayed on the report as per the user entered value.
Also, if only the country is selected and no user value is entered, then all the data for the particular country should be displayed.
I am not sure how to code this requirement within the where clause in SQL.
I have tried something like below but it doesnt work.
AND (OFFC_CODE IN (@office) and FOLDER_NUM IN (@fol_num)
OR OFFC_CODE IN (@office) OR FOLDER_NUM IN (@fol_num))
Can somebody please help on this ?
Thanks.
Can they select multiple offices or fol_num's? If so, then it gets a lot more challenging, but if not, then it should be something like this:
AND (OFFC_CODE = @office AND FOLDER_NUM = ISNULL(@fol_num,FOLDER_NUM))
OR (FOLDER_NUM = @fol_num AND OFFC_CODE = ISNULL(@office,OFFC_CODE))
Basically, provide @office and optionally @fol_num OR provide @fol_num and optionally @office. One of the two is required in each case in order to get any results.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 26, 2021 at 5:30 pm
Can they select multiple offices or fol_num's? If so, then it gets a lot more challenging, but if not, then it should be something like this:
AND (OFFC_CODE = @office AND FOLDER_NUM = ISNULL(@fol_num,FOLDER_NUM))
OR (FOLDER_NUM = @fol_num AND OFFC_CODE = ISNULL(@office,OFFC_CODE))Basically, provide @office and optionally @fol_num OR provide @fol_num and optionally @office. One of the two is required in each case in order to get any results.
Many thanks for your very helpful solution. I tested it and it worked absolutely fine !
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply