INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...An excellent site which has quite possibly prevented me from having a mental/nervous breakdown..."
Geography
Where in the world do Tek-Tips members come from?
|
Order By with Mid function NOT working ?
|
|
|
AlienX69 (TechnicalUser) |
15 Aug 12 12:33 |
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.
CODE --> 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 Mid([PD_ACT_PDTABLE_121.PATH_NAME],8,3), 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):
CODE --> 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'" |
|
Try something like this so ORACLE can understand:
CODESelect ...
Union Select...
Union Select ...
ORDER BY SUBSTR([PD_ACT_PDTABLE_121.PATH_NAME],8,3), ... Have fun.
---- Andy |
|
|
AlienX69 (TechnicalUser) |
15 Aug 12 14:19 |
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.
CODE --> 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 (TechnicalUser) |
15 Aug 12 14:28 |
|
Try (since you called this field Path):
CODESelect ...
Union Select...
Union Select ...
ORDER BY SUBSTR(Path,8,3), ...
or (since it is your first field)
CODESelect ...
Union Select...
Union Select ...
ORDER BY SUBSTR(1,8,3), ... Have fun.
---- Andy |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One