1 Reply Latest reply on Nov 9, 2007 3:15 PM by cf_dev2

    get the proper format to use in query

      There are many possibilities to get values as below,
      <cfquery name="getHistory" datasource ="#session.odbcfeed#">
      select h.routempid, h.Actingempid
      from #variables.child02table# h
      where h.RequestbaseID = #variables.index_id#

      <cfset variables.emailtoempidx = valuelist(getHistory.routempid)>
      and the output can be:
      ,,12,,11,, ,, ,, 19,, ,, ,,

      and more..
      I need to just get the number(s) and put it or them into the query to fetch some necessary values.

      <cfquery name="getReceiverToEmail" datasource ="#session.odbcfeed#">
      select e.email, e.index_id
      from employee e
      where e.index_id IN (#variables.emailtoempidx#)

      anyone can help me on this? I was thinking to use ListDeleteAt function, it needs a specific position, but positions where "," is located can be anywhere in the list. so I ended up using it. I just cannot think other ways to do that.
        • 1. Re: get the proper format to use in query
          cf_dev2 Level 1
          Since you seem to be using the same datasource, why not just use a join?

          select e.email, e.index_id
          from #variables.child02table# h, employee e
          where h.RequestbaseID = #variables.index_id#
          and h.routempid = e.index_id

          If not, you could probably exclude the empty values in your query's WHERE clause. The correct syntax depends on whether the column values are NULL or just empty strings.