Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2016
    Posts
    2

    Post Unanswered: select in filter like result of another select

    Hi
    In simple way I want to do something like this:

    select *
    from employee
    where EMPLOYEE like (select distinct 'VAC'||right(year,2)'%' from shd.dim_date where year=year('08/31/2016'))

    select inside filter must show for example 'VAC16%'. Right now the result is VAC16%.
    How write this to get 'VAC16%'

    I did something like this:

    ''''||(select distinct 'VAC'||right(year,2)||'%' from shd.dim_date where year=year('08/31/2016'))||'''

    the result in select is 'VAC16%' but it doesn't work in filter

    thanks for any answers
    Last edited by Grekio; 08-25-16 at 05:53.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    66
    Provided Answers: 6
    Hi,

    try something like this:
    Code:
    with pattern (str) as (values 'VAC16%', 'VAC17%')
    select *
    from table(values
    'VAC16***', 'VAC17***', 'VAC18***'
    ) e (employee)
    where exists (
    select 1
    from pattern p
    where e.employee like p.str
    )
    Regards,
    Mark.

  3. #3
    Join Date
    Aug 2016
    Posts
    2
    Quote Originally Posted by mark.bb View Post
    Hi,

    try something like this:
    Code:
    with pattern (str) as (values 'VAC16%', 'VAC17%')
    select *
    from table(values
    'VAC16***', 'VAC17***', 'VAC18***'
    ) e (employee)
    where exists (
    select 1
    from pattern p
    where e.employee like p.str
    )
    thanks but
    I must use this formula becouse is prepared from another system.

    right now I found a bug
    this works
    (select distinct 'VAC'||right(year,2)||'%' from shd.dim_date where year=year('08/31/2016'))
    without any '
    I lost || before '%'

  4. #4
    Join Date
    Mar 2015
    Posts
    34
    One thing it is never a good idea to use wildcard charters in your data. '%' is a definite problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •