0

I'm having problems sorting data in interbase, my timestamp is in seperate columns of year,month,date, etc so I need to order in that order (though in this case it wouldn't work even to order by a single field). For some reason the ORDER command throws a 'token unknown' error when used in combination with CREATE VIEW or INSERT INTO. Am I making a dumb syntax error here, or is there a more fundamental issue with the way I'm trying to ude the ORDER command?

CREATE VIEW HOURLY_AVES_SORT (MEASYEAR,MEASMONTH,MEASDAY,MEASHOUR,GH,GK,GDH,TA) as
SELECT
  HOURLY_AVES_ALL.MEASYEAR, HOURLY_AVES_ALL.MEASMONTH, HOURLY_AVES_ALL.MEASDAY,
  HOURLY_AVES_ALL.MEASHOUR, /*HOURLY_AVES.MEASMIN, HOURLY_AVES_ALL.MEASSECOND, */
  HOURLY_AVES_ALL.GH, HOURLY_AVES_ALL.GK, HOURLY_AVES_ALL.GDH, HOURLY_AVES_ALL.TA

FROM HOURLY_AVES

ORDER BY
   HOURLY_AVES_ALL.MEASYEAR, HOURLY_AVES_ALL.MEASMONTH, HOURLY_AVES_ALL.MEASDAY,
  HOURLY_AVES_ALL.MEASHOUR

trying to copy the data into another table rather than a view, throws the same token unknown error, ie:

INSERT INTO HOURLY_AVES_SORTED 
(MEASYEAR,MEASMONTH,MEASDAY,MEASHOUR,GH,GK,GDH,TA)

SELECT 
HOURLY_AVES_ALL.MEASYEAR, HOURLY_AVES_ALL.MEASMONTH, HOURLY_AVES_ALL.MEASDAY,
HOURLY_AVES_ALL.MEASHOUR, /*HOURLY_AVES.MEASMIN, HOURLY_AVES_ALL.MEASSECOND, */
HOURLY_AVES_ALL.GH, HOURLY_AVES_ALL.GK, HOURLY_AVES_ALL.GDH, HOURLY_AVES_ALL.TA

FROM HOURLY_AVES

ORDER BY
(MEASYEAR, MEASMONTH, MEASDAY,MEASHOUR, GH, GK,GH, TA)

any tips very welcome, Brian

Hamish_Fernsby
  • 558
  • 1
  • 7
  • 28
  • Whats the version of Interbase youre using? The old versions of Interbase (ie the version 6 which were open sourced) don't support `ORDER BY` clause in view's `SELECT` statement. Firebird implemented it in version 2, I'm not sure about Interbase. – ain Nov 14 '12 at 19:48
  • Hi, I'm using EMS SQL manager with Interbase 2009 – Hamish_Fernsby Nov 14 '12 at 20:21
  • An ORDER BY in a view doesn't make sense. A view is essentially like a table - and that is ***not*** ordered. Just add an order by when you select from the view. –  Nov 14 '12 at 21:04
  • It makes sense in a case where view would only selext x first records - then you want to have the `order by` to get the desired result. – ain Nov 14 '12 at 21:22

2 Answers2

2

Interbase doesn't support ORDER BY clause in the SELECT statement of the view. See the documentation, page 8-4 in the Data Definition Guide (youll find the page when selecting from the bookmarks "Working with views" -> "Creating views" -> "Using the SELECT Statement").

ain
  • 22,394
  • 3
  • 54
  • 74
  • hmm, ok, I get the impression there is also a conflict with using INSERT INTO and ORDER. If I use ORDER with neither INSERT INTO or CREATE view it works ok. I may just work around this by running the SELECT..ORDER by query and save as csv then re-import the data. – Hamish_Fernsby Nov 14 '12 at 20:55
  • What is a reason in ordered insert? – Andrej Kirejeŭ Nov 14 '12 at 21:59
  • Andrej, good point. I'm used to seeing my data in timedate order as it's easier to spot quickly if something isn't right. but it's true that I could order it as a seperate SELECT-ORDER BY script to view or export, and not worry so much if it isn't in order in between. – Hamish_Fernsby Nov 15 '12 at 10:04
  • Sorry, the site only allows me to 'accept' 1 valid answer. In this case as is often the case both Ains and Andrejs answers were helpful. I have given Andrej an 'accept' for another question, so hopefully everyone is content :-) – Hamish_Fernsby Nov 15 '12 at 10:08
  • @AndrejKirejeŭ If the table contains hierarchical data then you want to insert parent record before you insert child(s) - so you need the order by clause in the select statement which is source for the insert. – ain Nov 15 '12 at 15:23
  • I see. Another solution is to drop ref constraints, load an unordered data and restore constraints. – Andrej Kirejeŭ Nov 15 '12 at 15:49
1

Try to use column numbers:

CREATE VIEW HOURLY_AVES_SORT (MEASYEAR,MEASMONTH,MEASDAY,MEASHOUR,GH,GK,GDH,TA) as
SELECT
  h.MEASYEAR, h.MEASMONTH, h.MEASDAY, h.MEASHOUR, 
  h.GH, h.GK, h.GDH, h.TA

FROM 
  HOURLY_AVES h

ORDER BY
   1, 2, 3, 4
Andrej Kirejeŭ
  • 5,381
  • 2
  • 26
  • 31
  • Andrej, thanks for the tip, I hadn't come across column numbers, sadly it still threw a 'token unknown' error when using both INSERT INTO/CREATE VIEW and ORDER BY. – Hamish_Fernsby Nov 14 '12 at 20:58