Need help on Sorting...

Question

Need help on Sorting...

viralbpandya

SSC Enthusiast

Points: 112
More actions
May 12, 2016 at 2:03 am

#313854

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

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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: 112 More actions · Answer 2

viralbpandya

SSC Enthusiast

Points: 112

May 13, 2016 at 4:57 am

#1878862

is it possible to sort data other way?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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: 183698 More actions · Answer 5

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: 442467 More actions · Answer 6

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.