Skip to main content
N
New_York_Guy
Known Participant
February 4, 2009
Question

Why can't I find any matches ?

  • February 4, 2009
  • 2 replies
  • 269 views
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 ?
    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    February 4, 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.
    C
    craigkaminsky
    Inspiring
    February 4, 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!
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices