I.T. Discussion Community!
-Collapse +Expand
PrestwoodBoards
Search Group:

Advanced
-Collapse +Expand PrestwoodBoards Store

Prestwood eMagazine

November Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyPrestwoodBoards BoardMember Introductions Topic   Print This     

programming with QBE results

programming with QBE results in Member Introductions topic (part of our PrestwoodBoards group).

Quick Search: programming   results   programming QBE   programming QBE results  
jontower
London, -- UK

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

 Posted 11 years ago (Thread Starter)
Comment Quote
About jontower -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12489, 7 replies
Thread Started 2/22/2008 11:59:32 AM
View Counter=6593
Last Reply Posted 2/25/2008 4:50:12 PM)
Location=London, -- UK 
Joined=11 years ago   MB Posts=5  
rt
Castel, -- UK

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 Wink

Thats enough of that. Ed.

 Posted 11 years ago
Comment Quote
About rt -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About rt

I do like my jazz and blues!


Post ID #12498 (Level 1.1)  Reply to 12489
Thread Started 2/24/2008 2:22:11 PM
Location=Castel, -- UK 
Joined=19 years ago   MB Posts=687   KB Comments=1  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

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

 Posted 11 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #12500 (Level 1.2)  Reply to 12489
Reply Posted 2/24/2008 5:09:16 PM
Location=Home office in Fresno, CA. 
Joined=17 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

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

 Posted 11 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #12506 (Level 1.3)  Reply to 12489
Reply Posted 2/25/2008 11:55:37 AM
Location=Home office in Fresno, CA. 
Joined=17 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Most Recent Post
Steve Caple
Steve Caple Company
Sacramento, CA

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

 Posted 11 years ago
Comment Quote
About Steve Caple -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.
About Steve Caple

Steve Caple I first saw Paradox in 1985 when a guy named Rick Albers came to demo Ansa's new 1.1 release to the Sacramento PC Users Group. The feeling was like going from WordStar to WordPerfect 3.0, a sense of freedom and power.

A long time dBase user had an evaluation copy he let me use for a few weeks.  Going from dBase to Paradox was like finding yourself walking WITHOUT your shoe laces tied together.  I was hooked; within a week I wrote my first useful Paradox DOS program.  It took a few evenings to put together a script to  generate a Basic Bulk Mail sort,  using a series of Paradox queries against a bicycle club Century ride mailing list.  It then used PAL to send text to the printer, printing it out in 5 digit Zip, three digit Zip, and miscellaneous by state groups, including group header labels and spacers on one-across stock  -  just tear it apart at the headers and give it to the mailing volunteers, and a job that used to take 20 people 4 or 5 hours could be done by 5 or 6 in a single hour.  

Later, Paradox for Windows version 5 added many needed structural and indexing and linking features, and as hardware caught up with the needs of a GUI environment it became a powerful database for small companies and divisions or branches within large corporations.  Despite some years of no real changes it remains one of the best products for that purpose.


Post ID #12513 (Level 1.4)  Reply to 12489
Reply Posted 2/25/2008 4:50:12 PM
Location=Sacramento, CA  
Joined=15 years ago   MB Posts=71   KB Posts=3  
jontower
London, -- UK
HI - thanks
 Posted 11 years ago (Thread Starter)
Comment Quote
About jontower -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12502 (Level 1.5)  Reply to 12489
Reply Posted 2/25/2008 8:51:26 AM
Location=London, -- UK 
Joined=11 years ago   MB Posts=5  
jontower
London, -- UK
Hi - thanks rt and Daniel for your prompt and helpful replies - I think I'll switch further technical discussion to my other thread: programming with QBE results.
 Posted 11 years ago (Thread Starter)
Comment Quote
About jontower -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12503 (Level 1.6)  Reply to 12489
Reply Posted 2/25/2008 8:51:26 AM
Location=London, -- UK 
Joined=11 years ago   MB Posts=5  
jontower
London, -- UK

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!

 Posted 11 years ago (Thread Starter)
Comment Quote
About jontower -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12504 (Level 1.7)  Reply to 12489
Reply Posted 2/25/2008 8:59:32 AM
Location=London, -- UK 
Joined=11 years ago   MB Posts=5  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1123A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #12489 Counter
6593
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech