Home » SQL & PL/SQL » SQL & PL/SQL » order by clause
order by clause [message #283703] Wed, 28 November 2007 00:30 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL>
select substr (b.trea_code , 1 , 2 ) ,
rec_no , head_code , substr(b.v_no , 2 , 4) hd,
b.v_no , p.v_no , b.v_date , p_nplan , v_charged ,
ddo_code , to_number(grant_no) , p.gross_amt
from  bill_ent b , pay_det p where b.v_no = p.v_no
and b.trea_code = p.trea_code
and b.v_date = p.V_DATE
and
b.v_date between '01-JUN-07' and '30-JUN-07'
and grant_no not in ('REC' , 'PAC' , 'PEN') union  
select substr(b.trea_code , 1 , 2) ,
rec_no , head_code , substr(b.v_no , 2 ,4) hd,
b.v_no , b.v_no , b.v_date , p_nplan , v_charged,
ddo_code , to_number(grant_no) , b.gross_amt
from  bill_ent b where
b.v_date between '01-JUN-07' and '30-JUN-07'
and grant_no not in ( 'REC' , 'PAC' , 'PEN' )
and substr(b.v_no , 1 , 1 ) <> 'A' order by b.v_no ;

/
ERROR at line 19:
ORA-00904: "B"."V_NO": invalid identifier

HOw to use order by clause?

[Updated on: Wed, 28 November 2007 01:25]

Report message to a moderator

Re: order by clause [message #283707 is a reply to message #283703] Wed, 28 November 2007 00:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

and grant_no not in ('REC' , 'PAC' , 'PEN') order by b.v_no

Use only ONE order by clause.
Re: order by clause [message #283709 is a reply to message #283703] Wed, 28 November 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you have to learn how to write a query in a pretty way, it is then easier to debug.
There it is just a mess and I am amazed that Mohammad Taj saw the point so quickly.

Regards
Michel
Re: order by clause [message #283711 is a reply to message #283703] Wed, 28 November 2007 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you explain what the sense is in this logic:

I have some bags. All contain balls with numbers on it.

Take some balls out some bags and put them in order.
Now take some more balls out of some bags and put them in order.
Finally, put the two results together and filter out the duplicates.

There is no sense in the intermediat ordering! The end-result will not be ordered, unless you do an order by over the union-ed set!
Re: order by clause [message #283728 is a reply to message #283703] Wed, 28 November 2007 01:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Remove First order by

select substr (b.trea_code , 1 , 2 ) ,
             rec_no , head_code , substr(b.v_no , 2 , 4) hd,
             b.v_no , p.v_no , b.v_date , p_nplan , v_charged ,
             ddo_code , to_number(grant_no) , p.gross_amt
      from  bill_ent b , pay_det p 
      where b.v_no = p.v_no
      and b.trea_code = p.trea_code
      and b.v_date = p.V_DATE
      and b.v_date between '01-JUN-07' and '30-JUN-07'
      and grant_no not in ('REC' , 'PAC' , 'PEN') --
      union  --line 11
      select substr(b.trea_code , 1 , 2) ,
             rec_no , head_code , substr(b.v_no , 2 ,4) hd,
             b.v_no , b.v_no , b.v_date , p_nplan , v_charged,
            ddo_code , to_number(grant_no) , b.gross_amt
      from  bill_ent b 
      where b.v_date between '01-JUN-07' and '30-JUN-07'
      and grant_no not in ( 'REC' , 'PAC' , 'PEN' )
      and substr(b.v_no , 1 , 1 ) <> 'A' order by b.v_no 


Thumbs Up
Rajuvan

[Updated on: Wed, 28 November 2007 01:06]

Report message to a moderator

Re: order by clause [message #283734 is a reply to message #283703] Wed, 28 November 2007 01:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=10 ORDER BY EMPNO
  2  UNION
  3  SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 ORDER BY EMPNO;
 UNION
 *
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=10 ORDER BY EMPNO
  2  UNION
  3  SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 ;
 UNION
 *
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=10
  2  UNION
  3  SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 ORDER BY EMPNO;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7566 JONES
      7782 CLARK
      7788 SCOTT
      7839 KING
      7876 ADAMS
      7902 FORD
      7934 MILLER

8 rows selected.

SQL>


Hope it is clear now

Thumbs Up
Rajuvan.
Re: order by clause [message #283736 is a reply to message #283734] Wed, 28 November 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be clearer in the following way:
SQL> SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=10
  2  UNION
  3  SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 
  4  ORDER BY EMPNO;

That is:
<Set of subqueries separated by UNION, UNION ALL, INTERSECT, MINUS>
[optional ORDER BY clause]


Regards
Michel

[Updated on: Wed, 28 November 2007 01:14]

Report message to a moderator

Re: order by clause [message #283741 is a reply to message #283703] Wed, 28 November 2007 01:23 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member

And you have to learn how to write a query in a pretty way, it is then easier to debug

Michel,I am learning
Can you explain what the sense is in this logic

You have a point.
Now as suggested i have modified the above query
Now it returned
ERROR at line 19:
ORA-00904: "B"."V_NO": invalid identifier

[Updated on: Wed, 28 November 2007 01:27]

Report message to a moderator

Re: order by clause [message #283742 is a reply to message #283741] Wed, 28 November 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I have modified the above query

How?
Copy and paste your screen (with line numbers)

Regards
Michel
Re: order by clause [message #283743 is a reply to message #283703] Wed, 28 November 2007 01:28 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Michel, modified the query in the first post
Re: order by clause [message #283744 is a reply to message #283703] Wed, 28 November 2007 01:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ORA-00904: "B"."V_NO": invalid identifier

Quote:

ORA-00904: string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

[Updated on: Wed, 28 November 2007 01:34] by Moderator

Report message to a moderator

Re: order by clause [message #283747 is a reply to message #283703] Wed, 28 November 2007 01:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Did it happen to you like..

SQL>  SELECT EMPNO, ENAME NAME FROM EMP WHERE DEPTNO=10
  2   UNION
  3   SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 order by  ENAME;
 SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 order by  ENAME
                                                        *
ERROR at line 3:
ORA-00904: "ENAME": invalid identifier


Then try with Alias

SQL> SELECT EMPNO, ENAME NAME FROM EMP WHERE DEPTNO=10
  2  UNION
  3  SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=20 order by NAME;

     EMPNO NAME
---------- ----------
      7876 ADAMS
      7782 CLARK
      7902 FORD
      7566 JONES
      7839 KING
      7934 MILLER
      7788 SCOTT
      7369 SMITH

8 rows selected.

SQL>


Otherwise check the existence of v_no in "B"

Thumbs Up
Rajuvan

[Updated on: Wed, 28 November 2007 01:35] by Moderator

Report message to a moderator

Re: order by clause [message #283750 is a reply to message #283703] Wed, 28 November 2007 01:37 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
select substr (b.trea_code , 1 , 2 ) ,

rec_no , head_code , substr(b.v_no , 2 , 4) hd,

b.v_no , p.v_no , b.v_date , p_nplan , v_charged ,

ddo_code , to_number(grant_no) , p.gross_amt

from  bill_ent b , pay_det p where b.v_no = p.v_no

and b.trea_code = p.trea_code

and b.v_date = p.V_DATE

and

b.v_date between '01-JUN-07' and '30-JUN-07'

and grant_no not in ('REC' , 'PAC' , 'PEN') 
[B]UNION[/B]  

select substr(b.trea_code , 1 , 2) ,

rec_no , head_code , substr(b.v_no , 2 ,4) hd,

b.v_no , b.v_no , b.v_date , p_nplan , v_charged,

ddo_code , to_number(grant_no) , b.gross_amt

from  bill_ent b where

b.v_date between '01-JUN-07' and '30-JUN-07'

and grant_no not in ( 'REC' , 'PAC' , 'PEN' )

and substr(b.v_no , 1 , 1 ) <> 'A' order by b.v_no ;
 

ERROR at line 19:
ORA-00904: "B"."V_NO": invalid identifier

I know about the error but i don't know why its occuring.
If run just
select substr(b.trea_code , 1 , 2) ,

rec_no , head_code , substr(b.v_no , 2 ,4) hd,

b.v_no , b.v_no , b.v_date , p_nplan , v_charged,

ddo_code , to_number(grant_no) , b.gross_amt

from  bill_ent b where

b.v_date between '01-JUN-07' and '30-JUN-07'

and grant_no not in ( 'REC' , 'PAC' , 'PEN' )

and substr(b.v_no , 1 , 1 ) <> 'A' order by b.v_no ;

It works fine.
Re: order by clause [message #283758 is a reply to message #283750] Wed, 28 November 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you
think the query you
posted is
readable?

Regards
Michel
Re: order by clause [message #283762 is a reply to message #283703] Wed, 28 November 2007 01:50 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Well, i have written the query so its readable for me.
I tried to format it to make it readable for others also.
How more can i format it?
Its a query after all
Re: order by clause [message #283765 is a reply to message #283762] Wed, 28 November 2007 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using the SQL Formatter page of OraFAQ, for instance, as requested in the OraFAQ Forum Guide.

Regards
Michel
Re: order by clause [message #283776 is a reply to message #283703] Wed, 28 November 2007 02:22 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thanks michel,In future i will take care
SELECT Substr(b.trea_Code,1,2),
       rec_No,
       Head_Code,
       Substr(b.v_No,2,4) hd,
       b.v_No,
       p.v_No,
       b.v_Date,
       p_nPlan,
       v_Charged,
       dDo_Code,
       To_number(Grant_No),
       p.Gross_Amt
FROM   Bill_ent b,
       Pay_det p
WHERE  b.v_No = p.v_No
       AND b.trea_Code = p.trea_Code
       AND b.v_Date = p.v_Date
       AND b.v_Date BETWEEN '01-JUN-07'
                            AND '30-JUN-07'
       AND Grant_No NOT IN ('REC',
                            'PAC',
                            'PEN')
UNION 
SELECT Substr(b.trea_Code,1,2),
       rec_No,
       Head_Code,
       Substr(b.v_No,2,4) hd,
       b.v_No,
       b.v_No,
       b.v_Date,
       p_nPlan,
       v_Charged,
       dDo_Code,
       To_number(Grant_No),
       b.Gross_Amt
FROM   Bill_ent b
WHERE  b.v_Date BETWEEN '01-JUN-07'
                        AND '30-JUN-07'
       AND Grant_No NOT IN ('REC',
                            'PAC',
                            'PEN')
       AND Substr(b.v_No,1,1) <> 'A'
ORDER BY b.v_No;

ERROR at line 19:
ORA-00904: "B"."V_NO": invalid identifier
SELECT   Substr(b.trea_Code,1,2),
         rec_No,
         Head_Code,
         Substr(b.v_No,2,4) hd,
         b.v_No,
         b.v_No,
         b.v_Date,
         p_nPlan,
         v_Charged,
         dDo_Code,
         To_number(Grant_No),
         b.Gross_Amt
FROM     Bill_ent b
WHERE    b.v_Date BETWEEN '01-JUN-07'
                          AND '30-JUN-07'
         AND Grant_No NOT IN ('REC',
                              'PAC',
                              'PEN')
         AND Substr(b.v_No,1,1) <> 'A'
ORDER BY b.v_No;

runs fine
Re: order by clause [message #283782 is a reply to message #283703] Wed, 28 November 2007 02:44 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
After 16 posts, the question remains the same with a brand new formatted query.
Re: order by clause [message #283788 is a reply to message #283776] Wed, 28 November 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, next time also post line numbers (use SQL*Plus it is easier).

Regards
Michel
Re: order by clause [message #283789 is a reply to message #283703] Wed, 28 November 2007 02:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


This is what happned to your query.

SQL>  SELECT E.EMPNO,
  2             JOB ,
  3             E.ENAME
  4             FROM EMP E
  5             WHERE E.DEPTNO=20
  6   UNION
  7   SELECT E.EMPNO,
  8             E.ENAME ,
  9             E.ENAME
 10             FROM EMP E
 11             WHERE E.DEPTNO=10
 12             ORDER BY  E.ENAME;
                ORDER BY  E.ENAME
                          *
ERROR at line 12:
ORA-00904: "E"."ENAME": invalid identifier


Try with Alias

SQL>  SELECT E.EMPNO,
  2             JOB ,
  3             E.ENAME NAM
  4             FROM EMP E
  5             WHERE E.DEPTNO=20
  6   UNION
  7   SELECT E.EMPNO,
  8             E.ENAME ,
  9             E.ENAME
 10             FROM EMP E
 11             WHERE E.DEPTNO=10
 12             ORDER BY  NAM
 13  ;

     EMPNO JOB        NAM
---------- ---------- ----------
      7876 CLERK      ADAMS
      7782 CLARK      CLARK
      7902 ANALYST    FORD
      7566 MANAGER    JONES
      7839 KING       KING
      7934 MILLER     MILLER
      7788 ANALYST    SCOTT
      7369 SALESMAN   SMITH

8 rows selected.

SQL>


Thumbs Up
Rajuvan
Re: order by clause [message #283797 is a reply to message #283703] Wed, 28 November 2007 03:08 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thnaks rajavu.
But if i provide an alias in the second query it raises invalid identifier value.
Re: order by clause [message #283800 is a reply to message #283703] Wed, 28 November 2007 03:15 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi ,

Why can't you post the query you tried again ? Again repeating the same mistake and ignoring the advice from Michel ?


try like.

SELECT Substr(b.trea_Code,1,2),
       rec_No,
       Head_Code,
       Substr(b.v_No,2,4) hd,
       b.v_No bvno1,
       p.v_No pvno1,
       b.v_Date,
       p_nPlan,
       v_Charged,
       dDo_Code,
       To_number(Grant_No),
       p.Gross_Amt
FROM   Bill_ent b,
       Pay_det p
WHERE  b.v_No = p.v_No
       AND b.trea_Code = p.trea_Code
       AND b.v_Date = p.v_Date
       AND b.v_Date BETWEEN '01-JUN-07'
                            AND '30-JUN-07'
       AND Grant_No NOT IN ('REC',
                            'PAC',
                            'PEN')
UNION 
SELECT Substr(b.trea_Code,1,2),
       rec_No,
       Head_Code,
       Substr(b.v_No,2,4) hd,
       b.v_No ,
       b.v_No,
       b.v_Date,
       p_nPlan,
       v_Charged,
       dDo_Code,
       To_number(Grant_No),
       b.Gross_Amt
FROM   Bill_ent b
WHERE  b.v_Date BETWEEN '01-JUN-07'
                        AND '30-JUN-07'
       AND Grant_No NOT IN ('REC',
                            'PAC',
                            'PEN')
       AND Substr(b.v_No,1,1) <> 'A'
ORDER BY bvno1;


If in case any issue , Try to post the issue with error and line Number.

Thumbs Up
Rajuvan.
Re: order by clause [message #283838 is a reply to message #283782] Wed, 28 November 2007 04:52 Go to previous messageGo to next message
kakolz
Messages: 4
Registered: November 2007
Junior Member
varu123 wrote on Wed, 28 November 2007 02:44

After 16 posts, the question remains the same...

I think there exist also 2 additional solutions of the problem:
1. try to put simply 'v_No' rather than 'b.N_no' in order by clause (see what is the name of this column after select without clause 'order by')
2. try to put 'order by 5' (5 is the number of this column in select statement).
Let me know if it works.
Re: order by clause [message #283959 is a reply to message #283776] Wed, 28 November 2007 08:02 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
varu123 wrote on Wed, 28 November 2007 03:22



       AND b.v_Date = p.v_Date
       AND b.v_Date BETWEEN '01-JUN-07'
                            AND '30-JUN-07'





Over 150 posts and you still don't know what a DATE is? It may work in your case, but it won't work for most people. Just one example of a case where it doesn't work makes your code worthless. Please read the concepts manual on datatypes.
FOO SCOTT>l
  1  select 1 from dual
  2* where sysdate between '01-JUN-07' and '30-JUN-07'
FOO SCOTT>/
where sysdate between '01-JUN-07' and '30-JUN-07'
                                      *
ERROR at line 2:
ORA-01843: not a valid month

[Updated on: Wed, 28 November 2007 08:04]

Report message to a moderator

Re: order by clause [message #283967 is a reply to message #283703] Wed, 28 November 2007 08:30 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

Over 150 posts and you still don't know what a DATE is?

I didn't get you Joy division.
SQL>select 1 from dual where sysdate between '01-JUN-07' and '30-JUN-07';
no rows selected

i don't see any not a valid month error.
Quote:

Please read the concepts manual on datatypes.

Surely i will
Re: order by clause [message #283969 is a reply to message #283967] Wed, 28 November 2007 08:38 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:

I didn't get you Joy division.


'01-JUN-07' Is not a date, it is a string. do not compare dates to strings. explicitly convert strings to dates for correct comparisons
Re: order by clause [message #284571 is a reply to message #283969] Fri, 30 November 2007 02:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@varu123,
pablolee already gave you a bit more explanation. But what joy_division is trying to tell you is that you give strings to Oracle but you want to use them as dates. Oracle then tries to convert the strings to a date by using your default nls settings. This is called implicit casting. It might seem to work just fine but it is very unstable. Consider the following example:
SQL> REM Default NLS settings:
SQL> select 1 from dual where sysdate between '01-FEB-07' and '20-FEB-07';

no rows selected

SQL>
SQL> REM Changed date language:
SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH'
  2  /

Session altered.

SQL>
SQL> select 1 from dual where sysdate between '01-FEB-07' and '20-FEB-07';
select 1 from dual where sysdate between '01-FEB-07' and '20-FEB-07'
                                                         *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>
SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH'
  2  /

Session altered.

SQL> REM Changed date format
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'
  2  /

Session altered.

SQL> select 1 from dual where sysdate between '01-FEB-07' and '20-FEB-07';
select 1 from dual where sysdate between '01-FEB-07' and '20-FEB-07'
                                                         *
ERROR at line 1:
ORA-01843: not a valid month
Now, what would happen if we change the query like this:
select 1 from dual where sysdate between TO_DATE('01-FEB-07','DD-MON-YYYY') 
                                           and TO_DATE('20-FEB-07','DD-MON-YYYY');
It will work in most cases, except for the date language. Oracle will have problems with the MON format when the language is French (February is "février" in French). Although this is very rare, and a language is usually fixed, you can even add a third parameter to the TO_DATE telling Oracle in what language your date is:
select 1 from dual where sysdate between TO_DATE('01-FEB-07'
                                                ,'DD-MON-YYYY'
                                                ,'NLS_DATE_LANGUAGE=ENGLISH') 
                                     and TO_DATE('20-FEB-07'
                                                ,'DD-MON-YYYY'
                                                ,'NLS_DATE_LANGUAGE=ENGLISH');

In short: don't rely on implicit casting, use a TO_DATE (just the date format will do in most cases). Make your dates true dates before handing them over to a query Wink.

MHE
Re: order by clause [message #284572 is a reply to message #284571] Fri, 30 November 2007 02:29 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, 'MON' format gives abbreviated month name which can no more be with 3 characters since 10g (it was always 3 characters in previous version):
SQL> select to_char(sysdate,'MON') from dual;
TO_CH
-----
NOV.

1 row selected.

This is why I almost always use month numbers that are common to all languages (as long as you use gregorian calendar).

Regards
Michel
Previous Topic: problem in creating Hash Partition
Next Topic: how to place a decimal point into a number at a desired position
Goto Forum:
  


Current Time: Thu Dec 08 18:26:45 CST 2016

Total time taken to generate the page: 0.11581 seconds