Query of Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djc11 - 31 Oct 2007 02:02 GMT

Is there any way to simulate this mySQL query in a CF Q of Q? Basically Ineed
the results ordered by year, then the word month. I would be willing to
perform a simple operation on the Resultset after it has been fetched/created
to order it properly also. Either way would work for me.

SELECT bsa_year, bsa_month FROM
(SELECT * FROM data1 WHERE company_id = 'xxx') t1
UNION SELECT isa_year, isa_month FROM
(SELECT * FROM data2 WHERE company_id = 'xxx') t2
ORDER BY bsa_year,
FIELD(bsa_month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov
','Dec')

Reply to this Message

cf_dev2 - 31 Oct 2007 04:28 GMT

If bsa_month is numeric, it should sort that way anyway ie 1,2,3,4,5,6..12

Reply to this Message

Dan Bracuk - 31 Oct 2007 14:21 GMT

Your query is too complicated. The subqueries accomplish nothing that you could not have achieved by selecting directly from the tables.

Reply to this Message

djc11 - 31 Oct 2007 16:57 GMT

I know it could be stored as numbers instead of the letters, but the table was
not my work and that's the way it's set up. I could change everything related
to it and it probably wouldn't be too much. I just thought if there was a way
to sort like that, i wouldn't have to. And i could use in other places that
weren't just months.

Dan, I realize i could accomplish this with a simple query, but i already have
those queries in memory at the point of this request. The query i pasted in
here was what i was testing in sqlYog. In the code the subqueries are replaced
by the names of the previous queries. I thought this would be a better method
for lowering traffic and be faster since it was in mem already. I realize this
is a small example of both of those since it is such a small query, but would
that typically be the right idea? Or should i just stick to writing the
queries as i need them from the sql server?

-Thank you

Reply to this Message

cf_dev2 - 31 Oct 2007 17:25 GMT

Yes, it probably would be simpler to store the value as a number, or the
year/month/day as a datetime field.

Afaik, QoQ don't provide the type of function you're looking for. Its a
convenience that supports a limited number of functions. Nothing near as
extensive as your db's functions.

Another option is to perform the logic in your sql queries and add a
"bsa_month_number" column to the query results. Then use that column in your
QoQ.

Reply to this Message

Dan Bracuk - 31 Oct 2007 17:16 GMT

I don't use mySQL so I am not sure what it can and can't do. Since bsa_month
is a string, use either a case construct to convert those strings to numbers,
or a function on a date field to get the month number. Use that for your 2nd
sort field.

Subqueries do not work in Q of Q. You have to select from previous queries.

Reply to this Message

cf_dev2 - 31 Oct 2007 17:32 GMT

[q][i]Originally posted by: [b][b]Dan Bracuk[/b][/b][/i]
I don't use mySQL so I am not sure what it can and can't do. Since bsa_month
is a string, use either a case construct to convert those strings to numbers,
or a function on a date field to get the month number. Use that for your 2nd
sort field.
[/q]

Yes. A CASE statement is one option. Another is to concatenate the year,
month and '01' (ie first of month) and use the STR_TO_DATE function to convert
the string to a date. Then use that column in your QoQ order by

-- example
STR_TO_DATE('2007-Jan-01', '%Y-%b-%d') AS bsa_date

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / October 2007

Query of Queries