Professional Web Applications Themes

6.1 Query of Query Question. Ref to own Col - Coldfusion - Advanced Techniques

Hey I'm running a QoQ using session variables - every thing works fine until I try to refer to a QoQ column. Let me write the example: 1. application.qoq contains a valid dataset from a direct query to MS SQL SELECT id,somenumber,#(2*somenumber)# AS result FROM application.qoq This simply won't work - of course this sort of thing is normal in a direct dB SQL query. Let me write an example: 2. table1 is a valid table in MS SQL SELECT id,somenumber,(2*somenumber) AS result FROM table1 The things that don't work are: 3. SELECT id,somenumber,#(2*application.qoq.somenumber)# AS result FROM application.qoq Because the ...

  1. #1

    Default 6.1 Query of Query Question. Ref to own Col

    Hey

    I'm running a QoQ using session variables - every thing works fine until I try
    to refer to a QoQ column. Let me write the example:

    1. application.qoq contains a valid dataset from a direct query to MS SQL

    SELECT id,somenumber,#(2*somenumber)# AS result FROM application.qoq

    This simply won't work - of course this sort of thing is normal in a direct dB
    SQL query. Let me write an example:

    2. table1 is a valid table in MS SQL

    SELECT id,somenumber,(2*somenumber) AS result FROM table1

    The things that don't work are:

    3. SELECT id,somenumber,#(2*application.qoq.somenumber)# AS result FROM
    application.qoq

    Because the reference for application.qoq.somenumber always returns the 1st
    column of the application.qoq stored dataset

    There has to be an answer to this, as it can be done using structures (self
    referall) and essentially the datasource object is a structure variant.

    Help!

    smcgovern Guest

  2. #2

    Default Re: 6.1 Query of Query Question. Ref to own Col

    This normally works for me.

    Did you try correct SQL syntax?

    EG Not:
    "SELECT id,somenumber,#(2*somenumber)# AS result FROM application.qoq"

    But:
    "SELECT id,somenumber, (2*somenumber) AS result FROM application.qoq"

    -- MikeR


    MikerRoo Guest

  3. #3

    Default Re: 6.1 Query of Query Question. Ref to own Col

    Yep

    It just comes back with 'Variable SOMENUMBER is undefined'
    smcgovern Guest

  4. #4

    Default Re: 6.1 Query of Query Question. Ref to own Col

    Let me add in that I also perform a function: e.g.,

    SELECT id, somenumber, sin(2*somenumber) AS x FROM application.qoq

    The problem seems to be that unless you encapsulate the function
    sin(2*somenumber) in '#' marks it isn't calculated. But once you do, it thinks
    somenumber is a variable from outside the record set.

    smcgovern Guest

  5. #5

    Default Re: 6.1 Query of Query Question. Ref to own Col

    You've changed the problem!

    Apparently, Q of Q does not support the SQL version of the sin() function -- a
    reasonable design tradeoff but you might open a feature request.

    You can use non QofQ-SQL functions in a QofQ query but with unexpected
    results. For example,
    #sin (2 * application.qoq.somenumber)# AS x
    will work but always returns the calculation based on the first row.

    The best thing is to perform "fancy" SQL in your RDBMS and only use QofQ for
    sorting, repackaging, super-unions, etc.

    Or you can loop through the query. Or you an use the undoented meta
    functions.

    -- MikeR

    MikerRoo Guest

Similar Threads

  1. 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
  2. query of query throwing weird exception
    By damion in forum Coldfusion Database Access
    Replies: 0
    Last Post: April 25th, 03:12 AM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 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
  5. Replies: 1
    Last Post: July 2nd, 09:09 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