Hello, Here is some more detail:
2 tables - weborderheader,weborderdetail 1 stored procedure joining the 2 tables on the batch_number field. 1 input for the procedure - @searchstring Objective: pass either a btn or batch_number to the stored procedure. The stored procedure should then return the results of the search to a page where I will display the name, btn, and orderdate.The orders can match either the btn (could be multiple orders) or batch_number (will ALWAYS be one order). They will click a detail link where I will run the sproc again, this time showing all of the fields available, with the multiple detail lines. The sproc works fine as long as there is only one detail line. If there is more than one detail line, then I will get a recordcount back that is equal to the number of detail items. Thanks, - Gary -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 10:10 AM To: CF-Talk Subject: Re: OT - Stored Procedure Help > I have a header and detail table. The detail tables stores product(s) > associated with the header (user information). Whenever I have more than > one detail line, I get more than one record returned. I only need to get > the amount of headers back that match the input, not detail. I'm not sure I understand the question. Maybe it would be helpful to know what this query will be used for. The query you have here returns detail information like the product_id, quantity and item_price ... are you using this information after you return it to the cf page and if so, how so? There are several ways to limit the result set to only the headers that match your search info, but they will produce different results, i.e. You could use a group by clause and get aggregated information from the detail table, in other words, total number of all products ordered, total price of all products ordered. You could remove the detail table from the query all-together if you don't need the detail data at all. Or if you need the individual detail data but were hoping to get it in separate columns because you weren't sure how to display them properly on the page, you would probably want to use an order by clause and group the output in ColdFusion <cfoutput query="mystoredprocedure" group="btn"> ... header info here ... <cfoutput>... detail info here... </cfoutput> </cfoutput> hth > CREATE PROCEDURE spWEBOrderHeaderDetailLookup > @SearchString char(10) > AS > > SELECT oh.rssc_rep_id, > oh.office_code, > oh.entry_method, > oh.new_connect_flag, > oh.shipping_method, > oh.payment_method, > oh.delivery_date, > oh.btn, > oh. btn_cust_code, fyi ^ extra space in your column definition I almost didn't notice it, figured it was a typo. :) Isaac Dealey www.turnkey.to 954-776-0046 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
