query of queries

[beckyh]

Anyone see anything wrong with my query of queries? I get the following errorCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

<cfquery name="GetActual" datasource="amflibc">
SELECT YABPREP.BPKQCD, YABPREP.BPKRCD, YABPREP.BPGDNB, YABPREP.BPBAVA/100 as Balance
FROM YABPREP
GROUP BY YABPREP.BPKQCD, YABPREP.BPKRCD, YABPREP.BPGDNB, YABPREP.BPBAVA
HAVING (((YABPREP.BPKQCD)='6121') AND ((YABPREP.BPGDNB)='APR05'))
ORDER BY BPKRCD
</cfquery>

<cfquery name="GetBudget" datasource="amflibc" >
SELECT YABSREP.BSJ6CD, YABSREP.BSJ7CD, YABSREP.BSNBCD, YABSREP.BSBEVA
FROM YABSREP
GROUP BY YABSREP.BSJ6CD, YABSREP.BSJ7CD, YABSREP.BSNBCD, YABSREP.BSBEVA
HAVING (((YABSREP.BSJ6CD)='6121') AND ((YABSREP.BSNBCD)='APR05'))
ORDER BY YABSREP.BSJ7CD
</cfquery>

<cfquery name="JOIN" dbtype="query">
SELECT GetActual.BPKQCD, GetActual.BPKRCD, GetBudget.BSBEVA, GetActual.Balance, GetActual.BPGDNB
FROM GetActual LEFT JOIN GetBudget
ON GetActual.BPKQCD = GetBudget.BSJ6CD AND GetActual.BPKRCD = GetBudget.BSJ7CD
UNION SELECT GetBudget.BSJ6CD, GetBudget.BSJ7CD, GetBudget.BSBEVA, GetActual.Balance, GetBudget.BSNBCD
FROM GetActual RIGHT JOIN GetBudget
ON GetActual.BPKQCD = GetBudget.BSJ6CD AND GetActual.BPKRCD = GetBudget.BSJ7CD
WHERE GetActual.BPKQCD Is Null
</cfquery>

 

[PeterAtKangasoft]

Ok, first of all, Coldfusion's query of a query does not perform very efficiently and should probably be avoided if possible.

I believe you can simplify your code a great deal. Since both of your database queries are in the same datasource, you can do everything in one <cfquery>

To start, try something like this:

<cfquery name="getData" datasource="amflibc">
SELECT p.BPKQCD, p.BPKRCD, p.BPGDNB, p.BPBAVA/100 as Balance, s.BSJ6CD, s.BSJ7CD, s.BSNBCD, s.BSBEVA
FROM YABPREP p
    FULL OUTER JOIN YABSREP s ON p.BPKQCD = s.BSJ6CD
        AND p.BPGDNB = s.BSNBCD
        AND p.BPKRCD = s.BSJ7CD
WHERE p.BPKQCD = '6121'
    AND p.BPGDNB = 'APR05'
ORDER BY p.BPKRCD
</cfquery>

Have a look at what is returned, and add more conditions to the WHERE and ORDER BY clauses.

I would also suggest giving your database admin the flick as those field names are less than helpful

Peter Sankauskas
Web Developer

http://www.kangasoft.com/

 

[beckyh]

Good idea! I am now getting

IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL5001 - Column qualifier or table S undefined.

Does CF like FULL JOIN's?

 

[TruthInSatire]

I agree with peter that the query can be done with a single cfquery however i dissagree with the q of q efficiency issue.

I've written and tested this code. I'll provide the results from 5 runs below.

<cfset time1 = getTickCount()>
<cfquery datasource = "#dsn#" name = "qTimeTest">
	SELECT * 
	FROM	someTable
</cfquery>
<cfset time2 = getTickCount()>
<cfquery dbtype="query" name = "qoqTimeTest">
	SELECT *
	FROM	qTimeTest
</cfquery>
<cfset time3 = getTickCount()>
<cfoutput>
The query took #time2-time1# ms to run<br>
The qOfq took #time3-time2# ms to run<br>
</cfoutput>

results:

The query took 969 ms to run
The qOfq took 15 ms to run

The query took 968 ms to run
The qOfq took 16 ms to run

The query took 954 ms to run
The qOfq took 15 ms to run

The query took 984 ms to run
The qOfq took 16 ms to run

The query took 1015 ms to run
The qOfq took 16 ms to run

so unless you can be more spacific as to what part of q of q is slower than q of db, I'm forced to dissagree with you, considering the above numbers.

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand. - Putt's Law

 

[beckyh]

simulatedFun - i've tried peter's query above and get the above error. Any idea how I can fix that? I have no problem using one or 3 queries, but nothing seems to be working.

 

[TruthInSatire]

Does CF like FULL JOIN's?

cf does any kind of cf you put in the query, cfif's and variable processing, etc... however CF doesn't give a rats behind what the query actually says (except in QofQ). Cf simply sends the query to the Db. if the db doesn't like it, it will tell cf "i don't like it, this is why" then cf says to you "the db didn't like it, this is why" CF is little more than the "middle man" in these times.

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand. - Putt's Law

 

[TruthInSatire]

unfortunatly sql isn't my strong point. Your error suggests that it doesn't like your "s" allias for table "YABSREP" maybe he'll reply again, or rudy, our in house more than capable cf/sql guru can clear that up for you.

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand. - Putt's Law

 

[beckyh]

This is my latest and greatest error, as well as the updated SQL statement.

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword OUTER not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.


SQL = "SELECT BPKQCD, BPKRCD, BPGDNB, BPBAVA/100 as Balance, BSJ6CD, BSJ7CD, BSNBCD, BSBEVA FROM YABPREP FULL OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPGDNB = BSNBCD AND BPKRCD = BSJ7CD WHERE BPKQCD = '6121' AND BPGDNB = 'APR05' ORDER BY BPKRCD"

 

[TruthInSatire]

sounds like you need to use union overr full outer join...?? where oh where are our sql guru's?

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand. - Putt's Law

 

[beckyh]

I tried a UNION in a qoq...

SELECT GetActual.BPKQCD, GetActual.BPKRCD, GetBudget.BSBEVA, GetActual.Balance, GetActual.BPGDNB
FROM GetActual LEFT JOIN GetBudget
ON GetActual.BPKQCD = GetBudget.BSJ6CD AND GetActual.BPKRCD = GetBudget.BSJ7CD
UNION SELECT GetBudget.BSJ6CD, GetBudget.BSJ7CD, GetBudget.BSBEVA, GetActual.Balance, GetBudget.BSNBCD
FROM GetActual RIGHT JOIN GetBudget
ON GetActual.BPKQCD = GetBudget.BSJ6CD AND GetActual.BPKRCD = GetBudget.BSJ7CD
WHERE GetActual.BPKQCD Is Null

without luck.

 

[ecobb]

It sounds like you just need to get the syntax right for the database you're using. Have you tried posting your query in the appropriate database forum? I'm sure the people there could easily show you the right syntax.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

sorry, saw the first few posts in this thread when it first started a couple weeks ago, but did not keep up with it

cam back to it today when looking for QofQ information

TruthInSatire, thanks for the kind words

as always, yes, try to get the database to do all the work whenever possible, especially for joins

i've worked with DB2/400, and the 6-character field names are not so unusual, considering the history of the AS/400 platform as a midi-computer and the green screens you used to have to use to work with it

becky, what makes you feel that you need a FULL OUTER JOIN?

have you checked the DB2/400 manual to see if it's even allowed?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)