Home » SQL & PL/SQL » SQL & PL/SQL » How to sort it. (Report 6i, Oracle 9i)
How to sort it. [message #426681] Sun, 18 October 2009 22:17 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

hi,

I Required to sort the query by the field named Jono,catg. It sort the Jono, But it not sort the catg field, the query follows.

SELECT
CATG,
TYPE,
JONO,
JODT,
VCODE,
VNAME,
INDNO,
DOCNO,
PARTNO,
PARTNAME,
SUNIT,
Sum(Nvl(QTYSU,0)),
ISSUNIT,
Sum(Nvl(QTYIU,0)),
UNITSU,
Sum(Nvl(INVQTYSU,0)),
ISUNIT,
Sum(Nvl(ISQTYIU,0)),
SUUOM,
Sum(Nvl(RCPTQTYSU,0)),
IUUOM,
Sum(Nvl(ISSUEQTYIU,0))
FROM
(
(
SELECT
'A' CATG,
'INPUT_MATERIALS' TYPE,
A.JOBORDERNO JONO,
TO_CHAR(A.JOBORDERDT) JODT,
A.VENDCODE VCODE,
B.VENDNAME VNAME,
'' INDNO,
'' DOCNO,
C.PLHTREFNO PARTNO,
D.PARTNAME1 PARTNAME,
C.STD_UNIT SUNIT,
C.QTY_SENT_SU QTYSU,
C.ISSUE_UNIT ISSUNIT,
C.QTY_SENT_IU QTYIU,
'' UNITSU,
0 INVQTYSU,
'' ISUNIT,
0 ISQTYIU,
'' SUUOM,
0 RCPTQTYSU,
'' IUUOM,
0 ISSUEQTYIU
FROM
JOMAIN A,
VENDMAST B,
JOINPUT C,
PARTMASTER D
WHERE A.UNITID=&UID 
AND A.JOBORDERNO BETWEEN '&FR_JONO' AND '&TO_JONO'
AND A.VENDCODE=B.VENDCODE 
AND A.UNITID=C.UNITID 
AND A.JOBORDERNO=C.JOBORDERNO
AND C.PARTNUMBER=D.PARTNUMBER
)
UNION
(
SELECT
'B' CATG,
'MTL SENT THRU DC TO SUBCONT.' TYPE,
A.INDENTNO JONO,
'' JODT,
A.CUSTCODE VCODE,
D.VENDNAME VNAME,
A.INDENTNO INDNO,
A.DOCUMENTNO DOCNO,
B.PLHTREFNO PARTNO,
C.PARTNAME1 PARTNAME,
'' SUNIT,
0 QTYSU,
'' ISSUNIT,
0 QTYIU,
B.UNITCODE UNITSU,
B.INVOICE_QTY INVQTYSU,
B.ISSUE_UNIT ISUNIT,
B.ISSUEDQTY ISQTYIU,
'' SUUOM,
0 RCPTQTYSU,
'' IUUOM,
0 ISSUEQTYIU
FROM 
INVANNEXMAIN A,
INVANNEXITEM B,
PARTMASTER C,
VENDMAST D
WHERE A.UNITID=&UID
AND A.INDENTNO BETWEEN '&FR_JONO' AND '&TO_JONO'
AND A.UNITID=B.UNITID
AND A.DOCUMENTNO=B.DOCUMENTNO
AND SUBSTR(A.DOCUMENTNO,2,3)='116'
AND B.PARTNUMBER=C.PARTNUMBER
AND A.CUSTCODE=D.VENDCODE
)
UNION
(
SELECT
'C' CATG,
'MTL SENT THRU MTN  TO SUBCONT.' TYPE,
A.WORKORDERNO JONO,
'' JODT,
A.VENDCODE VCODE,
D.VENDNAME VNAME,
A.WORKORDERNO INDNO,
A.GRINNO DOCNO,
B.PLHTREFNO PARTNO,
C.PARTNAME1 PARTNAME,
'' SUNIT,
0 QTYSU,
'' ISSUNIT,
0 QTYIU,
B.STD_UNIT UNITSU,
B.RECD_QTY_SU INVQTYSU,
'' ISUNIT,
B.ACCEPTED_QTY_IU ISQTYIU,
'' SUUOM,
0 RCPTQTYSU,
'' IUUOM,
0 ISSUEQTYIU
FROM
GRINMAIN A,
GRINITEM B,
PARTMASTER C,
VENDMAST D
WHERE A.UNITID=&UID
AND A.WORKORDERNO BETWEEN '&FR_JONO' AND '&TO_JONO'
AND SUBSTR(A.GRINNO,2,3)='130'
AND A.UNITID=B.UNITID
AND A.GRINNO=B.GRINNO
AND B.PARTNUMBER=C.PARTNUMBER
AND A.VENDCODE=D.VENDCODE
)
UNION
(
SELECT
'D' CATG,
'RECEIPT ITEMS' TYPE,
A.PURORDERNO JONO,
'' JODT,
A.VENDCODE VCODE,
D.VENDNAME VNAME,
'' INDNO,
A.GRINNO DOCNO,
B.PLHTREFNO PARTNO,
C.PARTNAME1 PARTNAME,
'' SUNIT,
0 QTYSU,
'' ISSUNIT,
0 QTYIU,
'' UNITSU,
0 INVQTYSU,
'' ISUNIT,
0 ISQTYIU,
B.STD_UNIT SUUOM,
B.RECD_QTY_SU RCPTQTYSU,
'' IUUOM,
B.ACCEPTED_QTY_IU ISSUEQTYIU
FROM GRINMAIN A,
GRINITEM B,
PARTMASTER C,
VENDMAST D
WHERE A.UNITID=&UID
AND A.PURORDERNO BETWEEN '&FR_JONO' AND '&TO_JONO'
AND A.UNITID=B.UNITID
AND A.GRINNO=B.GRINNO
AND B.PARTNUMBER=C.PARTNUMBER
AND A.VENDCODE=D.VENDCODE
)
)
Group by CATG,
TYPE,
JONO,
JODT,
VCODE,
VNAME,
INDNO,
DOCNO,
PARTNO,
PARTNAME,
SUNIT,
ISSUNIT,
UNITSU,
ISUNIT,
SUUOM,
IUUOM
ORDER BY JONO, CATG


Please Guide me. how to correct this query.

Thanks & Regards,

CVS
Re: How to sort it. [message #426682 is a reply to message #426681] Sun, 18 October 2009 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>. It sort the Jono, But it not sort the catg field,
Obviously it is a bug. File a Service Request with Oracle.
Re: How to sort it. [message #426684 is a reply to message #426681] Sun, 18 October 2009 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alo you need to learn how to write a query in a way it is easy to read. Puting just one word or one character on first column of each line is NOT a good format.

Regards
Michel
Re: How to sort it. [message #426727 is a reply to message #426681] Mon, 19 October 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show us what results the query is returning, and why you think that the sort is not working.
Re: How to sort it. [message #427565 is a reply to message #426681] Fri, 23 October 2009 04:55 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

The Output is Follows :

C TYPE
--- -------------------------------
A INPUT_MATERIALS
A INPUT_MATERIALS
C MTL SENT THRU MTN TO SUBCONT.
D RECEIPT ITEMS
B MTL SENT THRU DC TO SUBCONT.
B MTL SENT THRU DC TO SUBCONT.



Thanks & Regards,

C Venkatesh
Re: How to sort it. [message #427569 is a reply to message #427565] Fri, 23 October 2009 05:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please log a support ticket, because if that is the outcome of your query, something is TRULY wrong with your database.
Re: How to sort it. [message #427570 is a reply to message #427565] Fri, 23 October 2009 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As your truncated output doesn't include the JONO column, how are we to determine what's going on?

Please repost, including the CATG and JONO columns in the result set.
Re: How to sort it. [message #427575 is a reply to message #427570] Fri, 23 October 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Fri, 23 October 2009 12:05
As your truncated output doesn't include the JONO column, how are we to determine what's going on?

Please repost, including the CATG and JONO columns in the result set.

And align the columns and use code tags or Courier font.

Regards
Michel

Re: How to sort it. [message #427704 is a reply to message #427575] Sat, 24 October 2009 03:05 Go to previous message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

Check if there are NULLS in CATG.
Sometimes NULLS makes the diffrence

If that is the case, use NULLS FIRST or NULLS LAST in order by clause.

Regards
Previous Topic: how to know the view's defined on particlular table (merged)
Next Topic: Find Tables Creation Date & Machine Name
Goto Forum:
  


Current Time: Sun Dec 04 12:39:54 CST 2016

Total time taken to generate the page: 0.20514 seconds