6 Replies Latest reply on Oct 31, 2007 9:31 AM by cf_dev2

    Query of Queries

    djc11 Level 1
      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')
        • 1. Re: Query of Queries
          cf_dev2 Level 1
          If bsa_month is numeric, it should sort that way anyway ie 1,2,3,4,5,6..12
          • 2. Re: Query of Queries
            Dan Bracuk Level 5
            Your query is too complicated. The subqueries accomplish nothing that you could not have achieved by selecting directly from the tables.
            • 3. Re: Query of Queries
              djc11 Level 1
              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
              • 4. Re: Query of Queries
                Dan Bracuk Level 5
                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.
                • 5. Re: Query of Queries
                  cf_dev2 Level 1
                  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.

                  • 6. Re: Query of Queries
                    cf_dev2 Level 1
                    quote:

                    Originally posted by: Dan Bracuk
                    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.



                    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