SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Maryland, USA
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CFSQL and calculated columns

    The code:
    Code:
    <cfsearch collection="meetings" name="meetinglist">
    <cfquery dbtype="query" name="test">
    SELECT url, "#GetFileFromPath(key)# as filename
    FROM meetinglist
    </cfquery>
    The error:

    Query manipulation error code = 0
    Invalid SQL
    SQL= select url, "102203.pdf" as filename from meetinglist

    Datasource = ""

    The question:
    Is it possible to insert a column into a query using CFSQL? I have tried a number of times to do this without any success. What I am trying to do is take the search results and insert a column which has only the file names in it.

    TIA
    Michael Hatch

  2. #2
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure what you mean by CFSQL, but I think what you need to do is use single quotes around the file name instead of double quotes. SQL doesn't recognize double quotes as string delimiters.

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Maryland, USA
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by davidjmedlock
    Not sure what you mean by CFSQL, but I think what you need to do is use single quotes around the file name instead of double quotes. SQL doesn't recognize double quotes as string delimiters.
    CFSQL is the query language supported in the Query of Queries.

    I tried changing the double quotes to single quotes and now get a new error:

    unknown exception condition

    PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

  4. #4
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I've used query of queries before, but I've never referred to it or heard it referred to as CFSQL... Learn something new every day, I guess.

    At any rate, come to think of it, CFSQL does have some limitations. I know aggregate functions don't exactly work that well. So, this solution may work, but it may not be pretty. (This is assuming you want the same value for filename for every row in the query):

    Code:
    <cfsearch collection="meetings" name="meetinglist">
    <cfquery dbtype="query" name="test">
    SELECT url
    FROM meetinglist
    </cfquery>
    
    <cfset FileNames = ArrayNew(1)>
    <cfloop query="test">
      <cfset FileNames[CurrentRow] = GetFileFromPath(key)>
    </cfloop>
    <cfset a = QueryAddColumn(test, "filename", FileNames)>
    That should add a column with the needed value to every row in the query... It's not elegant and not tested, so no guarantees there....

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Maryland, USA
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dave,

    This actually works. I had to tweak a little for syntax but it works. I am still mildly annoyed that the original syntax doesn't work. I will have to try it at home on MX. Stuck with 5.0 here at work.

    Quote Originally Posted by davidjmedlock
    Ah, I've used query of queries before, but I've never referred to it or heard it referred to as CFSQL... Learn something new every day, I guess.

    At any rate, come to think of it, CFSQL does have some limitations. I know aggregate functions don't exactly work that well. So, this solution may work, but it may not be pretty. (This is assuming you want the same value for filename for every row in the query):

    Code:
    <cfsearch collection="meetings" name="meetinglist">
    <cfquery dbtype="query" name="test">
    SELECT url
    FROM meetinglist
    </cfquery>
    
    <cfset FileNames = ArrayNew(1)>
    <cfloop query="test">
      <cfset FileNames[CurrentRow] = GetFileFromPath(key)>
    </cfloop>
    <cfset a = QueryAddColumn(test, "filename", FileNames)>
    That should add a column with the needed value to every row in the query... It's not elegant and not tested, so no guarantees there....

  6. #6
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mrhatch
    Thanks Dave,

    This actually works. I had to tweak a little for syntax but it works. I am still mildly annoyed that the original syntax doesn't work. I will have to try it at home on MX. Stuck with 5.0 here at work.
    Yeah, I figured it wasn't 100% syntactically correct. I didn't have much of a way to test it. I'd be interested to know if MX handles query of queries any better, though. I want to move to that at work, but we've got a large app that needs extensive testing before we do and not enough time to test it...

    Glad I could be of (some) help...


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
  •