Join a column for a match out of multiple data set in a column

Database
Enthusiast

Join a column for a match out of multiple data set in a column

Hello All,

I have a requirement to match numeric data of a column for a match with data in column with multiple numeric value seperated by hypen.

Ex:

Sys_calendar.day_of_week (Numeric field with values range from 1-7 denoting Sunday - Saturday)

Values

1

2

3

Mytable.Mycolumn (String field with values 'ALL' or 0-7 seperated by hyphen. Numbers denote Sunday-Saturday.  All means it is for all days. specific numbers mean for those matching days)

Values

All 

1-2-3-4-5

6-7

1-5

So mycolumn can be in different combinations.

For numbers mismatch i can subtract 1 from sys_calendar.day_of_week but I dont want to use case statement to achieve rest of the logic. Any better solution please?

3 REPLIES
Enthusiast

Re: Join a column for a match out of multiple data set in a column

This is what i came up with and works fine..

I would like to see if there is any better solution

sel a.*

 ,schedule_day

 ,calendar_date

 ,day_of_week

 ,case when schedule_day='All' then day_of_week 

 when schedule_day like '%0%' then 0

 when schedule_day like '%1%' then 1

 when schedule_day like '%2%' then 2

 when schedule_day like '%3%' then 3

 when schedule_day like '%4%' then 4

 when schedule_day like '%5%' then 5

 else 6 end as shedule_day_of_week

   from 

 (sel *from JOB_SCHEDULE) a cross join 

( sel calendar_date,day_of_week-1 as day_of_week from Sys_calendar.calendar where calendar_date  between '2016-01-15' and '2016-01-18' )b

where shedule_day_of_week=day_of_week

and date=calendar_date

Enthusiast

Re: Join a column for a match out of multiple data set in a column

I realize that my logic does not work :-(

I will not know what my day_of_week is and thus cannot put case statement like that... any suggestions appreciated

Enthusiast

Re: Join a column for a match out of multiple data set in a column

I was able to achieve using case statemetn -

CASE WHEN A.SCHEDULE_DAY='ALL' THEN DAY_OF_WEEK 

 WHEN A.SCHEDULE_DAY LIKE '%0%' THEN 0 ELSE 99 END AS A,

 CASE WHEN A.SCHEDULE_DAY LIKE '%1%' THEN 1 ELSE 99 END AS B,

CASE WHEN A.SCHEDULE_DAY LIKE '%2%' THEN 2 ELSE 99 END AS C,

 CASE WHEN A.SCHEDULE_DAY LIKE '%3%' THEN 3 ELSE 99 END AS D,

 CASE WHEN A.SCHEDULE_DAY LIKE '%4%' THEN 4 ELSE 99 END AS E,

 CASE WHEN A.SCHEDULE_DAY LIKE '%5%' THEN 5 ELSE 99 END AS F,

 CASE WHEN A.SCHEDULE_DAY LIKE '%6%' THEN 6 ELSE 99 END AS G 

& where clause

WHERE (DAY_OF_WEEK=A OR DAY_OF_WEEK=B OR DAY_OF_WEEK=C OR DAY_OF_WEEK=D OR DAY_OF_WEEK=E OR DAY_OF_WEEK=F OR DAY_OF_WEEK=G  )

any suggestions?