SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help on Sorting...


Need help on Sorting...

Author
Message
viralbpandya
viralbpandya
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 4
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
Luis Cazares
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102827 Visits: 21424
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
viralbpandya
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 4
is it possible to sort data other way?
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104373 Visits: 20857
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]



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)SSC Guru (594K reputation)

Group: General Forum Members
Points: 594204 Visits: 48031
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
Luis Cazares
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102827 Visits: 21424
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
Lynn Pettis
SSC Guru
SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)

Group: General Forum Members
Points: 241383 Visits: 40689
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search