×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help with sort order.

Help with sort order.

Help with sort order.

(OP)
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

RE: Help with sort order.

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

CODE

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:

CODE

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

RE: Help with sort order.

(OP)
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

RE: Help with sort order.

I would expect your example to fail. You had:

Quote:

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:

CODE

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

RE: Help with sort order.

(OP)
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

RE: Help with sort order.

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

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Help with sort order.

(OP)
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

RE: Help with sort order.

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

RE: Help with sort order.

(OP)
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

RE: Help with sort order.

Quote:

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.

Quote:

...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

RE: Help with sort order.

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close