8 Replies Latest reply: Dec 11, 2012 6:25 AM by 936666 RSS

    Procedure for order by

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        .