Post reply

Weird SQL Query

  • March 26, 2008 at 1:30 pm

    #154233

    Guys,

    I have a weired issue here

    insert into

    select [value] from cfg_elements WHERE ELEMENT_NAME='REPORT_ARBITRATOR_EVENTTYPCDS'

    --- result '45','95'

    when I run the following subquery I get results

    select * from employee where employee_typ in ('45', '95')

    --- I get 4 rows

    but when I run the below statement I do not get any results

    select * from employee where employee_typ in (select [value] from cfg_elements WHERE ELEMENT_NAME='REPORT_ARBITRATOR_EVENTTYPCDS')

    Any suggestions and inputs would help

    Thanks

  • March 27, 2008 at 1:07 am

    #794466

    IN doesn't parse apart a comma-delimited string. The form with a subquery is equivalent to an inner join

    Your query

    select * from employee where employee_typ in (select [value] from cfg_elements WHERE ELEMENT_NAME='REPORT_ARBITRATOR_EVENTTYPCDS')

    Is equivalent to

    select employees.* from employee INNER JOIN elements ON employee_typ = [value] WHERE ELEMENT_NAME='REPORT_ARBITRATOR_EVENTTYPCDS'

    i.e. looking for rows where employee_typ = '''45'', ''95'''

    There are some functions that I've seen in the script library here that will split apart such a string for use in the IN statement.

    You other option (simpler) would be to normalise the cfg_elements table so that you don't have a comma-delimited ist in a column. If it looks more like this, then the IN will work as expected

    Element Name Value

    REPORT_ARBITRATOR_EVENTTYPCDS 45

    REPORT_ARBITRATOR_EVENTTYPCDS 95

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply