CODEWITHSUNDEEP
sqloracle-databaseselectoracle-apex
user4196435
user4196435

Reputation:

SQL - ordering does not work with LENGTH function

I'm trying to sort out my query based on V.SITES_CODE. It works without the LENGTH function, but with the function the order is completely random. Can someone help here is the code:

SELECT     V.CODE_OF_HOLIDAY, V.SITES_CODE, A.SITES_NAME, D.DESCRIPTION
FROM       ZZZ_SITES_VISITED V, ZZZ_SITES_AVAILABLE A, ZZZ_HOLIDAY_DETAILS H, ZZZ_DIFFICULTY_RATINGS D
WHERE      V.CODE_OF_HOLIDAY = H.CODE_OF_HOLIDAY
AND        A.SITES_CODE = V.SITES_CODE(+)
AND        H.DIFFICULTY_RATING = D.HOLIDAY_DIFICULTY
AND        H.CODE_OF_HOLIDAY = V.CODE_OF_HOLIDAY
AND        LENGTH(D.DESCRIPTION) > 6;

Upvotes: 1

Views: 227

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Oracle does not have any default sorting. you MUST explicitly specify ORDER BY clause to reply on the sorting.

Add,

ORDER BY V.SITES_CODE

Upvotes: 1

Mureinik
Mureinik

Reputation: 311403

Unless you explicitly specify an order by clause, there is absolutely no guarantee on the order of rows returned. The fact that you got them in the order you expected when you omitted to length condition is completely coincidental, and may very well break if the table is moved to different storage or the indexes rebuilt.

If you want your result ordered by V.SITES_CODE, you should explicitly state it:

SELECT     V.CODE_OF_HOLIDAY, V.SITES_CODE, A.SITES_NAME, D.DESCRIPTION
FROM       ZZZ_SITES_VISITED V, ZZZ_SITES_AVAILABLE A, ZZZ_HOLIDAY_DETAILS H, ZZZ_DIFFICULTY_RATINGS D
WHERE      V.CODE_OF_HOLIDAY = H.CODE_OF_HOLIDAY
AND        A.SITES_CODE = V.SITES_CODE(+)
AND        H.DIFFICULTY_RATING = D.HOLIDAY_DIFICULTY
AND        H.CODE_OF_HOLIDAY = V.CODE_OF_HOLIDAY
AND        LENGTH(D.DESCRIPTION) > 6
ORDER BY   V.SITES_CODE

Upvotes: 2

Related Questions