1

I'm trying to find out why some of my order numbers in my table are dumping out there appropriate data while others aren't.

Here I'm trying to dump out data from a specific order number and it won't dump its data for the COMPLEX CITY, COMPLEX NAME and ORDERNUM. However, when I just select the columns it dumps there data and shows the corresponding data relating to the ordernum.

I'm using ColdFusion. Here is my code.

<cfquery name="communities" datasource="pdoxtest">
 SELECT DISTINCT 
 [COMPLEX CITY] as COMPLEXCITY, 
 [COMPLEX NAME] as COMPLEXNAME,
 [COMPLEX ST] as COMPLEXST,
 [ORDERNUM]
 FROM SCHEDULE
 WHERE COMPLEXNAME = 'FORGE HOMESTEAD'
</cfquery>

<cfdump var="#communities#">

When I dump out this data it appears like this:

enter image description here However, when I try to dump out a query like this:

<cfquery name="test" datasource="pdoxtest">
 SELECT DISTINCT 
 [COMPLEX CITY] as COMPLEXCITY, 
 [COMPLEX NAME] as COMPLEXNAME,
 [COMPLEX ST] as COMPLEXST,
 [ORDERNUM]
 FROM SCHEDULE
 WHERE ORDERNUM = '144873'
</cfquery>

<cfdump var="#test#">

Nothing gets returned: Here is a screenshot:

enter image description here

I'm not sure what I'm doing wrong because when I enter a different ordernumber in the WHERE clause for a different COMPELXNAME data gets returned while some of the ORDERNUM doesn't like this one.

Curious13
  • 329
  • 2
  • 23

1 Answers1

1

I guess it is sample of "WHAT YOU SEE IS NOT WHAT YOU GET":

 SELECT DISTINCT 
 [COMPLEX CITY] as COMPLEXCITY, 
 [COMPLEX NAME] as COMPLEXNAME,
 [COMPLEX ST] as COMPLEXST,
 [ORDERNUM]
 FROM SCHEDULE
 WHERE ORDERNUM LIKE '%144873%';

I suspect that ORDERNUM column is text and you probably have some whitespaces/control characters.

EDIT:

% is wildcard and it will match any characters. I propose to copy value to some text editor like Notepad++, enable show all characters and check for spaces/carriage return/tabs and so on.

You could think about cleansing data too.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275