SQL Order By

[white605]

Working VFP 9 I have the followind code which gets the desired result

mdate=CTOD("04/01/07")
medate=CTOD("04/30/07")

SELECT * ;
    FROM sch;
    WHERE (BETWEEN(S_DATE,mdate,medate)) AND type=[G] ;
    ORDER BY SCH.S_date ;
into table current.dbf

SELECT * ;
    FROM sch;
    WHERE (BETWEEN(S_DATE,mdate,medate)) AND type=[W] ;
    ORDER BY SCH.S_date ;
into table temp.dbf

IF USED("current")
SELECT current
ELSE
USE current
endif
APPEND FROM temp
brow

Can someone point me to the right direction to combine the select statments and get rid of the append from?
thanks
wjwjr

 

[Olaf Doschke]

SELECT * ;
    FROM sch;
    WHERE (BETWEEN(S_DATE,mdate,medate)) AND (type=[G] OR type=[W]);
    ORDER BY sch.type,SCH.S_date

Bye, Olaf.

 

[ilyad]

SELECT * ;
FROM sch;
WHERE (BETWEEN(S_DATE,mdate,medate)) ;
AND type IN ("G","W") ;
ORDER BY SCH.Type, SCH.S_date ;
into table current.dbf

Since it looks to me you want first all G records sorted by date and then all W records sorted by date.

 

[falconsight]

Just my two cents...
If you're working with temporal data (as I assume from the first code) use the code sent by ilyad, but change the last line like this

SELECT * ;
    FROM sch;
    WHERE (BETWEEN(S_DATE,mdate,medate)) ;
    AND type IN ("G","W") ;
    ORDER BY SCH.Type, SCH.S_date ;
into CURSOR CURRENT readwrite

that way you don't write to disk until you are ready to do so

 

[ilyad]

Very good point! No need to create temp tables if they are not needed.

 

[Mike Lewis]

Mike,

I often suggest people avoid mixing up SQL with xbase syntax.

May I ask why you do that?

I'm not disagreeing with you. I'm just curious to know the reason.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

Seems like nobody addressed:

...get rid of the append from.

Insert into current ;
Select.......
..........

Do NOT include the "Into" Statement.

 

[white605]

I often suggest people avoid mixing up SQL with xbase syntax."

I think thats a good idea! I hope its as enjoyable to watch people (like myself) learn as it to learn from others here. There are many things I would not have been able to do without this and other forums like this. Thanks to all!
wjwjr

 

[markros]

May I ask why you do that?

I'm not disagreeing with you. I'm just curious to know the reason.

I would guess to easily port into other back-end such as SQL Server.

 

[Imaginecorp]

If I remember correctly the following will not work in earlier versions of VFP. I think, and I am guessing, this compatablity was introduced in VFP8.

SELECT * ;
FROM sch ;
where s_date between m.mdate and m.medate ;
and type IN ("G","W")

For readability, though you can, I would not name a field "Type", as its a function name. Tip: any word that changes color in the editor, should not be used as a name for fields or variables.

 

[Olaf Doschke]

Imaginecorp,

the OP said, he's using VFP9. I adressed the "get rid of part", because my select (and selects of others too) would generate the current.dbf in a single select, no append needed.

Bye, Olaf.

 

[Stella740pl]

Imaginecorp,

I think, and I am guessing, this compatablity was introduced in VFP8.

I don't remeber when this was introduced, but I am comfortably using it in VFP6.

I think, it was there a couple of versions before, too (like VFP3, or maybe VFP5) - but wouldn't bet on it.

 

[Imaginecorp]

Stella740pl:
I stand corrected...Thanks
...........................
Olaf:
I must have missed the "Insert into"... or "Into Table Current" unless it was assumed. Your post, by itself, would create a cursor called "Query". {s}

 

[Olaf Doschke]

Imaginecorp,

true, I left out the obvious INTO part. But it would create the needed data in the needed order.

Bye, Olaf.

 

[TamarGranor]

FWIW, I'm pretty sure both the BETWEEN and IN keywords have been in FoxPro's SQL since it was introduced in FP2.0.

Tamar

 

[Stella740pl]

Tamar, you are right. Yes, they were there in FoxPro 2.0 back in 1991.

(This morning, I didn't have time to walk a few steps from my desk to the cabinet with the FoxPro 2.0 paper manual to check, but a few minutes ago I did.)

 

[Mike Lewis]

Going back to this point about not mixing SQL and XBase syntax .....

Does that mean you would not use things like LEFT() or UPPER() in a SELECT statement? And, if so, what would you do instead?

I know that some implementations of SQL have equivalents for some VFP functions, but, as far as I know, they are not part of standard SQL.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

As stated earlier: I stand corrected, looking at the Reference books from FoxPro for DOS & Windows:

Between and In, in a SQL-Select are supported.