Order By with Mid function NOT working ?

[AlienX69]

First, I'm using MS Access as a READ ONLY Front-End to a Oracle dB via ODBC.

I have a query that I cannot seem to figure out why it is not sorting correctly.

Trying to sort the PATH_NAME field first, in particular the 8th thru 10th characters. So I thought I would try the "Mid" function. Also need to sort by DRAWING_TYPE, then DRAWING_NO as well.

The results seem to ignore the Mid function, first sorting UPPERCASE then lowercase. This was the reason I chose to use the Mid function, to ignore the first 7 characters. As you will see in the results the first character is a drive letter, some are uppercase, some are lowercase.

SELECT PD_ACT_PDTABLE_121.PATH_NAME AS Path, Left([PD_ACT_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_ACT_PDTABLE_121.DRAWING_NO AS [Draw #], PD_ACT_PDTABLE_121.DRAWING_TITLE AS Title, PD_ACT_PDTABLE_126.DRAWING_SCALE AS Scale, PD_ACT_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_ACT_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_ACT_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_ACT_PDTABLE_121 INNER JOIN PD_ACT_PDTABLE_126 ON PD_ACT_PDTABLE_121.DRAWING_TYPE = PD_ACT_PDTABLE_126.DRAWING_TYPE
ORDER BY [b]Mid([PD_ACT_PDTABLE_121.PATH_NAME],8,3),[/b] PD_ACT_PDTABLE_121.DRAWING_TYPE, PD_ACT_PDTABLE_121.DRAWING_NO
union SELECT PD_ANI_PDTABLE_121.PATH_NAME AS Path, Left([PD_ANI_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_ANI_PDTABLE_121.DRAWING_NO AS [Draw #], PD_ANI_PDTABLE_121.DRAWING_TITLE AS Title, PD_ANI_PDTABLE_126.DRAWING_SCALE AS Scale, PD_ANI_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_ANI_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_ANI_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_ANI_PDTABLE_121 INNER JOIN PD_ANI_PDTABLE_126 ON PD_ANI_PDTABLE_121.DRAWING_TYPE = PD_ANI_PDTABLE_126.DRAWING_TYPE
union SELECT PD_BGLY_PDTABLE_121.PATH_NAME AS Path, Left([PD_BGLY_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_BGLY_PDTABLE_121.DRAWING_NO AS [Draw #], PD_BGLY_PDTABLE_121.DRAWING_TITLE AS Title, PD_BGLY_PDTABLE_126.DRAWING_SCALE AS Scale, PD_BGLY_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_BGLY_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_BGLY_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_BGLY_PDTABLE_121 INNER JOIN PD_BGLY_PDTABLE_126 ON PD_BGLY_PDTABLE_121.DRAWING_TYPE = PD_BGLY_PDTABLE_126.DRAWING_TYPE
UNION SELECT PD_CCU_PDTABLE_121.PATH_NAME AS Path, Left([PD_CCU_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_CCU_PDTABLE_121.DRAWING_NO AS [Draw #], PD_CCU_PDTABLE_121.DRAWING_TITLE AS Title, PD_CCU_PDTABLE_126.DRAWING_SCALE AS Scale, PD_CCU_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_CCU_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_CCU_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_CCU_PDTABLE_121 INNER JOIN PD_CCU_PDTABLE_126 ON PD_CCU_PDTABLE_121.DRAWING_TYPE = PD_CCU_PDTABLE_126.DRAWING_TYPE;

This is a partial list of the results (also see attachment for complete results):

Path	Draw Type	Draw #	Title	Scale	Cell File	Cell Path	Last Rev
D:\pds\act\draw\dgn\	643	FAA	FAA	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
D:\pds\act\draw\dgn\	643	G6-AB0-007	F-500 EQUIP ARRANGE	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
D:\pds\act\draw\dgn\	643	G6ABO001	0175-ACT-G6-ABO-001 COL 0 TO 8 @EL 21'	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
D:\pds\act\draw\dgn\	643	G6ABO002	0175-ACT-G6-ABO-002 COL 0 TO 8 @EL 49'	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
D:\pds\act\draw\dgn\	643	G6ABO003	0175-ACT-G6-ABO-003 COL 8 TO 15 @EL 21'	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
D:\pds\bgly\draw\643\	643	FP415	MAIN LVL LOOP REACT/FLASH STRIP FPs	"1/4""=1'"			
D:\pds\bgly\draw\643\	643	FP636	BUSS REACT/FLASH STRIP 2ND LVL	"1/4""=1'"			
D:\pds\bgly\draw\643\	643	M1AC8002	GRD FLR LOOP REACT/FLASH STRIP	"1/4""=1'"			
D:\pds\bgly\draw\643\	643	M1AC8005	MAIN LVL LOOP REACT/FLASH STRIP	"1/4""=1'"			
D:\pds\ccu\draw\644\	644	M1AD6001	EQUIPMENT ARRANG. @ GRADE	"1/8""=1'"			
D:\pds\ccu\draw\644\	644	M1AD6002	"EQUIPMENT ARRANG. @ EL.52'-10"""	"1/8""=1'"			
D:\pds\ccu\draw\644\	644	M1AD6003	EQUIP. ARRANG. SECTION LOOKING NORTH	"1/8""=1'"			
D:\pds\ccu\draw\644\	644	M1AD6004	EQUIP. ARRANG. SECTION LOOKING WEST	"1/8""=1'"			
d:\pds\act\draw\dgn\	643	0175-ACT-G6-AG0-010	"PLAN AT ELEVATION 21'-0"""	"3/32""=1'"	dwg_643.cel	D:\pds\act\draw\cell\	
d:\pds\act\draw\elec\	731	0175-ACT-I3-AB0-001	INSTR. WIRING PLAN -AREA 'B' NO.- GRADEL	"1/4""=1'"			
d:\pds\act\draw\elec\	731	0175-ACT-I3-AB0-002	INSTR. WIRING PLAN -AREA 'A' NO.- GRADEL	"1/4""=1'"			
d:\pds\act\draw\elec\	731	0175-ACT-I3-AB0-003	INSTR. WIRING PLAN -AREA 'B' SO.- GRADEL	"1/4""=1'"			
d:\pds\bgly\draw\664\	664	P1-AE4-001	PIPING LOCATION PLAN	"1/4""=1'"			
d:\pds\bgly\draw\664\	664	P1-AE4-002	PIPING LOCATION PLAN	"1/4""=1'"			
d:\pds\bgly\draw\664\	664	P1AH6001	E/W PIPERACK @ GLYOXAL	"1/4""=1'"

 

[Andrzejek]

Try something like this so ORACLE can understand:

Select ...
Union Select...
Union Select ...
[blue]
ORDER BY [b]SUBSTR[/b]([PD_ACT_PDTABLE_121.PATH_NAME],8,3)[/blue], ...

Have fun.

---- Andy

 

[AlienX69]

Got "Undefined function 'SubStr' in expression" leaving Order By after first SELECT string ....

ORDER BY SubStr([PD_ACT_PDTABLE_121.PATH_NAME],8,3), PD_ACT_PDTABLE_121.DRAWING_TYPE, PD_ACT_PDTABLE_121.DRAWING_NO

Reminder .... using MS Access to build queries via ODBC connection to Oracle.

If I put Order By at the end as you shown in your example, I get "Invalid bracketing of name '[PD_ACT_PDTABLE_121.PATH_NAME]'." message.

SELECT PD_ACT_PDTABLE_121.PATH_NAME AS Path, Left([PD_ACT_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_ACT_PDTABLE_121.DRAWING_NO AS [Draw #], PD_ACT_PDTABLE_121.DRAWING_TITLE AS Title, PD_ACT_PDTABLE_126.DRAWING_SCALE AS Scale, PD_ACT_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_ACT_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_ACT_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_ACT_PDTABLE_121 INNER JOIN PD_ACT_PDTABLE_126 ON PD_ACT_PDTABLE_121.DRAWING_TYPE = PD_ACT_PDTABLE_126.DRAWING_TYPE
union SELECT PD_ANI_PDTABLE_121.PATH_NAME AS Path, Left([PD_ANI_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_ANI_PDTABLE_121.DRAWING_NO AS [Draw #], PD_ANI_PDTABLE_121.DRAWING_TITLE AS Title, PD_ANI_PDTABLE_126.DRAWING_SCALE AS Scale, PD_ANI_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_ANI_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_ANI_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_ANI_PDTABLE_121 INNER JOIN PD_ANI_PDTABLE_126 ON PD_ANI_PDTABLE_121.DRAWING_TYPE = PD_ANI_PDTABLE_126.DRAWING_TYPE
union SELECT PD_BGLY_PDTABLE_121.PATH_NAME AS Path, Left([PD_BGLY_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_BGLY_PDTABLE_121.DRAWING_NO AS [Draw #], PD_BGLY_PDTABLE_121.DRAWING_TITLE AS Title, PD_BGLY_PDTABLE_126.DRAWING_SCALE AS Scale, PD_BGLY_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_BGLY_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_BGLY_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_BGLY_PDTABLE_121 INNER JOIN PD_BGLY_PDTABLE_126 ON PD_BGLY_PDTABLE_121.DRAWING_TYPE = PD_BGLY_PDTABLE_126.DRAWING_TYPE
UNION SELECT PD_CCU_PDTABLE_121.PATH_NAME AS Path, Left([PD_CCU_PDTABLE_121.DRAWING_TYPE],3) AS [Draw Type], PD_CCU_PDTABLE_121.DRAWING_NO AS [Draw #], PD_CCU_PDTABLE_121.DRAWING_TITLE AS Title, PD_CCU_PDTABLE_126.DRAWING_SCALE AS Scale, PD_CCU_PDTABLE_126.CELL_FILE_SPEC AS [Cell File], PD_CCU_PDTABLE_126.CELL_PATH_NAME AS [Cell Path], PD_CCU_PDTABLE_121.LAST_REVISION_NO AS [Last Rev]
FROM PD_CCU_PDTABLE_121 INNER JOIN PD_CCU_PDTABLE_126 ON PD_CCU_PDTABLE_121.DRAWING_TYPE = PD_CCU_PDTABLE_126.DRAWING_TYPE
ORDER BY SubStr([PD_ACT_PDTABLE_121.PATH_NAME],8,3), PD_ACT_PDTABLE_121.DRAWING_TYPE, PD_ACT_PDTABLE_121.DRAWING_NO;

 

[AlienX69]

SUBSTRING(string, start, length). In Microsoft Access and MySQL, you can use. MID(string, start, length). In Oracle, use. SUBSTR(string, start, length). A good ...


[URL unfurl="true"]https://www.google.com/search?q=SubStr+in+MS+Access&rls=com.microsoft:en-us:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7[/url]

 

[Andrzejek]

Try (since you called this field [blue]Path[/blue]):

Select ...
Union Select...
Union Select ...

ORDER BY SUBSTR([blue]Path[/blue],8,3), ...

or (since it is your [blue]first[/blue] field)

Select ...
Union Select...
Union Select ...

ORDER BY SUBSTR([blue]1[/blue],8,3), ...

Have fun.

---- Andy