×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

SQL SELECT brain dead

SQL SELECT brain dead

SQL SELECT brain dead

(OP)
Been away fro VFP for years and of course now I cannot figure out a code segment for a very simple query.

SELECT pilot, flt_time from flights ORDER BY launch TO FILE temp
OK fine, creates a TXT file as wanted.

Want to add:
SELECT pilot, flt_time+" minutes" from flights ORDER BY launch TO FILE temp

Found some samples where this should work.... but it has a syntax error

Another "want" is for the flt_time column to have a fixed position
Running the simple first example there are maybe 40 spaces between columns

The purpose of this venture is to make a two column page that can be inserted into the Body of a email.

RE: SQL SELECT brain dead

Hi colfrank,
you can't concatenate numeric data with a string that way.
You have to make 'minutes' a regular column or transform flt_time into text:


SELECT pilot, flt_time, "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp 


VFP will use the fields length definitions for column width in your target file. So if pilot is defined as C(50) your file will look like this:

Pilotname                                         1234 minutes
another pilot                                     750 minutes
a.s.o. 

In case it isn't a problem to cut off some characters from the pilot column, you could reduce the width like this:

SELECT CAST(pilot as C(20)) as pilot, flt_time, "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp 

that way your list will look like this:

Pilotname           1234 minutes
another pilot       750 minutes
a.s.o. 

Last but not the least, you column flt_time might need some correction too, as time usually is saved as an integer and ints don't display zeros in front. So, in case you want to show zeros to display the time as a 4 digit value, you could do that like this:

SELECT CAST(pilot as C(20)) as pilot, PADL(flt_time,4,[0]), "minutes" as fixtxt
FROM flights
ORDER BY launch
TO FILE temp 

which will result into this:

Pilotname           1234 minutes
another pilot       0750 minutes
a.s.o. 


HTH

-Tom
https://www.blogger.com/profile/089031659767875220...

RE: SQL SELECT brain dead

I've made a small test.
You can see that for 1 to 5 characters, VFP force the length to 21 characters
Then for each additional characters, the length is increased sometimes with 4, other times with 5 characters.
Don't know how to reduce the size, nor to control it.
I tried several commends, like SET MEMOWITH, but none helped.

CODE --> Foxpro

CLEAR
SET SAFETY OFF
FOR lni = 1 TO 20
	CLOSE DATABASES all

	SET MEMOWIDTH TO 10
	CREATE CURSOR flights  (pilot C(100), flt_time I, launch T)
	INSERT INTO flights VALUES ('gelu manelu', 10, DATETIME()-100)
	INSERT INTO flights VALUES ('onu', 20, DATETIME()-300)

	SELECT CAST(pilot as c(m.lni)) as pilot FROM flights TO FILE temp
	cAll = FILETOSTR("temp.txt")
	ALINES(aAll,m.cAll,4)

	?m.lni, LEN(m.aAll[2])
NEXT 

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

RE: SQL SELECT brain dead

I would rather right align the number of minutes than pad with leading zeroes, but let me also add that it's not the job of SQL to format text output.

Eevn though it's more code, I'd
1. Query data
2. TextMerge it into something you use in emails

This could also go into an HTML table for more modern mail look and in general is easier to maintain and extend.

CODE

*Create Cursor flights (pilot Char(30), flt_time integer, launch DateTime)
*Insert Into flights Values ("Col Frank",60, DateTime()-3600)
*Insert Into flights Values ("Tom Borgman",90, DateTime()-1800)
*Insert Into flights Values ("Olaf Doschke",120, DateTime()-900)

Select pilot, flt_time From flights Order By launch Into Cursor crsFlightList
Local lcBody, lcHTML, lcFilename
lcBody = '<h1>Flights Table</h1><p><table class="tof"><re><th>Pilot</th><th>Duration</th></tr>'
* (substr(ttoc(dtot(date())+crsGrid.nSeconds,2),4,2))
Scan
Text To lcBody Additive TextMerge Noshow
<tr><td><<Alltrim(pilot)>></td><td><<Substr(Ttoc(Dtot(Date())+flt_time,2),4)>></td></tr>
EndText
EndScan
lcBody=lcBody+'</table>'

Text To lcHTML TextMerge Noshow
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
   border: 1px solid black;
}
th, td {
   padding: 4px;
}
.tof {border-collapse: collapse;
}
.tof tr td:nth-child(2) { text-align:right; 
}
</style>
</head>
<body>
<<lcBody>>
</body>
</html>
ENDTEXT

lcFilename=Addbs(GetEnv('TEMP'))+'flights.html'
StrTofile(lcHTML,lcFilename)
Run "&lcFilename" 

You can of course store the final HTML where you want and don't need to "Run" it. If you don't know how to create HTML mail, please ask - start a new question for that, as it's related, but a topic of its own.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL SELECT brain dead

Quote:

I tried several commends, like SET MEMOWITH, but none helped.

SET MEMOWIDTH will have no effect here. It only affects the output displayed on the screen (or to a printer) by commands such as ? and LIST. And it only afects fields wider than 254 characters.

Keep in mind too that when you send the output of SELECT to a file, you get two extra spaces inserted at the start of each line. A more accurate way of testing this code would be to send the output to a cursor.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: SQL SELECT brain dead

As I see the discussion about extra spaces in text output of queries, even in case you don't want to create HTML I'd look into EXPORT or COPY TO with TYPE SDF

CODE

Create Cursor flights (pilot Char(30), flt_time integer, launch DateTime)
Insert Into flights Values ("Col Frank",60, DateTime()-3600)
Insert Into flights Values ("Tom Borgman",90, DateTime()-1800)
Insert Into flights Values ("Olaf Doschke",120, DateTime()-900)

Select pilot, flt_time From flights Order By launch Into Cursor crsFlightList

Local lcFilename

lcFilename=Addbs(GetEnv('TEMP'))+'flights.txt'
Copy To (lcFilename) Type SDF
Modify File (lcFilename) 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL SELECT brain dead

SDF outputs integers right aligned into 11 char width, as it theoretically can get to the minus billion range. But you can cast to any width char and get that out, no comma, no tabs. Also no header captions, but that's easy to integrate into the fixed text of your mail body.

Bye, Olaf.


Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL SELECT brain dead

Obviously, the best advise is to output the result otherwise, e.g. using Copy To, but I am intrigued by the behavior of SELECT ... TO

I noticed that SET HEADINGS have a significant impact on the output.\
When SET HEADINGS is OFF, the width starts with 4, and increase with 4 or 5 / character

CODE --> Foxpro

CLEAR
SET SAFETY OFF
FOR lni = 1 TO 70
	CLOSE DATABASES all
	CLEAR TYPEAHEAD 

	SET HEADINGS OFF
	CREATE CURSOR flights  (pilot C(100), flt_time I, launch T)
	INSERT INTO flights VALUES ('gelu manelu', 10, DATETIME()-100)
	INSERT INTO flights VALUES ('onu', 20, DATETIME()-300)

	SELECT CAST(pilot as c(m.lni)) as pilot FROM flights TO FILE temp
	cAll = FILETOSTR("temp.txt")
	ALINES(aAll,m.cAll,4)

	??m.lni, LEN(LTRIM(m.aAll[1]))
NEXT 

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

RE: SQL SELECT brain dead

Vilhelm-Ion, I've been running your code. I'm not seeing any difference with SET HEADINGS ON | OFF. As far as I know, that setting only affects the output of the TYPE and DIPLAY commands. Similarly, I don't see the need for CLOSE DATABASE or CLEAR TYPEAHEAD.

But, regardless of that, even if you get the number of spaces right, once you have inserted the output into an email body, the columns will look ragged (unless you use a fixed-pitch font, which is ugly). You might be able to avoid the problem by adding a tab character (CHR(9)) to the pilot's name (after trimming). But in my tests, I find that doesn't avoid a slightly ragged look (that might be an issue with my email client).

Perhaps the best option is to create an HTML table, as per Olaf's post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: SQL SELECT brain dead

(OP)
thanks for all the help..I now have a properly formatted SDF file (final.txt)

Next obstacle, using CDO2000 as my email package want to display the contents of final.txt in the body of the message.

.cTextBody = ??

I know that I could always just attach final.txt, but to have it part of the body is much more attractive.

RE: SQL SELECT brain dead

Remember one of the golden rules at technical forums: New question = new thread!

RE: SQL SELECT brain dead

Tore is right, but the short answer is FILETOSTR(lcFilename)

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: SQL SELECT brain dead

Mike Lewis, I will try the same prg on another PC.
I noticed some strange behavior.
Version of VFP is 9.0.7423
The two images:

and

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

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!

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