Professional Web Applications Themes

Complicated SQL query - Coldfusion Database Access

I'm writing a very complicated query (which is actually a series of queries) and wanted to get a few answers that don't seem easily accessible when googled: 1) How can I create a view in a query? If I create a view, can I use that view in the same cfquery statement via a select? How do I correctly separate my query statements if I can, e.g.: <cfquery name="blah" datasource="b"> create view myView as select * from myTable where myField = 1; select * from myView(?) </cfquery> 2) If I cannot create a view, how can I use one query ...

  1. #1

    Default Complicated SQL query

    I'm writing a very complicated query (which is actually a series of queries)
    and wanted to get a few answers that don't seem easily accessible when googled:

    1) How can I create a view in a query? If I create a view, can I use that
    view in the same cfquery statement via a select? How do I correctly separate
    my query statements if I can, e.g.:
    <cfquery name="blah" datasource="b">
    create view myView as
    select * from myTable
    where myField = 1;
    select * from myView(?)
    </cfquery>

    2) If I cannot create a view, how can I use one query as a subquery in
    another query, e.g.:
    <cfquery name="blah" datasource="b">
    select * from mytable
    </cfquery>
    <cfquery ???>
    select * from myOtherTable
    where myField not in #blah?#
    </cfquery>

    Hopefully you get the gist of my meaning here.
    3) Is it possible to manipulate multiple rows in a query, e.g., using
    QuerySetCell in a subquery?

    Thanks.

    pblecha Guest

  2. #2

    Default Re: Complicated SQL query

    SELECT *
    FROM mytable AS m
    WHERE NOT EXISTS(SELECT 1
    FROM myOtherTable AS mo
    WHERE m.myField = mo.myField)

    Phil
    paross1 Guest

  3. #3

    Default Re: Complicated SQL query

    The answer depends on your db.

    If it supports views, why not create it permanently?

    Some dbs support subqueries in the from clause. If they do, you have to give
    the subquery an alias.

    You can use any cold fusion query function after you have run the query.
    Depending on what you are trying to accomplish, and whether your db supports
    the syntax, you might be better off using the sql keywords case, when, then,
    else and end.

    Dan Guest

  4. #4

    Default Re: Complicated SQL query

    The DB is SQL Server 2005. I don't have access to the main production DB to
    create a permanent view; I wanted to create something in memory rather than on
    the DB.

    I have one very active query that acts as a subquery in several other queries;
    this query contains different information every time the db is queried, though,
    so caching it or creating a permanent view wouldn't help anyway. I would like
    to write the code that creates this query each time the application starts, but
    then be able to use it as a subquery (it is not actually being queried; it is
    being compared against, while the actual query is pulling out of the database).

    pblecha Guest

  5. #5

    Default Re: Complicated SQL query

    [q]Originally posted by: paross1
    SELECT *
    FROM myOtherTable AS mo
    WHERE NOT EXISTS(SELECT 1
    FROM mytable AS m
    WHERE m.myField = mo.myField)

    Phil[/q]

    So are you setting up the first part of this query as a temp view? I have a
    much more complicated query than that, and it's bombing out on me when I try
    this.

    pblecha Guest

  6. #6

    Default Re: Complicated SQL query

    pblecha wrote: 

    So show us the real thing. If you give us dummy questions you get dummy
    answers.

    Jochem


    --
    Jochem van Dieten
    Adobe Community Expert for ColdFusion
    Jochem Guest

  7. #7

    Default Re: Complicated SQL query

    You can't really create a "view" on the fly like that (at least that I am aware
    of.) I suppose that you could consider using query-of-query where your database
    query is your "view" and the Q-of-Q as your means extracting data from your
    inital query, etc.

    Otherwise, post your actual code so that we can see why the example didn't
    work, etc.

    Phil

    paross1 Guest

Similar Threads

  1. complicated query - please help
    By Ralph in forum MySQL
    Replies: 7
    Last Post: October 8th, 09:58 PM
  2. Replies: 1
    Last Post: June 26th, 04:09 PM
  3. Requesting help with complicated query
    By Fox in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 10:46 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