Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using single user entered attribute in where to match one of 4 different columns 1

Status
Not open for further replies.
ghbeers

ghbeers

Programmer
Jul 17, 2014
76
US
I have a report that is generated from a table that looks like

id
name
etc.
FA_SCHOLARSHIP
WI_SCHOLARSHIP
SP_SCHOLARSHIP
SU_SCHOLARSHIP

Where zero or more of the 4 scholarship columns will contain "Y"

I want the user to enter FA, WI, SP OR SU into a single prompt. So if they enter "FA" I only want records to display if there's a Y in FA_SCHOLARSHIP. If the user enters "WI" I only want records to display if there's a Y in WI_SCHOLARSHIPS, and so on if SP or SU was entered.

What is the best way to handle this in my stored procedure.
 
Sort by date Sort by votes
pseudocode:
if INSTR (user input) in ('FA','WI','SP','SU') then
SQL_STMT = 'select id, name, etc from <table> where ' || (user input) || '_SCHOLARSHIP' = ''Y'''
exec SQL_STMT
else <invalid value entered by user, you decide what to do>

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


 
Upvote 0 Downvote
THANK YOU!!!! Never thought of building a string to do this. Thank you so much.
 
Upvote 0 Downvote
Well I thought I could do this, but I still have a problem, because of my lack of expertise in sql server. Is there a way I can embed a string like johnherman suggest into my select without having to make the entire statement a string? I currently have the below select stmt, but the user now wants to be prompted for a 2 character term code to retrieve only the records where the column associated with that term code contains "Y". It's the very last line in the below syntax that needs to change to reflect the method johnherman suggested.

I realize that I must first convert the 2 character term code to the extension that's on the name of the column associated with the term. That's not a problem.

CREATE PROCEDURE [dbo].[XXX_LOI_BOOKSTORE_RPT]
@YR_CDE CHAR(4) = NULL,
@ID_NUM INT = NULL,
-- @TERM CHAR(4) = NULL
AS
if @YR_CDE is not NULL
SELECT
SPU.id_num,
NM.last_name,
NM.first_name,
SPU.sports_cde,
SPU.yr_cde,
SPU.trm_cde,
convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
SPU.trm_bk_fall,
SPU.trm_bk_wint,
SPU.trm_bk_spring,
SPU.trm_bk_sum,
SP.SPORTS_SCHOLARSHIP

FROM sports_tracking_udf SPU

left outer JOIN name_master NM ON SPU.id_num = NM.id_num

left outer join stud_term_sum_div SD on SPU.id_num = SD.id_num
and SPU.yr_cde = SD.yr_cde
and SPU.trm_cde = SD.trm_cde

inner join SPORTS_TRACKING SP on SPU.ID_NUM = SP.ID_NUM
and SPU.yr_cde = SP.yr_cde
and SPU.trm_cde = SP.trm_cde
and SPU.SPORTS_CDE = SP.SPORTS_CDE
and SP.SPORTS_SCHOLARSHIP = 'Y'

WHERE SPU.yr_cde = @YR_CDE
AND (@ID_NUM IS NULL OR SPU.ID_NUM = @ID_NUM)

AND (SPU.TRM_BK_FALL = 'Y' OR SPU.TRM_BK_WINT = 'Y' OR SPU.TRM_BK_SPRING = 'Y' OR SPU.TRM_BK_SUM = 'Y')




 
Upvote 0 Downvote
declare @sql as varchar(1000)
declare @TERM as varchar(4)
declare @MyWhere as varchar(100)

set @term = 'FA'

set @sql = 'SELECT SPU.id_num, NM.last_name, NM.first_name, SPU.sports_cde,'
set @sql = @sql + ' SPU.yr_cde, SPU.trm_cde, convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,'
set @sql = @sql + ' SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP'
set @sql = @sql + ' FROM sports_tracking_udf SPU'
set @sql = @sql + ' left outer JOIN name_master NM'
set @sql = @sql + ' ON SPU.id_num = NM.id_num'
set @sql = @sql + ' left outer join stud_term_sum_div SD'
set @sql = @sql + ' on SPU.id_num = SD.id_num'
set @sql = @sql + ' and SPU.yr_cde = SD.yr_cde'
set @sql = @sql + ' and SPU.trm_cde = SD.trm_cde'
set @sql = @sql + ' inner join SPORTS_TRACKING SP'
set @sql = @sql + ' on SPU.ID_NUM = SP.ID_NUM'
set @sql = @sql + ' and SPU.yr_cde = SP.yr_cde'
set @sql = @sql + ' and SPU.trm_cde = SP.trm_cde'
set @sql = @sql + ' and SPU.SPORTS_CDE = SP.SPORTS_CDE'
set @sql = @sql + ' and SP.SPORTS_SCHOLARSHIP = ''Y'''
set @sql = @sql + ' WHERE SPU.yr_cde = @YR_CDE'
set @sql = @sql + ' AND (@ID_NUM IS NULL'
set @sql = @sql + ' OR SPU.ID_NUM = @ID_NUM)'

select @MyWhere =
case @TERM
WHEN 'FA'
then ' and SPU.TRM_BK_FALL = ''Y'''
WHEN 'WI'
then ' and SPU.TRM_BK_WINT = ''Y'''
WHEN 'SP'
then ' and SPU.TRM_BK_SPRING = ''Y'''
WHEN 'SU'
then ' and SPU.TRM_BK_SUM = ''Y'''
END

set @sql = @sql + @MyWhere
print @sql

 
Upvote 0 Downvote
Hey,

As a side note, I would encourage you to enter then tab over your ON's AND's and OR's... Like below it will make your code SO much easier to follow

Simi

SQL: 
if @YR_CDE is not NULL
SELECT 
SPU.id_num,
NM.last_name,
NM.first_name,
SPU.sports_cde,
SPU.yr_cde,
SPU.trm_cde,
convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
SPU.trm_bk_fall,
SPU.trm_bk_wint,
SPU.trm_bk_spring,
SPU.trm_bk_sum,
SP.SPORTS_SCHOLARSHIP
FROM sports_tracking_udf SPU
left outer JOIN name_master NM 
	ON SPU.id_num = NM.id_num
left outer join stud_term_sum_div SD 
	on SPU.id_num = SD.id_num
	and SPU.yr_cde = SD.yr_cde
	and SPU.trm_cde = SD.trm_cde
inner join SPORTS_TRACKING SP 
	on SPU.ID_NUM = SP.ID_NUM
	and SPU.yr_cde = SP.yr_cde
	and SPU.trm_cde = SP.trm_cde
	and SPU.SPORTS_CDE = SP.SPORTS_CDE
	and SP.SPORTS_SCHOLARSHIP = 'Y'
WHERE SPU.yr_cde = @YR_CDE
	AND (@ID_NUM IS NULL 
		OR SPU.ID_NUM = @ID_NUM)
	AND (SPU.TRM_BK_FALL = 'Y' 
		OR SPU.TRM_BK_WINT = 'Y' 
		OR SPU.TRM_BK_SPRING = 'Y' 
		OR SPU.TRM_BK_SUM = 'Y')
 
Upvote 0 Downvote
Thank you Simi, your solution is what I implemented over the weekend. It works great. I was just hoping there was a way to do it with something dynamically built, but this method is much better. Thank you.
 
Upvote 0 Downvote
Don't you think Simi deserves a Star?
Click on [blue]Great Post![/blue] to award one.

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
It is old school ugly but it is not dynamic...

Simi

SQL: 
declare @sql as varchar(1000)
declare @TERM as varchar(4)

set @term = 'WI'

if @term = 'WI'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
End

if @term = 'FA'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_FALL = 'Y' 
End


if @term = 'SP'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_SPRING = 'Y' 
End

if @term = 'SU'
begin
	SELECT 	SPU.id_num,	NM.last_name, NM.first_name, SPU.sports_cde, SPU.yr_cde, SPU.trm_cde,
	convert(varchar, SPU.JOB_TIME, 101) as LAST_CHANGE,
	SPU.trm_bk_fall, SPU.trm_bk_wint, SPU.trm_bk_spring, SPU.trm_bk_sum, SP.SPORTS_SCHOLARSHIP
	FROM sports_tracking_udf SPU
	left outer JOIN name_master NM 
		ON SPU.id_num = NM.id_num
	left outer join stud_term_sum_div SD 
		on SPU.id_num = SD.id_num
		and SPU.yr_cde = SD.yr_cde
		and SPU.trm_cde = SD.trm_cde
	inner join SPORTS_TRACKING SP 
		on SPU.ID_NUM = SP.ID_NUM
		and SPU.yr_cde = SP.yr_cde
		and SPU.trm_cde = SP.trm_cde
		and SPU.SPORTS_CDE = SP.SPORTS_CDE
		and SP.SPORTS_SCHOLARSHIP = 'Y'
	WHERE SPU.yr_cde = @YR_CDE
		AND (@ID_NUM IS NULL 
			OR SPU.ID_NUM = @ID_NUM)
		AND SPU.TRM_BK_SUM = 'Y' 
End
 
Upvote 0 Downvote
Code: 
SELECT    SPU.id_num
        , NM.last_name
        , NM.first_name
        , SPU.sports_cde
        , SPU.yr_cde
        , SPU.trm_cde
        , CONVERT(VARCHAR, SPU.JOB_TIME, 101) AS LAST_CHANGE
        , SPU.trm_bk_fall
        , SPU.trm_bk_wint
        , SPU.trm_bk_spring
        , SPU.trm_bk_sum
        , SP.SPORTS_SCHOLARSHIP
    FROM  sports_tracking_udf SPU
          LEFT OUTER JOIN name_master NM
              ON SPU.id_num = NM.id_num
          LEFT OUTER JOIN stud_term_sum_div SD
              ON SPU.id_num = SD.id_num
                 AND SPU.yr_cde = SD.yr_cde
                 AND SPU.trm_cde = SD.trm_cde
          INNER JOIN SPORTS_TRACKING SP
              ON SPU.ID_NUM = SP.ID_NUM
                 AND SPU.yr_cde = SP.yr_cde
                 AND SPU.trm_cde = SP.trm_cde
                 AND SPU.SPORTS_CDE = SP.SPORTS_CDE
                 AND SP.SPORTS_SCHOLARSHIP = 'Y'
    WHERE SPU.yr_cde = @YR_CDE
        AND (@ID_NUM IS NULL
                  OR SPU.ID_NUM = @ID_NUM)
		AND ((case @TERM WHEN 'FA' then 1 ELSE 0 END = 1 and SPU.TRM_BK_FALL = 'Y')
			OR (case @TERM WHEN 'WI' THEN 1 ELSE 0 END = 1 and SPU.TRM_BK_WINT = 'Y')
			OR (CASE @TERM WHEN 'SP' then 1 ELSE 0 END = 1 and SPU.TRM_BK_SPRING = 'Y')
			OR (CASE @TERM WHEN 'SU' THEN 1 ELSE 0 END = 1 and SPU.TRM_BK_SUM = 'Y'))
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
Cpreston
  • Locked
  • Question Question
Group results into one row
2
Replies
37
Views
3K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question Question
Consolidate multiple rows into single row
Replies
4
Views
1K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
504
Chris Miller
Chris Miller
peac3
  • Locked
  • Question Question
split one row into multiple rows... help pls
Replies
12
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top