ORDER BY and SQL

[crossface]

I am using VFP 7.0

Here is my issue

I have a field which is character named TransDate

In an SQL statement I would like to use an ORDER BY on this field

Because the field is character 01/05/2004 comes after 10/15/2005

I thought I could use the following

ORDER BY TransAct.Pcard, right(transdate,4), transdate

I get an error indicating that the column Pcard can not be found

The following code works

ORDER BY TransAct.Pcard, transdate

Any thoughts

Kevin Cotter
Byron Schools

 

[Mike Gagnon]

I would suggest you create a field that it would contain the value you want to order it by.

 Select TransAct.Pcard, right(transdate,4) as chopped, transdate order by ORDER BY TransAct.Pcard,chopped ,transdate into cursor ...

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[TamarGranor]

Assuming you can't do the best thing, which is to change the field to Date, the simplest solution is probably to add an extra field in the query:

SELECT <all your other fields>, CTOD(transdate) AS dDate ;
 ... ;
 ORDER BY TransAct,Pcard, dDate

Tamar

 

[alvechurchdata]

CTOD won't work with STRICTDATE=2. You can use DATE(yy,mm,dd) as an alternative but personally I'd just SET STRICTDATE TO 0

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[TamarGranor]

Good point about StrictDate. I was trying to avoid having to know too much about the character field and figured CTOD() was the best bet. I agree that I'd set StrictDate to 0 for this query.

Tamar

 

[crossface]

Thanks for all the help

Worked great!

Kevin Cotter
Byron Schools