CF Query Repeating Content 1

monoone · May 24, 2002

Hi,

I have 4 Tables in my Databse:

Product
Location
Manager
Loaner

I am trying to filter out Loaners (transactions)
by Product.

I have written this SQL CF QUERY:

---------
<CFQUERY NAME="getFiles" DATASOURCE="Loaner" DBTYPE="ODBC">
SELECT InputDate, QtyOnHand, LocationName, LocationContact, ManName
FROM Loaner, Manager, Location
Where Loaner.ProdID = #FORM.ProdID#
ORDER BY InputDate
</CFQUERY>
---------

I do not get an error on my page but the cfoutput is repeating the info.
----------
How can I fix this?

Please help,

Thanks,

Monoone

pcorreia · May 24, 2002

The problem is that you're getting what's called a "Cartesian Product" or a "Cross Join", because you haven't specified the criteria upon which these tables should be joined.

I can't tell which columns belong to which tables. I suggest you re-post, listing each table and its columns, and I will show you the correct syntax for your join.

monoone · May 24, 2002

I have 4 Tables in my Databse:

Product:
ProdID
ProdNo
ProdDesc
UnitCost

Location:
LocationID
LocationNumber
LocationName
LocationContact

Manager:
ManID
ManName
ManRegion

Loaner
LoanID
ManID
LocationID
ProdID
OtyOnHand
DateInput

I am trying to filter out Loaners (transactions)
by Product.

I have written this SQL CF QUERY:

---------
<CFQUERY NAME="getFiles" DATASOURCE="Loaner" DBTYPE="ODBC">
SELECT InputDate, QtyOnHand, LocationName, LocationContact, ManName
FROM Loaner, Manager, Location
Where Loaner.ProdID = #FORM.ProdID#
ORDER BY InputDate
</CFQUERY>

Here is the CFOUTPUT:

----------------
<cftable query="getFiles" colspacing="7" colheaders="YES" htmltable="Yes">
<cfcol width="13" header="Date" align="LEFT" TEXT="#InputDate#">
<cfcol width="30" header="Manager" align="LEFT" TEXT="#ManName#">
<cfcol width="5" header="QTY" align="LEFT" TEXT="#QtyOnHand#">
<cfcol width="30" header="Location" align="LEFT" TEXT="#LocationName#">
<cfcol width="30" header="Contact." align="LEFT" TEXT="#LocationContact#">
</CFTABLE>

pcorreia · May 24, 2002

Here's the SQL Query:

Code:

SELECT
   L.InputDate, 
   L.QtyOnHand, 
   N.LocationName, 
   N.LocationContact, 
   M.ManName
FROM
   Loaner AS L,
   INNER JOIN Location AS N ON L.LocationID = N.LocationID,
   INNER JOIN Manager AS M ON L.ManID = M.ManID
WHERE
   L.ProdID = '#FORM.ProdID#'
ORDER BY
   L.DateInput

Besides cleaning up the formatting a little, the important change that I made was in the FROM clause. You have to specify which columns "match" between the tables. There are other types of joins that you could read up on, the most common of which (besides the INNER JOIN) is an OUTER JOIN. RIGHT OUTER JOIN (or simply RIGHT JOIN) and LEFT OUTER JOIN (or simply LEFT JOIN) are types of outer joins.

monoone · May 24, 2002

Hello;

I am now getting an error:
-----------
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

SQL = "SELECT L.InputDate, L.QtyOnHand, N.LocationName, N.LocationContact, M.ManName FROM Loaner AS L, INNER JOIN Location AS N ON L.LocationID = N.LocationID, INNER JOIN Manager AS M ON L.ManID = M.ManID WHERE L.ProdID = '1' ORDER BY L.DateInput"

Data Source = "LOANER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:59) in the template file D:\Inetpub\

http://wwwroot\heine_applications\loaner\Report1_results.cfm.

---------------

What did I miss?

pcorreia · May 24, 2002

Woops, I got a little overzealous with my commas. Sorry about that!

The proper syntax for the FROM statement is as follows (note no commas):

Code:

FROM
   Loaner AS L
   INNER JOIN Location AS N ON L.LocationID = N.LocationID
   INNER JOIN Manager AS M ON L.ManID = M.ManID

How embarassing.

monoone · May 24, 2002

another error:

Sorry to be such a pain.

------------
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'L.LocationID = N.LocationID INNER JOIN Manager AS M ON L.ManID = M.ManID'.

SQL = "SELECT L.InputDate, L.QtyOnHand, N.LocationName, N.LocationContact, M.ManName FROM Loaner AS L INNER JOIN Location AS N ON L.LocationID = N.LocationID INNER JOIN Manager AS M ON L.ManID = M.ManID WHERE L.ProdID = '1' ORDER BY L.DateInput"

Data Source = "LOANER"

----------------------

pcorreia · May 24, 2002

Somebody shoot me, I'm clearly having serious problems of the "Friday afternoon before a holiday weekend" variety. If anyone wants to help me out, go right ahead.

I don't really work much with Access -- more with SQL Server. Maybe whatever version of Access you are using doesn't support the ANSI JOIN syntax... in which case, you'd be looking to do something more like this:

Code:

SELECT
   Loaner.InputDate, 
   Loaner.QtyOnHand, 
   Location.LocationName, 
   Location.LocationContact, 
   Manager.ManName
FROM
   Loaner,
   Location,
   Manager
WHERE
   Loaner.LocationID = Location.LocationID
   AND Loaner.ManID = Manager.ManID
   AND Loaner.ProdID = '#FORM.ProdID#'
ORDER BY
   Loaner.DateInput

The difference here is that you're specifying which columns match in the WHERE clause instead of in the FROM clause. If you have the choice, I recommend always using the ANSI syntax, but with using Access you might not have that luxury.

monoone · May 24, 2002

Boy this is whacky;

Another error:

----------
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "SELECT Loaner.InputDate, Loaner.QtyOnHand, Location.LocationName, Location.LocationContact, Manager.ManName FROM Loaner, Location, Manager WHERE Loaner.LocationID = Location.LocationID AND Loaner.ManID = Manager.ManID AND Loaner.ProdID = 1 ORDER BY Loaner.DateInput"

Data Source = "LOANER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:59) in the template file D:\Inetpub\

http://wwwroot\heine_applications\loaner\Report1_results.cfm.

--------------------

calista · May 24, 2002

Here, try this:

<CFQUERY NAME="getFiles" DATASOURCE="Loaner" DBTYPE="ODBC">
SELECT InputDate, QtyOnHand, LocationName, LocationContact, ManName
FROM Loaner, Manager, Location
Where Loaner.ProdID = #FORM.ProdID#
AND Product.ProdID = #Form.ProdID#
ORDER BY InputDate
</CFQUERY>

Calista :-X
Jedi Knight,
Champion of the Force

monoone · May 24, 2002

This is soooo Over my head.

Still getting an error:
-----------------------
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "SELECT InputDate, QtyOnHand, LocationName, LocationContact, ManName FROM Loaner, Manager, Location Where Loaner.ProdID = 1 AND Product.ProdID = 1 ORDER BY InputDate"

Data Source = "LOANER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:59) in the template file D:\Inetpub\

http://wwwroot\heine_applications\loaner\Report1_results.cfm.

----------------------
Eric

pcorreia · May 24, 2002

OK, I bit the bullet and got into Access to see what it writes when you use the Design View to build a query. It's wacky, so get ready:

Code:

SELECT
   Loaner.InputDate, 
   Loaner.QtyOnHand, 
   Location.LocationName, 
   Location.LocationContact, 
   Manager.ManName
FROM
   Location INNER JOIN (Manager INNER JOIN Loaner ON Loaner.ManID = Manager.ManID) ON Loaner.LocationID = Location.LocationID
WHERE
   (((Loaner.ProdID) = '#FORM.ProdID#')))
ORDER BY
   Loaner.DateInput

It makes a twisted, perverse sort of sense. We're back to joining in the FROM clause, but with tons of extra parentheses and a very convoluted way of doing the join. Looks like a train wreck to me, but JET-SQL is sometimes a very strange beast.

monoone · May 24, 2002

It's not digging that either.

-----------------
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '(((Loaner.ProdID) = 1)))'.

SQL = "SELECT Loaner.InputDate, Loaner.QtyOnHand, Location.LocationName, Location.LocationContact, Manager.ManName FROM Location INNER JOIN (Manager INNER JOIN Loaner ON Loaner.ManID = Manager.ManID) ON Loaner.LocationID = Location.LocationID WHERE (((Loaner.ProdID) = 1))) ORDER BY Loaner.DateInput"

Data Source = "LOANER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:59) in the template file D:\Inetpub\

http://wwwroot\heine_applications\loaner\Report1_results.cfm.

---------------

???????

pcorreia · May 24, 2002

Okay, I would try taking out all those crazy parentheses (in the WHERE clause only). I don't think you need any of them.

monoone · May 24, 2002

That didn't work:

Thanks for the effort though - Let me know if anybody has any other ideas.

-----------------
<CFQUERY NAME="getFiles" DATASOURCE="Loaner" DBTYPE="ODBC">
SELECT
Loaner.InputDate,
Loaner.QtyOnHand,
Location.LocationName,
Location.LocationContact,
Manager.ManName
FROM
Location INNER JOIN (Manager INNER JOIN Loaner ON Loaner.ManID = Manager.ManID) ON Loaner.LocationID = Location.LocationID
WHERE
Loaner.ProdID = #FORM.ProdID#
ORDER BY
Loaner.DateInput

</CFQUERY>
----------------
Error:
----------------
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "SELECT Loaner.InputDate, Loaner.QtyOnHand, Location.LocationName, Location.LocationContact, Manager.ManName FROM Location INNER JOIN (Manager INNER JOIN Loaner ON Loaner.ManID = Manager.ManID) ON Loaner.LocationID = Location.LocationID WHERE Loaner.ProdID = 1 ORDER BY Loaner.DateInput"

Data Source = "LOANER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:59) in the template file D:\Inetpub\

http://wwwroot\heine_applications\loaner\Report1_results.cfm.

pcorreia · May 24, 2002

I feel obliged to let you know, since I'm making so many mistakes here, that there's a forum dedicated just to Access/Jet-SQL queries:

Microsoft: Access Queries and JET SQL Forum
forum701

jsimes · May 24, 2002

Hey there!

The problem is that you guys are fighting with Access syntax for the SQL.

You can make the joins with just AND's in the WHERE clause.

Try this:

<CFQUERY NAME="getFiles" DATASOURCE="Loaner" DBTYPE="ODBC">

Code:

	SELECT LN.InputDate, LN.QtyOnHand, LC.LocationName, LC.LocationContact, MN.ManName, PR.ProdNo
	FROM Loaner LN, Manager MN, Location LC, Product PR
	Where LN.ProdID = #FORM.ProdID# AND 
		MN.ManID = LN.ManID AND 
		LC.LocationID = LN.LocationID AND 
		PR.ProdID = LN.ProdID
	ORDER BY InputDate

</CFQUERY>

By the way I added the Product Number and Product table into the mix so you could print out the ProdNo.

Joe

CFDude · May 24, 2002

Hey, just to throw this out there, but is the ProdID in the database a string or a number? If it is a string, make sure you put single quotes around it

Where LN.ProdID = '#FORM.ProdID#'
(I noticed it had it in the access query, but not in anyone elses examples)

Tim P.

monoone · May 28, 2002

That worked Joe;

Thanks!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CF Query Repeating Content 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor