>I am trying to add more flexible sort ordering to my SQL Views (local and Remote). The following syntax seems to work:
>
>CREATE SQL VIEW v_TestOrder ;
> AS ;
> SELECT *;
> FROM Table1;
> WHERE Field1 LIKE ?pcField1;
> ORDER BY ?pnOrder
>
>If I use v_testOrder, I get the standard vfp dialog box asking for pcField1 and then pnOrder and the data is then ordered in the view by pnOrder.
>
>I have two problems:
>
>1.) If I pre-define pnOrder to be 1 and then open or requery() v_testOrder, I will get an error message: "SQL: Column PNORDER is not found" --> the ?pnOrder only seems to work via VFP dialog input.
>
>2.) If I can ever solve 1.), I would like to use a field name (character) for ?pnOrder (ok - ?pcOrder) instead of using a number. Using a field name -- Field1 or "Field1" causes SQL: Column '' Not Found error.
>
>Thanks for any help or alternatives to ordering views.
>
>Kevin

I have never done this with the ORDER BY, I have always had to use macro substitution. For parameterized views, all I do when I issue a requery, is define the parameter first, then issue the requery. Then I never get an error. If you put any of these views in a form DE, you need to set the NoDataOnLoad property to .T. Otherwise, you will be prompted for any parameters. The alternative is to define the parameters then open the views yourself.

CREATE SQL VIEW v_TestOrder AS ;
   SELECT * FROM Table1 WHERE Field1 LIKE ?pcField1 ;
   ORDER BY &pnOrder