programming with QBE results

Hi - My wife and I have taken over Administration of a large Amateur Theatre company in London - the only salaried position (as a jobshare).  We have inherited "interesting" old procedures and equipment, including a PC running Windows 98 and a Paradox-based Box Office system called Databox.  It is my wish to develop better-looking sheets of daily bookings for the Box Office volunteer staff to use.  I have managed so far to create QBEs that select the desired data, but seat numbers have been defined as alphanumeric (by the Databox people, so I can't change the definition), with the result that they are sorted thus:

26, 27, 3, 33, 34, 4, instead of 3, 4, 26, 27, 33, 34.  I have 25 years' experience of programming, so I can cope with adding zeroes on the left and taking a three-character right substring, but the help in Paradox 7 doesn't give me enough idea as to how/where to insert this code so as to produce the required query results.

I should be grateful for specific help on this and general advice on procuring tutorials (preferably free!) to ease myself into programming.  We have no manuals.

 Thanks in advance!

Jonathan

Which version of Paradox, I may have some old manulas... no promises and I'm in London in a couple of weeks.............

 Meantine can I suggest you buy Mike's Paradox book and I'm not on commissssson

Thats enough of that. Ed.

I hoe that this gets posted.  Any way the technique to use is to switch from the paradox qbe to SQL and use the Cast function from SQL.  The format is: Cast(<field name here> as integer).

Example:  Select distinct Cast(CustomerID as integer), customerName from Customers

The result will be an integer and should sort just fine.  You might run into a issue with field names.  Paradox QBE lets you get away with many things that SQL will not allow.  If this happens you will need to fully qualify the field name.  "<your table name>"."<your field name>"

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Sorry, I missed the part about Paradox 7.  You did great reading between the lines.  I just ran this on a Paradox 7 installation I have on an older PC.  Any way the only difficulty I ran into was that the order by clause did not recognize the field included in the Cast function.  I suspect that the version of the BDE included with Paradox 7 did not support this functionality.  You should probably download the most recient version of the BDE. http://info.borland.com/devsupport/bde/bdeupdate.html. 

I assume that you want to sort the results to display all the seats for a given performance together.  You should be able to achieve this by including the datetime field from the events table and then making that the first field in the orderby clause.   Leave all the other fields out of the orderby.  Make sure the Case(seat as integer) is the first field in the select.  Your SQL might look something like:

SELECT DISTINCT Cast(D2.Seat as integer), D.Code, D3.Surname, D3.Othernames, D3.Title, D3.MainPhone, D.DateTime
FROM ":DATABOX:EVENTS.DB" D, ":DATABOX:BOOKINGS.DB" D1, ":DATABOX:SALES.DB" D2, ":DATABOX:CUSTOMER.DB" D3
WHERE
(D.DateTime > '02/13/2008')
 AND (D.DateTime < '02/14/2008')
 AND (D1.Code = D.Code)
 AND (D2.TxRef = D1.TxRef)
 AND (D3.RefNo = D1.RefNo)
ORDER BY D.DateTime

Let's see where this gets you.

As far as the CD goes.  I dont know about that, I never had it.  Bought my copy on the cheep also.  There are not many copies of Mikes book floating around.  You might try the Prestwood store or even Ebay. 

While you are looking at books a good reference on SQL might be in order.  There are some things that are possible in SQL that just are not available in the QBE.  However that is another thread

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Jonathan,

I'm not sure how the SQL will turn out for you with the new BDE, so as an alternative,  do you have access to permanently change the seat numbers in the Databox tables?  That is, run a tCursor scan loop using the format() and string() function to change "3" to "003", etc.?  That would allow you to use the QBE queries you've already developed.  Show me the table structure and a sample of the data and I'd be happy to write the code for you.

Steve Caple
Programmer Analyst
Prestwood Software
http://www.prestwood.com

Hi Daniel,

It's possible you don't realise how much of a newbie I am!  We have "Paradox 7 for Windows 3.1 & Windows for Workgroups", but the PC is running Windows 98.  I've tried to take your advice about CAST.  I suppose when you say "switch from the paradox qbe to SQL" you mean go to the Query menu and choose 'Show SQL', then edit and run the SQL from there - that's what I've tried.  My resultant code looks like this:

SELECT DISTINCT D.Code, Cast(D2.Seat as integer), D3.Surname, D3.Othernames, D3.Title, D3.MainPhone
FROM ":DATABOX:EVENTS.DB" D, ":DATABOX:BOOKINGS.DB" D1, ":DATABOX:SALES.DB" D2, ":DATABOX:CUSTOMER.DB" D3
WHERE
(D.DateTime > '02/13/2008')
 AND (D.DateTime < '02/14/2008')
 AND (D1.Code = D.Code)
 AND (D2.TxRef = D1.TxRef)
 AND (D3.RefNo = D1.RefNo)
ORDER BY D2.Seat, D.Code, D3.Surname, D3.Othernames, D3.Title, D3.MainPhone

When I run this, I get "Capability not supported.  Token: CAST", and the Help button tells me that there is no additional information...

While I'm on, I have a couple more questions:  How can I extract the date and/or the time from a date/time field (we sometimes have two performances on one day); and if I can find someone to sell me Mike Prestwood's book but they don't include the CD, how much of a disadvantage would that be?

Looking forward to hearing from you again!