This discussion is archived
8 Replies Latest reply: Dec 11, 2012 4:25 AM by 936666 RSS

Procedure for order by

936666 Newbie
Currently Being Moderated
Hi,

create table test_table (
RID number,
STARTID number,
ENDID number,
ID number);

say im having some 1000 records in this table i need to fetch on the basis of param1 and param2
some times param2 might have value are might not have value

SELECT RID, STARTID, ENDID, ID FROM test_table ;

DECLARE param1 number :=1;
param2 number;

begin
SELECT RID, STARTID, ENDID, ID FROM test_table order by <param1>;
end;

Need to create SP for the above mention condition

Thanks!
  • 1. Re: Procedure for order by
    LPS Journeyer
    Currently Being Moderated
    Use the case statement in the order by clause and check for is null or is not null.

    SELECT RID, STARTID, ENDID, ID FROM test_table order by CASE WHEN param2 is null then param1 else param1,param2 end ;
  • 2. Re: Procedure for order by
    Ora Pro
    Currently Being Moderated
    DECLARE param1 number :=1;
    param2 number;

    begin
    SELECT RID, STARTID, ENDID, ID FROM test_table order by <param1>;
    end;
    Do you think this anonymous block will execute?
  • 3. Re: Procedure for order by
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    Hi,

    create table test_table (
    RID number,
    STARTID number,
    ENDID number,
    ID number);

    say im having some 1000 records in this table i need to fetch on the basis of param1 and param2
    some times param2 might have value are might not have value
    What do you intend to do with the 1000 records that will be fetched? Just display it using DBMS_OUTPUT or Insert into some Temp Table or Write it to a File?
    SELECT RID, STARTID, ENDID, ID FROM test_table ;

    DECLARE param1 number :=1;
    param2 number;

    begin
    SELECT RID, STARTID, ENDID, ID FROM test_table order by <param1>;
    end;

    Need to create SP for the above mention condition
    I will charge you, say 1500 USD for doing the job. Do you agree?

    Please learn, will you? You are asking people on a Forum to write a procedure for you; so that you can just copy and paste it into your project/assignment etc. Trust me, people have got better things to do than to write an entire procedure for you. If you are asking here, you must post what your best effort it. And the one that is posted is nowhere close to what can be called as Best. You have just posted very Vague and in-adequate requirements.

    Read {message:id=9360002} and post a reply to thread/update original with the relevant details and with your try with a demo stating the point where you get stuck. People will be more than happy to help you get rid of error you are facing.
  • 4. Re: Procedure for order by
    Ora Pro
    Currently Being Moderated
    Purvesh K wrote:
    I will charge you, say 1500 USD for doing the job. Do you agree?
    lol
  • 5. Re: Procedure for order by
    BluShadow Guru Moderator
    Currently Being Moderated
    LPS wrote:
    Use the case statement in the order by clause and check for is null or is not null.

    SELECT RID, STARTID, ENDID, ID FROM test_table order by CASE WHEN param2 is null then param1 else param1,param2 end ;
    You really should test your 'solutions' before you post them.

    You cannot supply multiple outpus from a CASE statement to be used in an Order by clause like that...
    SQL> select * from emp order by case when :param2 is null then hiredate else deptno, hiredate end
      2  /
    select * from emp order by case when :param2 is null then hiredate else deptno, hiredate end
                                                                                  *
    ERROR at line 1:
    ORA-00905: missing keyword
  • 6. Re: Procedure for order by
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    Hi,

    create table test_table (
    RID number,
    STARTID number,
    ENDID number,
    ID number);

    say im having some 1000 records in this table i need to fetch on the basis of param1 and param2
    some times param2 might have value are might not have value

    SELECT RID, STARTID, ENDID, ID FROM test_table ;

    DECLARE param1 number :=1;
    param2 number;

    begin
    SELECT RID, STARTID, ENDID, ID FROM test_table order by <param1>;
    end;
    Please explain clearly what you are trying to achieve, because your question does not make sense.

    You say you need to fetch data based on param1 and param2, but then your example code shows a query that is just ordering by param1, and no indication of what param2 is supposed to be used for.

    a) you cannot just put a select statement inside a PL/SQL execution block without selecting the data INTO something
    b) You've not explained why you need a (stored) procedure, and to be honest I can't see any reason why you would need one.
    Need to create SP for the above mention condition
    What condition? There is no condition in any query you've posted. A condition would typically be a WHERE clause, but you have none, so how can anybody help you if they don't know what the condition is?

    Please read {message:id=9360002} and post appropriate details.
  • 7. Re: Procedure for order by
    BluShadow Guru Moderator
    Currently Being Moderated
    The closest I can think of, based on the poor requirements given would be something like...
    SQL> var param1 number
    SQL> var param2 number
    SQL> exec :param1 := 5
    
    PL/SQL procedure successfully completed.
    At this point param2 is null, so selecting our data...
    SQL> ed
    Wrote file afiedt.buf
    
      1* select * from emp order by case when :param2 is not null then decode(:param1,1,empno,2,ename,5,to_char(hiredate,'J')) else null end
    SQL> /
    
         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
          7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20
          7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
          7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
    
    14 rows selected.
    doesn't apply any order to our results

    Now if we set param2 to a non null value...
    SQL> exec :param2 := 1;
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from emp order by case when :param2 is not null then decode(:param1,1,empno,2,ename,5,to_char(hiredate,'J')) else null end
      2  /
    
         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
          7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
          7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
          7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20
          7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20
    
    14 rows selected.
    The data becomes ordered based on the value in param1, as we've defined.
  • 8. Re: Procedure for order by
    936666 Newbie
    Currently Being Moderated
    .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points