Help with sort order.

[Djbell]

Hi All

I have the following View / Query

CREATE VIEW "Top3Results" AS SELECT TOP 3 "T" ."CalcTime" ,"T" ."Reason" ,(SELECT COUNT (*)FROM "GBO5DSLNoMeals" "T1" WHERE "T1" ."CalcTime" > "T" ."CalcTime" )"NUMBER" FROM "GBO5DSLNoMeals" "T"

This works great, but i want the CalcTime displayed in descending order, so at the end of the query i added

ORDER BY "T" ."CalcTime" DESC

When i add this i get an error on the order ORDER<<<?>>>, am i doing something silly on the Order By?

Regards

Djbell

 

[mirtheil]

I don't believe you can have an ORDER BY in a view. I tried:

create view v1 as select top 3 * from class order by ID

and got the same error.
You can add an ORDER BY when you select from the view:

create view v1 as select top 3 * from class;
select * from v1 order by ID

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi Mirtheil

I have tried your suggestion identically apart from changing class and ID to the appropriate View and Field and I now get the error on the second select..

create view v1 as select top 3 * from GBO5DSLNoMeals
select * from v1 order by CalcTime

The error i get now is.

[LNA][Pervasive][ODBC Engine Interface]Syntax Error: create view v1 as select top 3 * from GBO5DSLNoMeals
select<<???>>* from v1 order by CalcTime

I am creating the view through Pervasive.SQL Control Centre Version 9.1

I just dont get pervasive, creating a sort order is usually a standard criteria in SQL so why it doesnt work in Pervasive is really bugging me.

Regards

djbell

 

[mirtheil]

I would expect your example to fail. You had:

create view v1 as select top 3 * from GBO5DSLNoMeals
select * from v1 order by CalcTime

There are two SELECT statements there with no separator. If you're using the PCC, you need to change it to:

create view v1 as select top 3 * from GBO5DSLNoMeals#
select * from v1 order by CalcTime

(Note the "#" which is a statement separator.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi Mirtheil

Thanks for your reply, I have changed to suit your above example it now gives me the <<???>> error between the GBO5DSLNoMeals name and the # statement seperator

Basically all I want to do is look up a view and display 3 records with the highest figures in them, at the moment I can get it to display 3 figures but these are just the top 3 in the view, I need to put the view in descending order so it displays the 3 highets values..

Regards

Djbell

 

[mirtheil]

You might try changing the "#" to a ";". Both are used as statement separators.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi yeah tried the ; first it just ignores the second select.

Can what am trying to do be done a different way without using a sort order??

Regards djbell

 

[mirtheil]

These are two separate statements. YOu need to run them independently. First you create the view then you select from the view. If you are using the PCC, you need to put the cursor into the statement you want to run. It won't automatically run the second statement.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi Mirtheil

What I meant when using the ; statement seperator, when I save the view and reopen it the second select statement is no longer in the view it seems to delete the second statement...

This is the first of me using PSQL so I am not familiar with it, what do u mean by Cursor??

Regards

Douglas Bell

 

[mirtheil]

What I meant when using the ; statement seperator, when I save the view and reopen it the second select statement is no longer in the view it seems to delete the second statement...

That's correct. The view that I gave is very simple. It has one SELECT statement in it. The second statement actually accesses that view.

...what do u mean by Cursor??

The cursor in the PCC SQL Editor is the same cursor as in other programs like Notepad.

I just thought of something that I should have asked before. Why are you wanting a View? Why not just run the SELECT statement?



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi

Thanks for your help, as you can tell am new at SQL not just PSQL..

I have an external VB.net program that will use the information from the View to display specific information on a Data Collection Display...

As I say am new to all of this, how would I just run the select statement??

(I appologise for the silly questions, but I guess if you dont ask you dont learn)

Regards

Djbell

 

[mirtheil]

In your VB.NET program, you would execute the SELECT just like you were using the view. Here's a link to another thread on VB.NET and PSQL:

http://www.tek-tips.com/viewthread.cfm?qid=1346380&page=1

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com