Drop Down List not sorting correctly

Report · Aug 11, 2009

Hi,

I have a query that I use in a drop down form. NOMEN.GENERIC_NOMEN is the values that I want displayed in the drop down

While the value I want to select is NOMEN.NOMENCLATURE_ID.

Here is the underlying Query (notice it is sorted by the form values that I want to display)

<cfquery name="NOMEN" datasource="ilmf_parts_dev" blockfactor="100">
SELECT *
FROM NOMEN_TABLE
ORDER BY GENERIC_NOMEN ASC
</cfquery>

Here is the code for that part of the form:

<td width="96" class="style1">Nomenclature</td>
      <td width="379" class="style1"><cfselect name="Add_Inv_Nomen">
          <cfoutput query="NOMEN">
            <option value="#NOMEN.NOMENCLATURE_ID#">#NOMEN.GENERIC_NOMEN#</option>
          </cfoutput>
      </cfselect>      </td>

For some reason, the drop down displays the text in the order of the table index instead of alphabetically in ASC order.

Any Ideas?

Eric

Ps Running off of Oracle 11g DB.

Report · Aug 11, 2009

What's the outcome if you cfdump the query results?

Report · Aug 11, 2009

It does not come out in alphabetical order (seems like it should !)

Look at how the Printer entries are broken up...

Eric

query
	GENERIC_NOMEN	NOMENCLATURE_ID
1	DRIVE, DVD	4
2	PRINTER , ROLLER	39
3	PRINTER, FUSER	40
4	PRINTER, KIT	41
5	AC ADAPTER	22
6	BATTERY	35
7	CABLE	12
8	CARD, CONTROLLER	38
9	CARD, NIC	7
10	CARD, SOUND	17
11	CARD, VIDEO	15
12	COMPUTER	8
13	CPU	13
14	DIMM	3
15	DRAC MODULE	27
16	DRIVE, CD ROM	19
17	DRIVE, FLOPPY	37
18	DRIVE, REMOVABLE	32
19	DRIVE, ZIP	33
20	FAN	20
21	FIBER MODULE	30
22	FIBER OPTIC MODULE	18
23	HARD DRIVE	1
24	KEYBOARD	2
25	MONITOR	6
26	MOTHER BD, COMPUTER	26
27	MOUSE	23
28	NETWORK SWITCH	25
29	NUT, RETAINING	29
30	POWER SUPPLY	5
31	PRINTER	9
32	PRINTER, CARD	16
33	PRINTER, PART	43
34	PRINTER, TRANSFER	11
35	RAM, NV	21
36	SCREW, PHILLIPS HEAD	28
37	SPEAKER(s)	14
38	SWITCH	24
39	TERMINAL, RS232	31
40	TESTER, AC POLARITY	36
41	TONER CARTRIDGE	10
42	TRANCEIVER MODULE	34
43	UNKNOWN NOMENCLATURE	0

CACHED

false

EXECUTIONTIME

0

SQL

SELECT * FROM NOMEN_TABLE ORDER BY GENERIC_NOMEN ASC

Report · Aug 11, 2009

Another simple/dumb question but will help trace the issue...

Can you run the same statement directly inside of the oracle environment, apart from coldfusion, and see what the result set is if you haven't already?

Report · Aug 11, 2009

I haven't used blockfactor but I guess I would try to drop that and see what happens. I don't see it causing issues but if we can strip down to the very basics it may be a better starting point. (Again, you may have tried this)

Report · Aug 11, 2009

Removing "Blockfactor" has no effect on the problem.

I also ran the query directly in Oracle SQL Developer and it sorted in the same incorrect order.

I think it has something to do with sorting on VARCHAR2 feild types...

Eric

Report · Aug 11, 2009

Yep, that'd be the cause. I know you can use some decode/replace functionality within the sql to sort a varchar2 but I'm not positive of the structure of those statements. Sorry I couldn't be of any additional help. Good luck.

Report · Aug 12, 2009

For the benifit of others with this problem, I will expose myself as an idiot and reveal the solution...

There was a space character before 3 of the PRINTER entries in the table. This is why

those three did not sort correctly. I did not notice this initially as I did not have my glasses.

Eric