First off you need to get rid of the where 0=0 in the where clause and use some logic to set how you really want your where statement to look.
I�ve found that the easier way to handle these is to do it all in an cfif statement before the actual query. That way you don�t have to do all of the logic in the query.
I�ve found it more readable and easy to use.
For example
<CFPARAM NAME=� FORM.ponumber� DEFAULT=��>
<CFPARAM NAME=�FORM.invoicenumber� DEFAULT=��>
<CFPARAM NAME=�FORM.vendor� DEFAULT=��>
<CFPARAM NAME=�SearchAll� DEFAULT=��>
<CFIF Form.ponnumber neq ��>
<CFSET Searchpon=Where ponumber like �%#Form.ponumber#%�
<CFIF FORM.invoicenumber neq ��>
<CFSET SearchInv=�And invoicenumber LIKE'%#FORM.invoicenumber#%'�
</CFIF>
<CFIF FORM.vendor neq ��>
<CFSET Searchven= �And vendor LIKE '#trim(FORM.vendor)#'�>
</CFIF>
<CFSET SearchAll=�#SearchPon##SearchInv##SearchVen#�>
<CFELSEIF Form.Ponnum is �� and Form.Invoicenumber NEQ ��>
<CFSET SearchInv=�WHERE invoicenumber LIKE'%#FORM.invoicenumber#%'�>
<CFIF FORM.vendor neq ��>
<CFSET Searchven= �And vendor LIKE '#trim(FORM.vendor)#'�>
</CFIF>
<CFSET SearchAll=�#SearchInv##SearchVen#�>
<CFELSEIF Form.Ponnum is �� and Form.Invoicenumber is �� and Form.vendor NEQ ��>
<CFSET SearchVendor= �Where vendor LIKE '#trim(FORM.vendor)#'�>
<CFSET SearchAll=�#SearchVendor#�>
</CFIF>
<CFQUERY NAME=�Getpo� datasource="purchaseorders">
SELECT *
FROM purchaseorders p, itemsordered I inner join p.CommonID=i.CommonID
#SearchAll#
You also need to connect the tables in your from clause. Your current from would list out a record for each record from both tables. (ie:if you have 10 in the I table and 7 in table P you�d have 70 records that show). I added an inner join, just add whatever id hooks the 2 tables.
I hope that you did a select * just for the example here and that you are not using it for the actual query. You should only select what you really need. The * is going to pull every single field from both tables- do you really need/want all that info. (and do you want your database chugging away on all that unwanted stuff?)
Hope this gets you started.
J
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
