Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: Order By probs ..help me ASAP

    Hi All

    I have one prob with order by caluse...
    I have to display the data in the follow order (names in Measure Column)

    UnitsSold
    SAL
    CRM
    UReturn

    For now i am just inserting values into Measure Column like this

    A UnitsSold
    B SAL
    C CRM
    D UReturn

    and selecting the date using below code...
    -------------------------------------------

    SELECT Writt_Sales_Dt Writt_Sales_Dt,
    Store_Code Store_Code,
    DelDocNo DelDocNo,
    Substr(Measure,2) Measure,
    Value Value
    FROM WSDTest
    Group by Writt_Sales_Dt,
    Store_Code,
    DelDocNo,
    Measure,
    Value
    ORDER BY Writt_Sales_Dt;
    -------------------------------------------------
    Which satisfies my requirement....

    Instead of this apporach...is there any other way to go about it...as this like hard coded
    I dont want to do like this.....So please help me in this ASAP

    This is sample code for testing...

    ----------------------------------------------------

    create table WSDTest(Writt_Sales_Dt Date,Store_Code Varchar2(2),DelDocNo Varchar2(2) ,Measure Varchar2(50),Value Varchar2(5))

    Select * from WSDTest

    Insert into WSDTest values('01-JAN-2000','01','00','UReturn','50')
    Insert into WSDTest values('01-JAN-2000','01','00','SAL','50')
    Insert into WSDTest values('01-JAN-2000','01','00','CRM','50')
    Insert into WSDTest values('01-JAN-2000','01','00','UnitsSold','50')

    Insert into WSDTest values('02-JAN-2000','01','00','UReturn','50')
    Insert into WSDTest values('02-JAN-2000','01','00','SAL','50')
    Insert into WSDTest values('02-JAN-2000','01','00','CRM','50')
    Insert into WSDTest values('02-JAN-2000','01','00','UnitsSold','50')

    SELECT Writt_Sales_Dt Writt_Sales_Dt,
    Store_Code Store_Code,
    DelDocNo DelDocNo,
    Measure Measure,
    Value Value
    FROM WSDTest
    Group by Writt_Sales_Dt,
    Store_Code,
    DelDocNo,
    Measure,
    Value
    ORDER BY Writt_Sales_Dt;

    Here i am inserting without A,B,C ....for any of the measure just
    for test..u can see the difference from above SQL and First SQL
    -------------------------------------------------------------------


    Thanks
    Suryadevara

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do this with CASE or DECODE:
    Code:
    ORDER BY DECODE (Measure,'UnitsSold',1,'SAL',2,'CRM',3,'UReturn',4)
    
    ORDER BY CASE Measure WHEN 'UnitsSold' THEN 1
                          WHEN 'SAL' THEN 2
                          WHEN 'CRM' THEN 3
                          WHEN 'UReturn' THEN 4
                  END
    If you are not on 9i then CASE will not work within PL/SQL (but will work in pure SQL).

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    Thx a lot it's work well..and solves my prob tooo...
    Suryadevara

  4. #4
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    Thx a lot it's work well..and solves my prob tooo...
    Suryadevara

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •