Professional Web Applications Themes

using list in subquery - Coldfusion - Advanced Techniques

I have a query that I would like to pass a list of values to use in the WHERE statement. How do I use a list in the WHERE statement? Example: <cfset list = "a,b,c"> SELECT * FROM table WHERE id IN ('#list#') ColdFusion reads the above query as: SELECT * FROM table WHERE id IN ('a,b,c') It read the list as a single string instead of: SELECT * FROM table WHERE id IN ('a',b','c') I remember hearing of a way to insert the needed quotes, but I have forgotten....

  1. #1

    Default using list in subquery

    I have a query that I would like to pass a list of values to use in the WHERE
    statement. How do I use a list in the WHERE statement?

    Example:
    <cfset list = "a,b,c">
    SELECT *
    FROM table
    WHERE id IN ('#list#')

    ColdFusion reads the above query as:
    SELECT *
    FROM table
    WHERE id IN ('a,b,c')

    It read the list as a single string instead of:
    SELECT *
    FROM table
    WHERE id IN ('a',b','c')

    I remember hearing of a way to insert the needed quotes, but I have forgotten.






    spacehog Guest

  2. #2

    Default Re: using list in subquery

    Hey SpaceHog

    I think ListQualify is the function you're after.

    Zoe
    zoeski80 Guest

  3. #3

    Default Re: using list in subquery

    As long as you're not using a stored procedure, just do something like this:
    where id in ('#replace(list, ',', '','', 'ALL')#') So, your ids are character
    strings and not numeric? If they're numeric and you're using MSSQL, you'll
    just want to do it like: where id in (#list#) It doesn't matter which way you
    do it if you're using MySQL, IIRC. Now I have a question for you: how do you
    get your code to look all nice like that when you post it in this forum? HTH
    &amp; Thanks :-) - Andrew.

    anclarke Guest

  4. #4

    Default Re: using list in subquery

    Hmm duh yeah. Forget what I said: use listQualify(). I never do that myself so I didn't even know that existed!

    - Andrew.
    anclarke Guest

Similar Threads

  1. Avoiding subquery
    By André Hänsel in forum MySQL
    Replies: 3
    Last Post: February 17th, 06:48 AM
  2. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  3. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 PM
  4. Select From SubQuery
    By SriSamp in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 09:28 AM
  5. subquery with more than 1 fields
    By Calvin in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 05:53 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139