Updating prompt when "null" is selected

• mschaper

  Ten Centuries

  Points: 1120

  More actions

  April 9, 2015 at 9:19 am

  #295798

  I have 3 prompts in my report: (1) Group Number, (2) State, and (3) Branch. The user selects the group number (1) and the prompt for State (2) is updated with all of the states the group does business in and "null". Then the user selects either "null" or one of the states from the state prompt (2). When they make select the state, the branches prompt (3) is completed using the sql below:

  select distinct

  bta.stn_stn_id

  from ody.bil_tas_areas bta

  where

  bta.stpr_id = (select uda.area_id

  from ody.usr_dfnd_areas uda

  where uda.aty_area_typ = 'tas_a_stpr'

  and uda.AREA_DESC = ?)

  order by 1

  This works good when the user make any "state" selection but "null". If they select "null" and want to run the report for all states, I would like to see all of the branches for the group number they selected. Currently, the 3rd prompt is empty. I'm not sure how to get all of the branches displayed when "null" is selected.

  I'm new to sql but I've tried to provide some data with the statement below.

  SELECT *

  INTO #Locations

  FROM (

  SELECT‘01’, ‘Missouri’. ‘A1’ UNION ALL

  SELECT‘01’, ‘Missouri’. ‘A2’ UNION ALL

  SELECT‘01’, ‘Missouri’. ‘A3’ UNION ALL

  SELECT‘01’, ‘Kansas’. ‘A4’ UNION ALL

  SELECT‘01’, ‘Kansas’. ‘A5’ UNION ALL

  SELECT‘01’, ‘Illinois’. ‘A6’ UNION ALL

  SELECT‘01’, ‘Illinois’. ‘A7’ UNION ALL

  SELECT‘01’, ‘Illinois’. ‘A8’ UNION ALL

  ) d (Group_Locs)

  The sql provide above works good if the user selects a group and state. For example, if they select Group "01" and "Missouri", then "A1", "A2", and "A3" are displayed.

  What I'm trying to see is when the user selects "01" and "null", then "A1", "A2", "A3", "A4", "A5", "A6", "A7", and "A8" are displayed.

  Thanks for your help.....

• $t@r

  SSCommitted

  Points: 1751

  More actions

  April 20, 2015 at 12:33 am

  #1791927

  I am Not understanding ur complete requirment..I am giving a query as per my understanding

  SELECT *

  INTO #Locations

  FROM (

  SELECT'01' GroupNo, 'Missouri' State, 'A1' Branch

  UNION ALL

  SELECT'01', 'Missouri', 'A2'

  UNION ALL

  SELECT'01', 'Missouri', 'A3'

  UNION ALL

  SELECT'01', 'Kansas', 'A4'

  UNION ALL

  SELECT'01', 'Kansas', 'A5'

  UNION ALL

  SELECT'01', 'Illinois', 'A6'

  UNION ALL

  SELECT'01', 'Illinois', 'A7'

  UNION ALL

  SELECT'01', 'Illinois', 'A8'

  ) d

  Declare @prm_groupNumber nvarchar(2) ='01',

  @prm_State Nvarchar(20) = null , --'Kansas'

  @prm_Branch Nvarchar(5)= null --'A5' ;

  select * from #Locations

  where GroupNo = @prm_groupNumber and state = isnull(@prm_State,state) and branch = isnull(@prm_Branch ,branch)

  Hope this helps 🙂