Sort Question for all

alsjka · Nov 6, 2003

Hi i have output like the following

FI-7-M
FI-7-M
FI-7-M
FI-7-S
FI-7-C
FI-7-CAB
FI-7-A
FI-7-B
FI-26-M
FI-10-M
FI-1-A

AND SINCE NORMAL SORTING PRODUCES BAD RESULTS
FI-26-M
FI-7-A
AND SO ON (SIMPLY INSTEAD OF SORTING 1,2,3 I GET 1,10,2,20)
HOW DO YOU GO ABOUT SORTING SOMETHING LIKE I HAVE ABOVE IN A NORMAL ORDER EITHER DESC OR ASC?

csteinhilber · Nov 6, 2003

Zero padding would'be been a good thing. Zero padding is your friend.

Code:

FI-01-A
FI-07-A
FI-07-B
FI-07-C
FI-07-CAB
FI-07-M
FI-07-M
FI-07-M
FI-07-S
FI-10-M
FI-26-M

would sort correctly.

That being said, if you're unable to do any zero padding (like, if these are part numbers from another legacy system, say), you're going to have to do something wacky with some kind of hash or structure.

The structure option might look something like:

Code:

<CFSET lstMyValues = &quot;FI-7-M,FI-7-M,FI-7-M,FI-7-S,FI-7-C,FI-7-CAB,FI-7-A,FI-7-B,FI-26-M,FI-10-M,FI-1-A&quot;>

<CFSET strValueStruct = StructNew()>
<CFLOOP list=&quot;#lstMyValues#&quot; index=&quot;whichValue&quot; delimiters=&quot;,&quot;>
    <CFSET sKey = ListGetAt(&quot;#whichValue#&quot;,2,&quot;-&quot;)>
    <CFIF NOT StructKeyExists(strValueStruct,sKey)>
        <CFSET strValueStruct[sKey] = ArrayNew(1)>
    </CFIF>
    <CFSET ArrayAppend(strValueStruct[sKey],whichValue)>
</CFLOOP>
<CFLOOP list=&quot;#ListSort(StructKeyList(strValueStruct),&quot;numeric&quot;)#&quot; index=&quot;whichArray&quot;>
    <CFSET lstSortedValues = ListSort(&quot;#ArrayToList(strValueStruct[whichArray])#&quot;,&quot;text&quot;)>
    <CFLOOP list=&quot;#lstSortedValues#&quot; index=&quot;whichSortedValue&quot;>
        <CFOUTPUT>#whichSortedValue#<br /></CFOUTPUT>
    </CFLOOP>
</CFLOOP>

Yuck!

Or, perhaps a more elegant solution would be to use a query.

Code:

<CFSET qryValueSet = QueryNew(&quot;section1,section2,section3&quot;)>
<CFLOOP list=&quot;#lstMyValues#&quot; index=&quot;whichValue&quot; delimiters=&quot;,&quot;>
	<CFSCRIPT>
	    QueryAddRow(qryValueSet);
		QuerySetCell(qryValueSet,&quot;section1&quot;,ListGetAt(&quot;#whichValue#&quot;,1,&quot;-&quot;));
		QuerySetCell(qryValueSet,&quot;section2&quot;,ListGetAt(&quot;#whichValue#&quot;,2,&quot;-&quot;));
		QuerySetCell(qryValueSet,&quot;section3&quot;,ListGetAt(&quot;#whichValue#&quot;,3,&quot;-&quot;));
	</CFSCRIPT>
</CFLOOP>

<CFQUERY name=&quot;qrySortedValues&quot; dbtype=&quot;query&quot;>
	SELECT *
	FROM qryValueSet
	ORDER BY section1,section2,section3
</CFQUERY>

<p>
<CFOUTPUT query=&quot;qrySortedValues&quot;>
#qrySortedValues.section1#-#qrySortedValues.section2#-#qrySortedValues.section3#<br />
</CFOUTPUT>
</p>

If you're getting the list of values from a query already, you might be able to set up alias columns in that query as you're selecting it... which means you wouldn't have to do it in CFML:

Code:

<CFQUERY ...>
   SELECT *, SUBSTRING(part_number,1,CHARINDEX(&quot;-&quot;,part_number)) AS section1, ...
      :
</CFQUERY>

depending on your database.

-Carl

amillia · Nov 6, 2003

What if you wrote a replace function to take out the dashes and replace them with empty space and then sorted it?

csteinhilber · Nov 6, 2003

You'd still get the same sort order.
If you stripped out the dashes, and replaced them with spaces, you'd now have values of:

Code:

FI 7 M
FI 7 M
FI 7 M
FI 7 S
FI 7 C
FI 7 CAB
FI 7 A
FI 7 B
FI 26 M
FI 10 M
FI 1 A

which would still have the undesired effect of sorting as:

Code:

FI 1 A
FI 10 M
FI 26 M
FI 7 A
FI 7 B
FI 7 C
FI 7 CAB
FI 7 M
FI 7 M
FI 7 M
FI 7 S

-Carl

alsjka · Nov 7, 2003

OK this is the code i Have csteinhilber. I seem to be have trouble understanding what you are trying to explain. Can you clarify more...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Items Not Physical Inventoried</title>
</head>
<link rel="stylesheet" href="/stylesheets/crayola.css" type="text/css">

<body>
<cfquery name="get_verify2" datasource="mp2sql">
select warehouseinfo.itemnum, warehouseinfo.LASTDATECOUNTED,warehouseinfo.warehouseid, warehouseinfo.STOCKITEM,stock.qtyonhand, stock.location
from warehouseinfo, stock
where warehouseinfo.itemnum = stock.itemnum
and warehouseinfo.WAREHOUSEID = '#cfs_warehouse#'
and stock.warehouseid = '#cfs_warehouse#'
and warehouseinfo.lastdatecounted < '#form.txt_date#'
and warehouseinfo.itemnum in (select itemnum from invy where type not like '%fas%')
and warehouseinfo.itemnum in (select itemnum from stock where location not like 'FI-GAR%')
and warehouseinfo.itemnum in (select itemnum from invy where itemnum not like 'ELEC-WIRE%')
and warehouseinfo.itemnum in (select itemnum from invy where ud2 not like 'mexico')
and warehouseinfo.stockitem = 'y'
group by warehouseinfo.itemnum, warehouseinfo.LASTDATECOUNTED,warehouseinfo.warehouseid, warehouseinfo.STOCKITEM,stock.qtyonhand, stock.location
ORDER BY case when isnumeric(location) <> 0 then convert(int,location)end DESC
</cfquery>


<cfoutput>#cfs_warehouse#</cfoutput> 
Total Uncounted Items - <cfoutput>#get_verify2.recordcount#</cfoutput>

<table align="center" bordercolor="Silver" border="1" cellpadding="3">
<tr bgcolor="#BFDFFF" bordercolor="Blue">
<td align="center">
Part Number
</td>
<td align="center">
Last Date Counted
</td>
<td align="center">
Stock Location
</td>
<td align="center">
Qty On Hand
</td>
</tr>
<cfloop query = "get_verify2">

<tr>
<td><cfoutput>#get_verify2.itemnum#</cfoutput></td>
<td><cfoutput>#dateformat(get_verify2.LASTDATECOUNTED)#</cfoutput></td>

<td><cfoutput>#get_verify2.location#</cfoutput></td>
<td><cfoutput>#get_verify2.qtyonhand#</cfoutput></td>
</tr>
</cfloop>
</table>
<cfinclude TEMPLATE="/mp2/include/back_arrow_mp2.cfm">

<cfoutput></cfoutput>
</body>
</html>

csteinhilber · Nov 7, 2003

Okay... so you are getting the values from a resultset already. So, you have a couple of options... the last solution I gave may work, depending on which database you're using.

Basically what you need to do is break the

Code:

itemnum

into three separate sections (based on your original example), and sort on each section in order... so you sort on the first section ("FI&quot

first, the second section ("7", "26", "10", etc) second, and so on.

If, as I said, your database will support the proper SQL extensions, the query solution would probably be the fastest. Where you can break that itemnum into the appropriate sections right there in the SQL as you're querying the table, and alias the sections as new column names:

Code:

<CFQUERY name=&quot;get_verify2&quot; datasource=&quot;mp2sql&quot;>
   SELECT warehouseinfo.itemnum,
          warehouseinfo.LASTDATECOUNTED,
          warehouseinfo.warehouseid, 
          warehouseinfo.STOCKITEM,
          stock.qtyonhand,
          stock.location,

Code:

SUBSTRING( warehouseinfo.itemnum, 1, ( CHARINDEX("-",warehouseinfo.itemnum,1) - 1 ) ) AS itemnum_section1, SUBSTRING( warehouseinfo.itemnum, ( CHARINDEX("-",warehouseinfo.itemnum,1) + 1 ), ( CHARINDEX("-",warehouseinfo.itemnum,(CHARINDEX("-",warehouseinfo.itemnum,1) + 1)) - 1 ) ) AS itemnum_section2, SUBSTRING( warehouseinfo.itemnum, ( CHARINDEX("-",warehouseinfo.itemnum,(CHARINDEX("-",warehouseinfo.itemnum,1) + 1)) + 1 ), ( LEN(warehouseinfo.itemnum) ) ) AS itemnum_section3,

Code:

   FROM warehouseinfo, stock
         :
         :
   ORDER BY itemnum_section1,itemnum_section2,itemnum_section3

What all that SUBSTRING business does (or should do... I haven't tested it) is pull out each section of your itemnum and assign the contents to a new column name ("itemnum_section1", "itemnum_section2" and "itemnum_section3&quot

... then you order the results by those columns.

So your items should be sorted properly right out of the starting gate, and your CFLOOP should work as you desire.

If your database doesn't support SUBSTRING and/or CHARINDEX, you may need to do a little research to determine if there are alternatives for those functions for your database (MID and POS could be possibilities). Or, if there simply aren't functions that perform those functions, you'll have to use one of the other solutions I mentioned (the structure or the manual query).

-Carl

alsjka · Nov 10, 2003

This is the error i get?

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name '-'. The specific sequence of files included or processed is:

Any comments?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Items Not Physical Inventoried</title>
</head>
<link rel="stylesheet" href="/stylesheets/crayola.css" type="text/css">

<body>
<cfquery name="get_verify2" datasource="mp2sql">
select warehouseinfo.itemnum, warehouseinfo.LASTDATECOUNTED,warehouseinfo.warehouseid, warehouseinfo.STOCKITEM,stock.qtyonhand, stock.location,
SUBSTRING(stock.location,
1,
(
CHARINDEX("-",stock.location,1) - 1
)
) AS itemnum_section1,

SUBSTRING(
stock.location,
(
CHARINDEX("-",stock.location,1) + 1
),
(
CHARINDEX("-",stock.location,(CHARINDEX("-",stock.location,1) + 1)) - 1
)

) AS itemnum_section2,

SUBSTRING(
stock.location,
(
CHARINDEX("-",stock.location,(CHARINDEX("-",stock.location,1) + 1)) + 1
),
(
LEN(stock.location)
)

) AS itemnum_section3

FROM warehouseinfo, stock
where warehouseinfo.itemnum = stock.itemnum
and warehouseinfo.WAREHOUSEID = '#cfs_warehouse#'
and stock.warehouseid = '#cfs_warehouse#'
and warehouseinfo.lastdatecounted < '#form.txt_date#'
and warehouseinfo.itemnum in (select itemnum from invy where type not like '%fas%')
and warehouseinfo.itemnum in (select itemnum from stock where location not like 'FI-GAR%')
and warehouseinfo.itemnum in (select itemnum from invy where itemnum not like 'ELEC-WIRE%')
and warehouseinfo.itemnum in (select itemnum from invy where itemnum not like 'PT-BUS%')
and warehouseinfo.itemnum in (select itemnum from invy where itemnum not like 'ELEC-STAKON%')

and warehouseinfo.itemnum in (select itemnum from invy where ud2 not like 'mexico')
and warehouseinfo.stockitem = 'y'
group by warehouseinfo.itemnum, warehouseinfo.LASTDATECOUNTED,warehouseinfo.warehouseid, warehouseinfo.STOCKITEM,stock.qtyonhand, stock.location
ORDER BY itemnum_section1,itemnum_section2,itemnum_section3

</cfquery>


<cfoutput>#cfs_warehouse#</cfoutput> 
Total Uncounted Items - <cfoutput>#get_verify2.recordcount#</cfoutput>

<table align="center" bordercolor="Silver" border="1" cellpadding="3">
<tr bgcolor="#BFDFFF" bordercolor="Blue">
<td align="center">
Part Number
</td>
<td align="center">
Last Date Counted
</td>
<td align="center">
Stock Location
</td>
<td align="center">
Qty On Hand
</td>
</tr>
<cfloop query = "get_verify2">

<tr>
<td><cfoutput>#get_verify2.itemnum#</cfoutput></td>
<td><cfoutput>#dateformat(get_verify2.LASTDATECOUNTED)#</cfoutput></td>

<td><cfoutput>#get_verify2.location#</cfoutput></td>
<td><cfoutput>#get_verify2.qtyonhand#</cfoutput></td>
</tr>
</cfloop>
</table>
<cfinclude TEMPLATE="/mp2/include/back_arrow_mp2.cfm">

</body>
</html>

csteinhilber · Nov 10, 2003

Try using single quotes in the CHARINDEX functions, rather than the double quotes. Sorry about that.

ex:

Code:

  CHARINDEX('-',stock.location,1)

etc

-Carl

alsjka · Nov 11, 2003

newest error any response?

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid length parameter passed to the substring function.

alsjka · Nov 11, 2003

ok got it had to remove the "-1"

SUBSTRING(stock.location,
1,
(
CHARINDEX("-",stock.location,1)
)
) AS itemnum_section1,

alsjka · Nov 11, 2003

Thanks again for all your help and support..

Demon148 · Dec 10, 2003

I assume adding a Column to the Table in the database was out of the question. If not why didn't you just add a column called "sort" and give each record an number based on how it should be sorted.
My 2 cents

NetworkGuy101 · Dec 10, 2003

Or better yet give it 3 columns one for FI a second one for the second number and a 3rd for the 3rd number then sort by those columns.

csteinhilber · Dec 10, 2003

That's essentially what we're doing with the SQL above... splitting out the part number into three different columns (via aliasing).

I kind of assumed that it was a part number coming from a legacy database, and that the actual table couldn't be altered. My bias, I suppose ( but based on lots of experience working with legacy data ) ;-)

-Carl

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sort Question for all

alsjka

Technical User

csteinhilber

Programmer

amillia

Programmer

csteinhilber

Programmer

alsjka

Technical User

csteinhilber

Programmer

alsjka

Technical User

csteinhilber

Programmer

alsjka

Technical User

alsjka

Technical User

alsjka

Technical User

Demon148

Technical User

NetworkGuy101

IS-IT--Management

csteinhilber

Programmer

Similar threads

Part and Inventory Search

Sponsor