sqlparadox

SQL Statement Dump Issue

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.

Solution

  • 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.