Query of Queries

[restlessmedia]

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... :)

[KRIKAT]

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?

[paross1]

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

[KRIKAT]

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>

[sthompson]

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)#

[game_on]

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@)

[Jared@Itron]

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
parse 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 parse 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

[Dan Bracuk]

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

[MikerRoo]

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