Professional Web Applications Themes

Query of Queries - Coldfusion Database Access

I think that we're using 5 on both environments (not checked that though - they shouldn't be different anyway)... It seems that i've fixed it... notes below: main query (getall) SELECT * FROM tableName WHERE ID=#variables.nId# query of queries SELECT * FROM getall WHERE TYPE='TITLE' The only difference being that the bottom query doesn't reference the columns by name in the select, just uses the asterix to catch all. Im trying think of a logical explanation to this but nothings arrived at my head yet... :)...

  1. #1

    Default Re: Query of queries

    I think that we're using 5 on both environments (not checked that though - they
    shouldn't be different anyway)... It seems that i've fixed it... notes below:
    main query (getall) SELECT * FROM tableName WHERE ID=#variables.nId# query of
    queries SELECT * FROM getall WHERE TYPE='TITLE' The only difference being that
    the bottom query doesn't reference the columns by name in the select, just uses
    the asterix to catch all. Im trying think of a logical explanation to this but
    nothings arrived at my head yet... :)

    restlessmedia Guest

  2. #2

    Default Query of Queries

    Moving from 6.1 to 7 and have a page the makes use of query of queries. Works
    fine in 6.1 but getting the following error in 7:

    Query Of Queries runtime error.
    Comparison Exception: While executing "="
    Unsupported Type Comparison Exception: Comparator operator "=" does not
    support comparison between following types:
    Left hand side expression type = "STRING".
    Right hand side expression type = "NULL".

    Any ideas on how to fix this?


    KRIKAT Guest

  3. #3

    Default Re: Query of Queries

    You didn't post your code, but you should be using IS NULL or IS NOT NULL instead of = NULL or <> NULL respectively when attempting to determine if a column has a NULL value or not.

    Phil
    paross1 Guest

  4. #4

    Default Re: Query of Queries

    I'm not comparing for Null values as that is the error message I get from CF.
    Below is the query that joins two other CF queries. In 6.1 there was no problem
    but 7 is causing the error.

    <cfquery name="account" dbtype="query">
    select * from account, accountx where account.account_id=accountx.account_id
    and account.period=accountx.counter
    order by gl_type, account_id
    </cfquery>

    KRIKAT Guest

  5. #5

    Default Re: Query of Queries

    When using Query of Queries, you have to explicitly test comparison values for
    NULL, otherwise you will get the error you are seeing.

    <cfquery name="account" dbtype="query">
    select *
    from account, accountx
    where account.account_id = accountx.account_id and
    accout.period is not null and
    accountx.counter is not null and
    account.period=accountx.counter
    order by gl_type, account_id
    </cfquery>

    Also note that comparisons are case sensitive, so if you care comparing values
    to CF variables, you need to convert the variable and column value to either
    uppercase or lowercase.

    where upper(table.col ) = #UCase(cfvar)#

    sthompson Guest

  6. #6

    Default Query of Queries

    Hi. I am running a Query of Queries to save processor.

    Its working great when there is nothing complex:


    --------------------------------------------------------------------------------
    ---------------------
    <cfquery name="Logs" datasource="mydatasource" username="myusername"
    password="mypassword" cachedwithin="mytime">
    select cip,date
    from logs
    </cfquery>

    then a query of that query:

    <cfquery name="LogsByTime" dbtype="query">
    select *
    from logs
    </cfquery>


    --------------------------------------------------------------------------------
    ---------------------

    But, when I try to add functions in there it errors...

    All of the following cuase a problem:

    <cfquery name="LogsByTime" dbtype="query">
    select distinct([time_format( time, '%H' )]) as d, cip
    select cip,date
    from logs
    group by d
    </cfquery>

    <cfquery name="LogsByTime" dbtype="query">
    select distinct([time_format( time, '%H' )]) as d, cip
    select cip,date
    from logs
    group by [d]
    </cfquery>

    <cfquery name="LogsByTime" dbtype="query">
    select distinct([time_format( time, '%H' )]), cip
    select cip,date
    from logs
    group by 'time'
    </cfquery>


    Can anyone help me?

    M)


    game_on Guest

  7. #7

    Default Query of Queries

    I'm trying to run a query that gives me the account number in a table if the
    first three characters of an identifer are greater than or equal to 055. For
    example if the identifier was 000054321 then this would not pass but 000055321
    would. The leading zeros are pads to have a large range of numbers so there
    will be a variable amount of them to begin the identifier. I would like to
    p these identifiers in coldfusion as opposed to trying to do it in SQL.
    The only thing is i have to have access to these variables prior to running the
    querythat checks the numbers. Is there a simple approach to this? Perhaps a
    query to get all the values of the table, then use those results to p the
    identifier and query the table again? I need the final output in query form so
    I can pass it to the coldfusion report builder Thanks for the help.

    -jared

    Jared@Itron Guest

  8. #8

    Default Re: Query of Queries

    If you can cast these strings to an integer in your original query, it might solve all your problems.
    Dan Bracuk Guest

  9. #9

    Default Re: Query of Queries

    Because QofQ does not have substring operators, you cannot use QofQ by itself
    to do this.

    You must either loop through the Q results and churn (NOT recommended) or you
    must do some of the work in your original query.

    For example, if the original column is integer, then add another line to your
    first select statement like this:
    SELECT
    ...
    LEFT (CAST (ORIGINAL_COLUMN AS varchar (88), 2) AS sIdentifier
    ...

    (Syntax shown is for MS SQL server but the same thing works, with minor tweaks
    in most RDBM's.)

    If the original column is not an integer then additional operations are needed
    -- details depend on what's really in the original column.

    Then, later you can do a Q of Q that has WHERE CAST (sIdentifier AS Integer)
    >= 55

    MikerRoo Guest

  10. #10

    Default Query Of Queries

    I'm having some trouble with Query of Queries in CF5. I have included my
    select statement in the attached code.

    What I'm looking to be able to do now is select totals from the original query
    using a Query of Queries. For example, I need to know my total return visitors
    as compared to my total new visitors (for the sake of the query, a new visitor
    is anyone whose FirstVisitDate falls within whatever date range the user has
    specified [attributes.start to attributes.end] and a return visitor is anyone
    whose FirstVisitDate does nto fall within the specified range.

    I have tried something like:
    <cfquery dbtype="query" name="GetNewHits">
    Select Count(Visitor)
    From QueryVisitors
    Where FirstVisit
    Between #attributes.start#
    and #attributes.end#
    </cfquery>

    The above query (when it returns results at all) returns a count of all of the
    records within the original query (to me it seems like it doesn't recognize the
    field FirstVisit as a date field). Can anyone offer any suggestions? Thanks,

    --Daniel Casper



    <cfquery Datasource="#request.maindsn#" Name="QueryVisitors">
    SELECT DISTINCT
    dbo.WebSiteVisitors.WebSiteVisitorsID As Visitor
    ,dbo.WebSiteVisitors.FirstVisitDate As FirstVisit
    ,dbo.WebSiteVisitors.WebSiteVisitor_UTM_Source As FirstSource
    ,dbo.WebPageVisits.VisitDate As LastVisit
    ,dbo.WebPageVisits.VisitPage As LandingPage
    ,dbo.WebPageVisits.utm_source As UTMSource
    ,dbo.WebPageVisits.utm_campaign As UTMCampaign
    ,dbo.WebPageVisitorInfo.LastName + ', ' + dbo.WebPageVisitorInfo.FirstName
    AS Name
    FROM
    dbo.WebSiteVisitors
    LEFT OUTER JOIN
    dbo.WebPageVisitorInfo
    ON
    dbo.WebSiteVisitors.WebSiteVisitorsID = dbo.WebPageVisitorInfo.VisitorID
    LEFT OUTER JOIN
    dbo.WebPageVisits
    ON
    dbo.WebSiteVisitors.WebSiteVisitorCookie = dbo.WebPageVisits.VisitorID
    WHERE
    (dbo.WebPageVisits.VisitID IN
    (SELECT MIN(VisitID) AS Expr1
    FROM
    dbo.WebPageVisits
    WHERE
    (dbo.webpagevisits.visitdate
    BETWEEN
    #CreateODBCDate(attributes.start)#
    AND
    #CreateODBCDate(attributes.end)#)
    GROUP BY
    VisitorID))
    ORDER BY
    #Evaluate("attributes.orderby")#
    </cfquery>

    DanCasper Guest

  11. #11

    Default Re: Query Of Queries

    Have you considered not using Count()?
    Have you considered using GetNewHits.Recordcount instead?

    Have you also considered not using BETWEEN?
    But instead use > and <= as your operator.


    Good luck!


    Guest

  12. #12

    Default Re: Query Of Queries

    I (and many others) don't support CF5 anymore, so my first suggestion is to
    upgrade.

    Anyway, I don't remember enough of the Q of Q problems in CF5 but since you
    are using MS SQL, there is another way.

    First add this to the select statement of QueryVisitors:

    , CASE
    dbo.WebSiteVisitors.FirstVisitDate
    BETWEEN
    #CreateODBCDate(attributes.start)#
    AND
    #CreateODBCDate(attributes.end)#)
    THEN
    1
    ELSE
    0
    END AS bFirstVisit


    Then your Q of Q becomes:

    <CFQUERY dbtype="query" name="GetNewHits">
    Select SUM (bFirstVisit) AS iNumNewVisits
    From QueryVisitors
    </CFQUERY>


    -- which should work even in CF5.


    MikerRoo Guest

  13. #13

    Default Re: Query Of Queries

    I wish I had a choice to upgrade, but the company won't spring for a local
    server and our hosting company is 2 releases behind. I think I found a way to
    implement it though without using Q of Q.

    I put a conditional increment in when the results are displayed that checks
    the date against my user range and then either increments a newhit variable if
    it falls within the range, or increments a returnhit variable if it does not.
    So far it's working well that way. If anyone needs it, the conditional
    increment is:

    <cfloop query="QueryVisitors">
    <cfif FirstVisit gt #dateformat(attributes.start, "YYYY-MM-DD")#><cfset
    newhits = #newhitscur# + 1><cfelse><cfset returnhits = #returnhitscur# +
    1></cfif>
    </cfloop>

    Thanks all for your suggestions.


    DanCasper Guest

Similar Threads

  1. Need Help with Query of Queries
    By LyndonPatton in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 18th, 09:02 PM
  2. query of queries with avg()
    By gogl in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 6th, 09:50 PM
  3. Query of Queries in 7.0
    By Funke in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 17th, 11:12 PM
  4. 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
  5. Query of Queries?
    By artists_envy in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 25th, 04:31 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