Need help on Sorting...

  • viralbpandya

    SSC Enthusiast

    Points: 102

    Can Any one help me on below query?

    _________________

    select WES.CUSTOMER_ORDER_LINE.ORDER_NO,

    WES.CUSTOMER_ORDER_LINE.STATE,

    WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO)TYPE,

    WES.CUSTOMER_ORDER_LINE.CUSTOMER_NO,

    WES.CUSTOMER_INFO_API.Get_Name(CUSTOMER_No),

    WES.CUSTOMER_ORDER_LINE.CATALOG_NO,

    WES.CUSTOMER_ORDER_LINE.CATALOG_DESC,

    WES.CUSTOMER_ORDER_LINE.REVISED_QTY_DUE,

    TO_CHAR(WES.CUSTOMER_ORDER_LINE.PROMISED_DELIVERY_DATE,'IW')UGE,

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'MNO',1,'KNO',1,0)NON_W,

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'RNO',1,0)RES,

    WES.CUSTOMER_ORDER_LINE.WANTED_DELIVERY_DATE,

    WES.CUSTOMER_ORDER_LINE.PROMISED_DELIVERY_DATE,

    WES.CUSTOMER_ORDER_LINE.PLANNED_DELIVERY_DATE,

    WES.CUSTOMER_ORDER_LINE.DATE_ENTERED,

    LINE_NO,

    WES.CUSTOMER_ORDER_API.get_STATE(ORDER_NO) STATE,

    DECODE(WES.sales_part_API.Get_catalog_group(contract,part_no), '10',1,0)SALES_GROUP

    From WES.CUSTOMER_ORDER_LINE

    Where

    (UPPER(WES.CUSTOMER_ORDER_LINE.STATE) like UPPER('Frigivet')

    or

    UPPER(WES.CUSTOMER_ORDER_LINE.STATE) like UPPER('Released')

    or

    UPPER(WES.CUSTOMER_ORDER_LINE.STATE) like UPPER('Reserveret')

    )

    and WES.CUSTOMER_ORDER_LINE.CATALOG_NO not in ('1','4','8','248','251','252','VF','XXXXX','3000','%SERVICE%','DI%','249')

    and WES.CUSTOMER_ORDER_LINE.CATALOG_NO not like 'DI%'

    and WES.CUSTOMER_ORDER_LINE.CATALOG_NO not like '%SERVICE%'

    and WES.CUSTOMER_ORDER_LINE.CATALOG_NO not like 'FRAGT%'

    and WES.CUSTOMER_ORDER_LINE.CATALOG_NO not like 'CONT%'

    and WES.CUSTOMER_ORDER_LINE.CATALOG_DESC not like '%FREIGHT%'

    and WES.CUSTOMER_ORDER_LINE.CATALOG_DESC not like '%FRACHT%'

    and UPPER(WES.CUSTOMER_ORDER_LINE.CATALOG_DESC )not like UPPER('%Ordretilgang%')

    and WES.CUSTOMER_ORDER_API.get_STATE(ORDER_NO) not in ('Planlagt','Spærret')

    order by to_char(WES.CUSTOMER_ORDER_LINE.PLANNED_DELIVERY_DATE,'YYYY-IW') [ASC],

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'MNO',1,'KNO',1,0) [ASC]

    ______________________________

    I am not getting desired result for above.

    I want to sort the data first by "Planned Delivery Data" and than by "Order No".

    Thanks in advance.

    Regards,

    Viral Pandya

  • Luis Cazares

    SSC Guru

    Points: 183578

    Then don't use functions on the ORDER BY clause.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • viralbpandya

    SSC Enthusiast

    Points: 102

    is it possible to sort data other way?

  • ChrisM@Work

    SSC Guru

    Points: 186054

    viralbpandya (5/13/2016)


    is it possible to sort data other way?

    Death by function!

    Functions are overused in this query. The two most significant consequences are obfuscation, which is sometimes good but not in this case, and performance.

    Here's the query reformatted a little and with table aliases in place:

    select

    ol.ORDER_NO,

    ol.STATE,

    WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO) TYPE,

    ol.CUSTOMER_NO,

    WES.CUSTOMER_INFO_API.Get_Name(CUSTOMER_No),

    ol.CATALOG_NO,

    ol.CATALOG_DESC,

    ol.REVISED_QTY_DUE,

    TO_CHAR(ol.PROMISED_DELIVERY_DATE,'IW')UGE,

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'MNO',1,'KNO',1,0) NON_W,

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'RNO',1,0) RES,

    ol.WANTED_DELIVERY_DATE,

    ol.PROMISED_DELIVERY_DATE,

    ol.PLANNED_DELIVERY_DATE,

    ol.DATE_ENTERED,

    LINE_NO,

    WES.CUSTOMER_ORDER_API.get_STATE(ORDER_NO) STATE,

    DECODE(WES.sales_part_API.Get_catalog_group(contract,part_no), '10',1,0) SALES_GROUP

    From WES.CUSTOMER_ORDER_LINE ol

    Where

    (

    UPPER(ol.STATE) like UPPER('Frigivet')

    or

    UPPER(ol.STATE) like UPPER('Released')

    or

    UPPER(ol.STATE) like UPPER('Reserveret')

    )

    and ol.CATALOG_NO not in ('1','4','8','248','251','252','VF','XXXXX','3000','%SERVICE%','DI%','249')

    and ol.CATALOG_NO not like 'DI%'

    and ol.CATALOG_NO not like '%SERVICE%'

    and ol.CATALOG_NO not like 'FRAGT%'

    and ol.CATALOG_NO not like 'CONT%'

    and ol.CATALOG_DESC not like '%FREIGHT%'

    and ol.CATALOG_DESC not like '%FRACHT%'

    and UPPER(ol.CATALOG_DESC) not like UPPER('%Ordretilgang%')

    and WES.CUSTOMER_ORDER_API.get_STATE(ORDER_NO) not in ('Planlagt','Spærret')

    order by

    to_char(ol.PLANNED_DELIVERY_DATE,'YYYY-IW') [ASC],

    DECODE(WES.CUSTOMER_ORDER_API.get_ORDER_ID(ORDER_NO),'MNO',1,'KNO',1,0) [ASC]

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Gail Shaw

    SSC Guru

    Points: 1004446

    This looks like Oracle code.

    While some people do know Oracle, this is a SQL Server forum (as in Microsoft SQL Server), and that's what most answers will be written for.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Luis Cazares

    SSC Guru

    Points: 183578

    viralbpandya (5/12/2016)


    I am not getting desired result for above.

    I want to sort the data first by "Planned Delivery Data" and than by "Order No".

    Use

    ORDER BY WES.CUSTOMER_ORDER_LINE.PLANNED_DELIVERY_DATE [ASC],

    WES.CUSTOMER_ORDER_LINE.ORDER_NO [ASC]

    If you convert dates to strings, it will follow string rules to order (2016-11 would come before 2016-2). The same will happen when converting numbers to strings.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lynn Pettis

    SSC Guru

    Points: 442152

    GilaMonster (5/13/2016)


    This looks like Oracle code.

    While some people do know Oracle, this is a SQL Server forum (as in Microsoft SQL Server), and that's what most answers will be written for.

    The DECODE is a big clue.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply