Professional Web Applications Themes

Query Of Query Substring Issue - Coldfusion - Advanced Techniques

I am using the Query of Query method and I am getting a error message when I get to my query that uses SUBSTRING ( expression , start , length ) in the Select Statement. I know that Query of Query makes you write your Statements out a little differntly then when you are just querying your live database and not caching it like I am doing. This query runs perfect when I am not trying the Query of Query Method. My code is below. Can someone please give me some suggestions. Am I missing a bracket or something? Thanks... ...

  1. #1

    Default Query Of Query Substring Issue

    I am using the Query of Query method and I am getting a error message when I
    get to my query that uses SUBSTRING ( expression , start , length ) in the
    Select Statement. I know that Query of Query makes you write your Statements
    out a little differntly then when you are just querying your live database and
    not caching it like I am doing. This query runs perfect when I am not trying
    the Query of Query Method. My code is below. Can someone please give me some
    suggestions. Am I missing a bracket or something? Thanks...



    Query Of Queries syntax error.
    Encountered "=. Incorrect Select Statement, Expecting a 'FROM', but
    encountered '=' instead, A select statement should have a 'FROM' construct.

    The error occurred in
    D:\INETPUB\GARDEN-PONDS-WATERFALLS\CustomInsert\index.cfm: line 50

    48 : SELECT X = SUBSTRING(META_SRC_URI, 91, 2),_ID
    49 : FROM alldata
    50 : Where _category_data_epond._ID = #_product_SKU#
    51 : </cfquery>
    52 :



    Gdev Guest

  2. #2

    Default Re: Query Of Query Substring Issue

    You could always select the entire field, and use the mid function on it
    afterwards.

    Or you could include the substring in your original query, give it an alias
    (appears to be x in your example) and select the alias in your Q of Q.

    Originally posted by: Gdev
    I am using the Query of Query method and I am getting a error message when I
    get to my query that uses SUBSTRING ( expression , start , length ) in the
    Select Statement. I know that Query of Query makes you write your Statements
    out a little differently then when you are just querying your live database and
    not querying the cached results like I am doing. This query runs perfect when I
    am not trying the Query of Query Method. My code is below. Can someone please
    give me some suggestions. Am I missing a bracket or something? Thanks...



    Query Of Queries syntax error.
    Encountered "=. Incorrect Select Statement, Expecting a 'FROM', but
    encountered '=' instead, A select statement should have a 'FROM' construct.

    The error occurred in
    D:\INETPUB\GARDEN-PONDS-WATERFALLS\CustomInsert\index.cfm: line 50

    48 : SELECT X = SUBSTRING(META_SRC_URI, 91, 2),_ID
    49 : FROM alldata
    50 : Where _category_data_epond._ID = #_product_SKU#
    51 : </cfquery>
    52 :





    Dan Guest

  3. #3

    Default Re: Query Of Query Substring Issue

    Thanks for the quick reply. What I was really trying to find out is why this
    query runs perfectly when I am not using this Query of Query Method. I have
    searched all of the internet and I have found some information that when you
    use certain SQL Statements in Query of Query you have to do a little extra to
    get it to work meaning add some special code , brackets, etc... It would be
    great if I knew what this Query of Query wanted in my SQL Statement so I will
    not get this error.

    Here is a PDF that has some information on Query of Query. I have read through
    it and tried some of there examples but have had no luck.

    http://www.dendwrite.com/samples/cfmx_ch_qoq.pdf


    Thanks again...

    Gdev Guest

  4. #4

    Default Re: Query Of Query Substring Issue

    They all have a lot of things in common but oracle is slightly different than
    ms sql which is slightly different than sybase, which is slight different than
    db2, which is slightly different than redbrick which is slightly different than
    mysql,..., which is slightly different than Q of Q. If ya wanna use it, ya
    gotta learn it.

    Originally posted by: Gdev
    Thanks for the quick reply. What I was really trying to find out is why this
    query runs perfectly when I am not using this Query of Query Method. I have
    searched all of the internet and I have found some information that when you
    use certain SQL Statements in Query of Query you have to do a little extra to
    get it to work meaning add some special code , brackets, etc... It would be
    great if I knew what this Query of Query wanted in my SQL Statement so I will
    not get this error.

    Here is a PDF that has some information on Query of Query. I have read through
    it and tried some of there examples but have had no luck.

    http://www.dendwrite.com/samples/cfmx_ch_qoq.pdf


    Thanks again...



    Dan Guest

  5. #5

    Default Re: Query Of Query Substring Issue

    Gdev,

    As you said, Query of Query is a CF construct that allows you to re-query
    results in memory. It has only a limited set of functions, much more limited
    than if you were querying a regular database. I don't know about MX 7, but I
    don't think that QoQ's in MX6 have a substring type function. I'm not 100%
    positive though.

    http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636

    As mentioned two work arounds are to either perform these operations in your
    regular query (using your database's string functions) or to format the output
    using CF's string functions.



    mxstu Guest

Similar Threads

  1. query issue
    By vince_n in forum Coldfusion Database Access
    Replies: 5
    Last Post: January 12th, 10:16 PM
  2. Query Issue - HELP PLS
    By kingjes in forum Coldfusion - Getting Started
    Replies: 5
    Last Post: November 3rd, 07:18 PM
  3. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM

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