A

Anonymous

Resuelto

Converting an ID# to Name, Address, etc.

Forum|Forum|16 years ago
November 3, 2009
23 respuestas
2337 visualizaciones

I am working on an output page that displays orders. My query calls on a field called ID# which is the primary key for another table named Dealers.

Here is my dilemma, I want to convert the ID to the actual name, address, etc. of the dealer for dispaly on my output page. I know how to run a query to accomplish this but my question is how do this within a cfoutput of a different query. My page displays a grouped output query of two tables, neither of which is the Dealers table. How would I insert the converted ID number within this output? Can you turn an output query on and off midstream?

Este tema ha sido cerrado para respuestas.

Mejor respuesta de -__cfSearching__-

That's becuase Orders_ID does not exist in your database schema. It is an alias in your select clause and you are not allowed to use an alias in the Order By clause you have to use the actual table.fieldName you want to order by.

That's becuase Orders_ID does not exist in your database
schema. It is an alias in your select clause and you are
not allowed to use an alias in the Group By clause you have
to use the actual table.fieldName you want to order by.

Another reason to use MS SQL, MySQL, etcetera. They do let you ORDER BY an alias

D

Dan_Bracuk

Inspiring

Why don't you simply select it in the original query?

A

Anonymous

Okay, I am trying to select it within one query...here is my code;

<cfquery name="qGetDaily_Orders" datasource="manna_premier">
SELECT Orders.ID AS Orders_ID,
       US_Dealers.ID AS US_Dealers_ID,
    US_Dealers.DealerName,
    US_Dealers.DealerAddress,
    US_Dealers.DealerCity,
    US_Dealers.DealerState,
    US_Dealers.DealerZIPCode,
    ProductOrders.OrderID,
    ProductOrders.Product,
    ProductOrders.Quantity,
    ProductOrders.BagsCases,
    ProductOrders.NewExisting,
    Orders.SaleDate,
    Orders.TerritoryManager,
    Orders.Distributor,
    Orders.DealerID,
    Orders.PONumber,
    Orders.Variable,
    Orders.ISOCheck,
    Orders.ISOCheckNumber,
    Orders.ISOCheckAmount,
    Orders.UserID
FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID]
WHERE SaleDate = #CreateODBCDate(URL.SaleDate)# AND UserID = #URL.UserID#
ORDER BY Orders_ID, OrderID
</cfquery>

And when executed I get this error;

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in D:\Inetpub\mannapremier\daily_report2.cfm: line 51

49 :         Orders.UserID
50 : FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID]
51 : WHERE SaleDate = #CreateODBCDate(URL.SaleDate)# AND UserID = #URL.UserID#
52 : ORDER BY Orders_ID, OrderID
53 : </cfquery>

SQLSTATE	07002
SQL	SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID, US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity, US_Dealers.DealerState, US_Dealers.DealerZIPCode, ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity, ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate, Orders.TerritoryManager, Orders.Distributor, Orders.DealerID, Orders.PONumber, Orders.Variable, Orders.ISOCheck, Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] WHERE SaleDate = {d '2009-11-02'} AND UserID = 10124 ORDER BY Orders_ID, OrderID
VENDORERRORCODE	-3010
DATASOURCE	manna_premier

Resources:

What am I doing wrong here?

ilssac

Inspiring

What happens if you take the SQL from that error message, and run it directly in your database?

SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID,
US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity,
US_Dealers.DealerState, US_Dealers.DealerZIPCode,
ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity,
ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate,
Orders.TerritoryManager, Orders.Distributor, Orders.DealerID,
Orders.PONumber, Orders.Variable, Orders.ISOCheck,
Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM
(US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID])
INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] WHERE
SaleDate = {d '2009-11-02'} AND UserID = 10124 ORDER BY Orders_ID,
OrderID

M

Mi-ul-MVAtZo

Participating Frequently

Assuming your dealers query (qDealer) only produces one row

<h1>OrderID: #orderID#</h1>

<p>Order date: #datePlaced# by #qDealer.name#. #qDealerAddress#</p>

etc.

</cfoutput>

or

<h1>OrderID: #qOrders.orderID#</h1>

<p>Order date: #qOrders.datePlaced# by #qDealer.name#. #qDealerAddress#</p>

</cfoutput>

<tr><td>#lineID#</td><td>#lineDescription#</td><td>#lineQty#</td></tr>

</cfoutput>

Error Executing Database Query.

Regístrese

Social Login

Bienvenido

Social Login