URGENT !!!! How I can set an order in SQL ... 
Author Message
 URGENT !!!! How I can set an order in SQL ...

Hi,

I have create a database (.DBC) and create a view of an free table...until
here no problem...but now I need to set an order in my view in runtime...if
my client click in a control that control change the order of my view and so
on with others controls.

example:

create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

?yyy ->  here he give me an error telling me that yyy is not a column!!!!

Can someone help me?

TIA



Mon, 05 Sep 2005 02:36:48 GMT  
 URGENT !!!! How I can set an order in SQL ...
You can create indicies on the fly.
-Lew

Quote:
> Hi,

> I have create a database (.DBC) and create a view of an free table...until
> here no problem...but now I need to set an order in my view in
runtime...if
> my client click in a control that control change the order of my view and
so
> on with others controls.

> example:

> create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

> ?yyy ->  here he give me an error telling me that yyy is not a column!!!!

> Can someone help me?

> TIA



Mon, 05 Sep 2005 02:49:43 GMT  
 URGENT !!!! How I can set an order in SQL ...
Hi Aramini,

Not too much to go on from your message, but here's one or two items you
might consider.  First off, you don't need to re-create the view on the
fly like this.  Create the thing offline (design-time) and then just do a
"SELECT * FROM vwAlibaba ORDER BY xxx", where xxx is a valid column in
the view.  If you want to do updates to the data, don't use this method.

Otherwise, your syntax looks fine, as long as yyy is a valid column in
the table.  "CREATE VIEW vwAlibaba AS SELECT * FROM Alibaba WHERE colx =
xxx ORDER BY coly" is proper, if "colx" and "coly" are valid column names
in the table.

Don't know if this helps or not.  If it were me, I'd execute that CREATE
VIEW line from the command window and see what happens.  In fact, just
remove the "CREATE VIEW xxx AS" from it and execute the SELECT statement
in the command window, see if you get what you're looking for.  You may
get an error, which you should be able to easily troubleshoot right
there, then fix it in your control code after you determine what the
problem is.

If this isn't enough help, put up a bit more info on your table, even
specifics, we can write it out for you.

HTH
-michael

Quote:

> Hi,

> I have create a database (.DBC) and create a view of an free
> table...until here no problem...but now I need to set an order in my
> view in runtime...if my client click in a control that control change
> the order of my view and so on with others controls.

> example:

> create view xpto as select * from alibaba where xxx=?nxxx order by
> ?yyy

> ?yyy ->  here he give me an error telling me that yyy is not a
> column!!!!

> Can someone help me?



Mon, 05 Sep 2005 03:09:05 GMT  
 URGENT !!!! How I can set an order in SQL ...

Quote:
> Hi,

> I have create a database (.DBC) and create a view of an free
> table...until here no problem...but now I need to set an order in my
> view in runtime...if my client click in a control that control change
> the order of my view and so on with others controls.

> example:

> create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

> ?yyy ->  here he give me an error telling me that yyy is not a
> column!!!!

> Can someone help me?

You can create an index on the view, then set that as
the order:

SELECT myView
INDEX ON someField TAG mySort
SET ORDER TO mySort

-- TRW
_______________________________________
My e-mail:  t r w 7

_______________________________________



Mon, 05 Sep 2005 03:27:21 GMT  
 URGENT !!!! How I can set an order in SQL ...
Thanks for responding...

But my problem is not that I don't know how to order bey...but is that when
the form is open I order by column 1...but the user can change the order in
runtime...like this:

"xpto" is the view with fields -> col1,col2,col3,col4
spinnerbutton -> here I change  the nxxx value column that I use in my sql
statement ->    xxx=?nxxx
button1 -> order by col1
button2 -> order by col2
button3 -> order by col3
button4 -> order by col4

when the user click on button1 the view must be ordered by col1...but if the
user clicks on button3 the view is ordered by col3...and so on....so if I
put in code:

CREATE SQL VIEW xpto AS SELECT * FROM alibaba WHERE xxx=?nxxx ORDER BY yyy

so if the user click on button3 the value of yyy is col3 or if the user
clicks on button2 the value of yyy is col2 and so on...
my problem is I need to have the same facility as I have in 'xxx=?nxxx', if
I could do the same thing with order by I resolve my problem!!! but how I do
that???

TIA


Quote:
> Hi Aramini,

> Not too much to go on from your message, but here's one or two items you
> might consider.  First off, you don't need to re-create the view on the
> fly like this.  Create the thing offline (design-time) and then just do a
> "SELECT * FROM vwAlibaba ORDER BY xxx", where xxx is a valid column in
> the view.  If you want to do updates to the data, don't use this method.

> Otherwise, your syntax looks fine, as long as yyy is a valid column in
> the table.  "CREATE VIEW vwAlibaba AS SELECT * FROM Alibaba WHERE colx =
> xxx ORDER BY coly" is proper, if "colx" and "coly" are valid column names
> in the table.

> Don't know if this helps or not.  If it were me, I'd execute that CREATE
> VIEW line from the command window and see what happens.  In fact, just
> remove the "CREATE VIEW xxx AS" from it and execute the SELECT statement
> in the command window, see if you get what you're looking for.  You may
> get an error, which you should be able to easily troubleshoot right
> there, then fix it in your control code after you determine what the
> problem is.

> If this isn't enough help, put up a bit more info on your table, even
> specifics, we can write it out for you.

> HTH
> -michael


> > Hi,

> > I have create a database (.DBC) and create a view of an free
> > table...until here no problem...but now I need to set an order in my
> > view in runtime...if my client click in a control that control change
> > the order of my view and so on with others controls.

> > example:

> > create view xpto as select * from alibaba where xxx=?nxxx order by
> > ?yyy

> > ?yyy ->  here he give me an error telling me that yyy is not a
> > column!!!!

> > Can someone help me?



Mon, 05 Sep 2005 03:45:58 GMT  
 URGENT !!!! How I can set an order in SQL ...
Thanks,

I have resolve the problem with your help.


Quote:

> > Hi,

> > I have create a database (.DBC) and create a view of an free
> > table...until here no problem...but now I need to set an order in my
> > view in runtime...if my client click in a control that control change
> > the order of my view and so on with others controls.

> > example:

> > create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

> > ?yyy ->  here he give me an error telling me that yyy is not a
> > column!!!!

> > Can someone help me?

> You can create an index on the view, then set that as
> the order:

> SELECT myView
> INDEX ON someField TAG mySort
> SET ORDER TO mySort

> -- TRW
> _______________________________________
> My e-mail:  t r w 7

> _______________________________________



Mon, 05 Sep 2005 03:52:43 GMT  
 URGENT !!!! How I can set an order in SQL ...
Hi,

Here is the sample, how can it be done with view

close all
clear all
set safety off
create database dtest
create table test (f1 N(1,0),f2 C(1), f3 D)
insert into test values (2,"C",date()+1)
insert into test values (1,"B",date()+2)
insert into test values (3,"A",date()+1)
m.or="f1"
create sql view vtest as select * from test order by &?or
m.or="f2"
use vtest
browse
m.or="f1"
requery()
brow
m.or="f3,f2"
requery()
brow
use
delete view vtest
close database
delete database dtest
delete file test.dbf

but IMO indexes are simpler and preferable

Leonid


Quote:
> Hi,

> I have create a database (.DBC) and create a view of an free table...until
> here no problem...but now I need to set an order in my view in
runtime...if
> my client click in a control that control change the order of my view and
so
> on with others controls.

> example:

> create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

> ?yyy ->  here he give me an error telling me that yyy is not a column!!!!

> Can someone help me?

> TIA



Mon, 05 Sep 2005 17:53:51 GMT  
 URGENT !!!! How I can set an order in SQL ...
Hi, Aramini!
You wrote  on Wed, 19 Mar 2003 18:36:48 -0000:

 AP> I have create a database (.DBC) and create a view of an free
 AP> table...until here no problem...but now I need to set an order in my
 AP> view in runtime...if my client click in a control that control change
 AP> the order of my view and so on with others controls.

 AP> example:

 AP> create view xpto as select * from alibaba where xxx=?nxxx order by ?yyy

 AP> ?yyy ->  here he give me an error telling me that yyy is not a
 AP> column!!!!

create view xpto as select * from alibaba where xxx=?nxxx order by &?yyy

Note the macro symbol in front of your ?yyy. But there is really some things
you have to know about:
1) You can't open such a view in standard View designer - so you can only
manipulate it from code, or use some other 3rd-party utility.
2) You have to issue REQUERY() every time you want to switch the order (and
so you have to save all uncommited changes, as REQUERY needs "commited"
cursor)
3) You can't use such a technique with remote views, only local ones support
it.

BTW in your case it is little bit simplier to create temporery index, as
others mention. There is also some drawbacks:
1) You can't index (create index, not "use" it) view in 5-th buffering mode,
only row-buffered view (3-d buffering mode) can be indexed, so if you want
to use table buffering mode, you have to switch to row mode (and also commit
all the changes before this), then create index, and after that switch back
to table buffering mode.
2) There _may_be_ some problems with your temporary indices if you'll isuue
TABLEREVERT (), so you may need to recreate indices after TABLEREVERT()
also.

P.S. The similar approach can be used to have flexible WHERE condition:

create view xpto as select * from alibaba where &?lcWhere order by &?yyy

So you can dynamically create ALL the condition, not only SOME predefined
parameters. But the simiar limitations exist (however need in REQUERY is
really not a limitation in this case :))))

WBR, Igor



Mon, 05 Sep 2005 18:10:46 GMT  
 URGENT !!!! How I can set an order in SQL ...
Thanks to all!!


Quote:
> Hi, Aramini!
> You wrote  on Wed, 19 Mar 2003 18:36:48 -0000:

>  AP> I have create a database (.DBC) and create a view of an free
>  AP> table...until here no problem...but now I need to set an order in my
>  AP> view in runtime...if my client click in a control that control change
>  AP> the order of my view and so on with others controls.

>  AP> example:

>  AP> create view xpto as select * from alibaba where xxx=?nxxx order by
?yyy

>  AP> ?yyy ->  here he give me an error telling me that yyy is not a
>  AP> column!!!!

> create view xpto as select * from alibaba where xxx=?nxxx order by &?yyy

> Note the macro symbol in front of your ?yyy. But there is really some
things
> you have to know about:
> 1) You can't open such a view in standard View designer - so you can only
> manipulate it from code, or use some other 3rd-party utility.
> 2) You have to issue REQUERY() every time you want to switch the order
(and
> so you have to save all uncommited changes, as REQUERY needs "commited"
> cursor)
> 3) You can't use such a technique with remote views, only local ones
support
> it.

> BTW in your case it is little bit simplier to create temporery index, as
> others mention. There is also some drawbacks:
> 1) You can't index (create index, not "use" it) view in 5-th buffering
mode,
> only row-buffered view (3-d buffering mode) can be indexed, so if you want
> to use table buffering mode, you have to switch to row mode (and also
commit
> all the changes before this), then create index, and after that switch
back
> to table buffering mode.
> 2) There _may_be_ some problems with your temporary indices if you'll
isuue
> TABLEREVERT (), so you may need to recreate indices after TABLEREVERT()
> also.

> P.S. The similar approach can be used to have flexible WHERE condition:

> create view xpto as select * from alibaba where &?lcWhere order by &?yyy

> So you can dynamically create ALL the condition, not only SOME predefined
> parameters. But the simiar limitations exist (however need in REQUERY is
> really not a limitation in this case :))))

> WBR, Igor



Mon, 05 Sep 2005 18:53:49 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. set order results in no error and no order set

2. Index order not set error with SQL Server and Stored Procs

3. Setting order to child table in SET RELATION.

4. Problem filling listbox from table ordered on datetime field with set filter to >= datevalue

5. Set order timing issues

6. Order of execution in a form set

7. Order of fields in result set

8. set order to

9. set order to and browse for

10. Set Order ... Descending in vfp5 RunTime

11. table has no index order set?

12. set order in a grid

 

 
Powered by phpBB® Forum Software