INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

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

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


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

(OP)
THANK YOU!!!! Never thought of building a string to do this. Thank you so much.

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

(OP)
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')




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

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

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

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

CODE --> 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') 

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

(OP)
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.

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

Don't you think Simi deserves a Star?
Click on Great Post! to award one.

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

It is old school ugly but it is not dynamic...

Simi

CODE --> 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 

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

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')) 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close