Why can't I find any matches ?

Report · Feb 04, 2009

My first query select PONumber and itemNO from table1 based on the document number entered in the form.

<cfquery name="qryName1" datasource="dbName">
select
PONumber,
rtrim(itemNo) as itemNo
from table1
where documentNo = '#form.documentNumber#'
</cfquery>

I then take the two fields retrived and match it against this table, to get the pGR and netPrice.
<cfquery name="qryName2" datasource="dbName">
select pGr, netPrice
from OpenPO
where
(purchDoc = '#qryName1.PONumber#' and Item = '#TRIM(qryName1.itemNo)#')
</cfquery>

What is happending is that the PONumber matches but the itemNo does not match. That is why I rtrim in the sql in the first query and trim in the where clause in this query but it still does not find a match. Both items are defiend as nvarchar 5 in the sql tables. I even used cfoutput to show the len and it is 5, so there are no blank spaces, yet it still will not find a match, and I know there are matches.

Any anybody tell me what is wrong and/or what to look for ?

Report · Feb 04, 2009

NYG,

Can you pull out the raw data from the OpenPO table on the Item col for the record you want? Perhaps by altering query2:
<cfquery name="qryName2" datasource="dbName">
select pGr, netPrice, Item
from OpenPO
where purchDoc = '#qryName1.PONumber#'
</cfquery>

I realize this might bring back several records but it could be helpful to see what the DB has versus what you're comparing.

In addition, what happens if you adjust the query2 where clause to use a like for the itemNo)?
where (purchDoc = '#qryName1.PONumber#' and Item like '%#TRIM(qryName1.itemNo)#')

Also, more out of curiosity, does anything change if you alter query1 to change the name of the alias for ItemNo:
<cfquery name="qryName1" datasource="dbName">
select
PONumber,
rtrim(itemNo) as itemNum
from table1
where documentNo = '#form.documentNumber#'
</cfquery>

More curious than anything on that last one!

Report · Feb 04, 2009

First, you should only need one query.

Second, my favourite way to troubleshoot these types of problems is to look at the data. In your case,

select ponumber, item, itemno
from table1 t join openpo o on purchdoc = ponumber
where documentNo = '#form.documentNumber#'

Then cfdump the results.