QoQ over more than 2 tables

[aubweb]

Hi AIl,

I've made a query (Q1) that returns me aprox. 90k lines, after that I do QoQ (36 different queries)
eg :
Q2 =
select dataA/dataB as result1 from Q1
where year_id = 2008 and type = 1
Q3 =
select dataA/dataB as result2 from Q1
where year_id = 2007 and type = 3
....
The problem is after that I want to return one query to Flex, I tried to join all the tables but CF allows only a select over 2 tables.

My wish is to have a query that returns me something like that

ID | result1 | result2 | ... | result36
84 8.12 6.48 7.69

I already tried to create a struct with all my queries but it's not easy to handle the output in flex....
myOutput = ArrayNew(1);
myOutput[1] = Q2;
myOutput[2] = Q3;

Idea anyone ?

Thank you for you help

Aubry

[Newsgroup_User]

wow, 36 QoQs... sure you can't do it all in one query with some creating
SQL and GROUP BY clause???

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Daverms]

Hi,

Why dont you make it as a store procedure and return your result as an array?.

[Newsgroup_User]

> wow, 36 QoQs... sure you can't do it all in one query with some creating
> SQL and GROUP BY clause???

Seconded. I think the first thing that needs explaining is why there are
36 queries to start with.

--
Adam

[Dan_Bracuk]

For this:
My wish is to have a query that returns me something like that

ID | result1 | result2 | ... | result36
84 8.12 6.48 7.69

An array might be simpler.

Are you sure you need 90K rows? That slows down production big time. If it's historical data that's never going to change it might be worth the time and effort to summarize it in your db.

[aubweb]

I completely changed my mind...

I do not have the rights to create a SP.

The data I retrieve is updated every month ...
What I do... run the big query, export data to a csv file,
import the csv into a SQL database and then I run a SP from my SQL server... it does the job so...thank you for you ideas anyway !