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

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

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

   ► MB LobbyCorel Paradox / ObjectPAL Coding Board   Print This     

QBE & SQL Topic

Query by Example and Paradox SQL (A.K.A. Local SQL)
 Show:
New Activity New Threads Oldest Unanswered Most Active Most Read
  


25 Threads:

Add New ThreadRandom Thread

Thread 25 of 25
Thread Starter  

PS_3060
QBE answer table not populated with data
I have a query which I wish to populate a particular table (not the answer table). I have been unable to run this in objectpal. I think it has to do with the tilde variables but i'm not sure. following is my code. The variable are populated correctly so any advise is most appreciated. son = 22013, sol = 1  , qvar = Query Answer: :CaliachData:rptWorkorders.DB , WORKORDERS.db |WKOWONO | WKOPTNO |WKOBAL | WKOUSER | WKOUSE1 | WKOUSE2 | WKOUSER3 | WKOUSER4 |, , |CHECK | CHECK |CHECK | CHECK |CHECK ..~sol | CHECK | CHECK ..~son| CHECK | Endquery ExecuteQBE(qvar), tv.open(":CaliachData:rptWorkorders.DB") Thanks
17 days ago
6 replies. Last post:

Steven.G
and errorshow() doen't fire? I'd suggest string(sono) and string(solno), but errorshow() would fire if lack of that was the problem
14 days ago
Nothing New Since Your Last Visit
56
Hits

Thread 24 of 25
Thread Starter  

GaryG
Performance of SQL Passthru Query in a loop
{Too Long!}
37 days ago
1 Reply:

Steven.G
I don't think there's any way to make a significant change, other than "fastest machine you have".. you can do 1000 loops in a minute.. that seems quite reasonable
37 days ago
Nothing New Since Your Last Visit
28
Hits

Thread 23 of 25
Thread Starter  

PS_3060
Connecting to PostgresSQL server
Hi,  I have been dragged back from retirement to fix a small Paradox database we use for design. Previously I used the Paradox ODBC to connect to the DB and all was well. The DB is now a PostgreSQL databse. I have installed the ODBC connection and can connect useing Access.  PostgreSQL35W ODBC System DSN On the paradox form I have a button to connect to the DB useing the following Code dynalias["UserName"] = "aaaaa", dynalias["Password"] = "XXXXXX" if not db.open(":PostgreSQL35W:", dynalias) then   ;Which Is the ODBC System DSN, , msginfo("Caliach Database Not Open", "stop") else message.visible = true ;UPDATE NEW PARTS, message = ("UPDATING PARTS DATA") sqlvar = sql SELECT PTMPTNO, PTMP1CD, PTMSTUM, PTMSEQ,PTMSATX , PTMUSE1, PTMUSE2, FROM altro.PTMFILE altro_ptmfile, WHERE PTMSEQ > ~oldptmseq, endsql executeSql(db, sqlvar, ":caliachData:NewParts"), , endif How do I change this to connect to the Postsgres35 DYN?
5 months ago
1 Reply:

Steven.G
> ":PostgreSQL35W:" are you certain that the alias itself is correctly defined in the BDE and/or the app? > msginfo("Caliach Database Not Open", "stop") throw in an errorshow(), too.. might answer the question
5 months ago
Nothing New Since Your Last Visit
91
Hits

Thread 22 of 25
Thread Starter  

PS_2579

Hello Prestwood,

I

Hello Prestwood, I am once again asking for your support.  During the coronavirus lockdown I decided it was a good time to simplify my reports. One of the challenges I face is a set of 2d bar graphs I have created from a table.  Note my X axis data points will vary greatly: I believe I have run into a glitch I will not be able to fix.  The X axis 'ticks' keep counting but the data isnt showing up. If i play around resizing the graph in design, it may or may not fix it. I would like to sort this out so I do not have to manually involve myself further. I uploaded an image of my issue to imgur https://imgur.com/a/uWrA9rC  (imgur is a safe site, with many photos of cats) THANK YOU
10 months ago
2 replies. Last post:

Peter.P3
hahaha, well ill figure something out. Got plenty of time recently
10 months ago
Nothing New Since Your Last Visit
178
Hits

Thread 21 of 25
Thread Starter  

PS_2579
alpha to numeric
{Too Long!}
12 months ago
2 replies. Last post:

Peter.P3
Hi Steven,  Im sorry i didnt reply earlier, I didnt forget about you :) I finally got around to using this script in my report and so far it seems to be working.  Thanks a million! -Pete
11 months ago
Nothing New Since Your Last Visit
192
Hits

Thread 20 of 25
Thread Starter  

PS_291
.. used alone in QBE
{Too Long!}
13 months ago
4 replies. Last post:

John.O2
{Too Long!}
13 months ago
Nothing New Since Your Last Visit
153
Hits

Thread 19 of 25
Thread Starter  

GaryG
Use a variable in QBE as a resulting value
Is it possible to use a variable as a resulting value? Or is it only possible to use a variable as filter.  I would like the value in a variable to be put in the resulting column "DATETIMECHANGED". Thanks in advance, Gary var, todaysdatetime DateTime, custnbr String, qq Query, endVar custnbr = "1234", todaysdatetime = dateTime() qq = Query ANSWER: :PRIV:CHNGED.DB OPTIONS: NO AUXILIARY TABLES,Big Grin!ATA:Items.db | FILENO | CUSTNO |, | Check _join1 | ~custnbr | :DATA:PRICING.db | FILENO | QUANTITY | PRICE | DATECHANGED | REASON | INITIALS | PREVIOUSPRICE | PERCENTCHANGE | PRINTSELECT | LASTREFNO | SETUP |, | _join1 | Check | Check | Check calc ~todaysdatetime AS "DATETIMECHANGED" | Check | Check | Check | Check | Check | Check | Check |, EndQuery if NOT qq.executeQBE() then, errorShow(), else, endIf
17 months ago
4 replies. Last post:

Gary.G4
Thanks
17 months ago
Nothing New Since Your Last Visit
192
Hits

Thread 18 of 25
Thread Starter  

Shores
Strange errors with :priv: report master table
{Too Long!}
27 months ago
3 replies. Last post:

Steven.G
if the path name is too long, doesn't matter which way you shorten it.. the printer name, you are best to actually be a shared printer, and shorten the network name
27 months ago
Nothing New Since Your Last Visit
653
Hits

Thread 17 of 25
Thread Starter  

PS_383
pass parameter to paradox sql file
Is there a way to pass parameter to paradox sql file?  Something like:, , Update Table1 Set CurrentDate = ?, Update Table1 Set CurrentDate = :CurrentDate, Update Table1 Set CurrentDate = @CurrentDate, , All I found is this article:, https://stackoverflow.com/questions/12416503/delphi-query-parameter-usage-when-all-values-is-also-an-option
41 months ago
1 Reply:

Steven.G
sorry, haven't touched the sql stuff within paradox in a long time
41 months ago
Nothing New Since Your Last Visit
785
Hits

Thread 16 of 25
Thread Starter  

Jay.M
getAnswerFieldOrder
{Too Long!}
44 months ago
2 replies. Last post:

Jay.M
Thats what I was hoping for that maybe someone would be able to use what I found.. thats exactly why I left the post up.. THANKS!
44 months ago
Nothing New Since Your Last Visit
1076
Hits

Thread 15 of 25
Thread Starter  

csgPdx
creating flat file from a normalized table w/ QBE
Some years ago I saw a query of a single normaiized table that gave a flat file answer table. Presently, I have file that tracks payments and adjustments to outstanding receivables. A single claim may have many payments (either from insurance companies or patients) and/or adjustments (from contract write-offs to collection write-off). I am needing to show for each claim, in a single row, amounts for each type of payment and adjustments. Of course, I can produce this result in a series of queries. And fortunately, there is a relatively small number of payment and adjustment categories, so this is entirely doable. However, I am interested in the technique, as such, that used a "self-join." I've had no success in searching on the internet, in part, perhaps, because I am not even using the right terminology. Thanks, NK
6 years ago
Nothing New Since Your Last Visit
1981
Hits

Thread 14 of 25
Thread Starter  

Gary.A2
Help! New old user!
{Too Long!}
6 years ago
Nothing New Since Your Last Visit
1431
Hits

Thread 13 of 25
Thread Starter  

Coder_182
How to do a FULL JOIN in SQL Paradox Editor
When i use the keyword  FULL JOIN  I get an error message. 
7 years ago
10 replies. Last post:

Anibal.C
{Too Long!}
7 years ago
Nothing New Since Your Last Visit
5224
Hits

Thread 12 of 25
Thread Starter  

Gar.L
Running multiple SQL (DDL) scripts against Paradox
HI, Does anyone know of a (free) utility to run multiple SQL (DDL) statements/scripts against a Paradox database?  Ideally used for deployments (where you are updating a database with more than one SQL script) and you don't want to manually run one script after another. Thanks
9 years ago
Nothing New Since Your Last Visit
1751
Hits

Thread 11 of 25
Thread Starter  

John.C
Problem with Delete QBE Queries
We are currently experiencing a problem with 16bit Paradox 7.  When I run a delete query (QBE) the query completes and we would assume that all the data that we want to remove has been removed. However, when we check the table we ran this query on only some of the data has been removed.  So we run the query again..  When we check the table more of the data has been removed but some is still left. We have to repeat this multiple times before all the data is gone.  Each time we repeat it we don't modify the query - its the same one we ran 1st time around. What could be causing this problem and how can we solve it? I would appreciate all advice offered regarding this question. Best Wishes John
9 years ago
1 Reply:

AIBreveleri
Post the QBE.  Try to cut and paste it -- don't retype it.  Also, describe the structures of the tables referenced by the QBE.  Include validity constraints and secondary indices. -Al.
9 years ago
Nothing New Since Your Last Visit
2723
Hits

Thread 10 of 25
Thread Starter  

jlockley
"out of memory error when running query by form
Query by form script gives "out of memory " error at a set point . Large query. Have tried resetting shared memory but not memory location in BDE. Any ideas here what could be done? ,
10 years ago
Nothing New Since Your Last Visit
2189
Hits

Thread 9 of 25
Thread Starter  

Shores
Creating a new logical column with false values
Hi everybody! In the table resulting from a QBE I need to have a new column of the logical type containing all false values. I was used to type CALC 1 AS NEWFIELDNAME  to generate a new longint field named NEWFIELDNAME having the default value of 1. So, I tried doing CALC FALSE AS NEWFLAG, but that syntax produces a new STRING field containing the word "FALSE"... :) I then thought that CALC 1<>1 AS NEWFLAG would do the trick, but that simply isn't accepted by the QBE parser... Any ideas? :),
10 years ago
3 replies. Last post:

Dan
Yep, that will work.   However there is an outside chance that the values will match.  You will need to make sure the value in the helper table has a value that does not exist in the joined table.
10 years ago
Nothing New Since Your Last Visit
2983
Hits

Thread 8 of 25
Thread Starter  

John.G2
Paradox for Windows Desktop has stopped working
I recently bought a new computer  (Core i7, 3.33 GHz, Windows 7 64 bit) and then installed and upgrade to  WP office X15, including Paradox 11. I've been doing this kind of query of decades and had no trouble before., Now,  when I make a QBE I get the usual answer table, but when I go to Query, Properties, Structure and change the query structure order and then  click the Run the Query button the following message appears:  Paradox for Windows Desktop.  Paradox for Windows Desktop has stopped working.  It says it will try to find a solution but never does.  Paradox then closes down. Occasionally, it crashes as above when I only go to Query and then click on Properties., I did a reinstall of Paradox which did nothing and also a reinstall of WPO5.  Still have the problem.  Every other function of Paradox 11 seems to work fine., Any suggestions about how I can cure this problem?  Thanks, JG,
11 years ago
3 replies. Last post:

peterbell
{Too Long!}
10 years ago
Nothing New Since Your Last Visit
7044
Hits

Thread 7 of 25
Thread Starter  

jlockley
Logical field in query by form
{Too Long!}
11 years ago
1 Reply:

Jo_Lynne.L
Answer to self: 1) Declaring as string worked for a bit. Now not passing value to the query.  2) The negative value on the query form must be blank. If set to False, only those records with the value false in the logical field will be called up. The checked  value remains TRUE (Or yes, whatever is desired
11 years ago
Nothing New Since Your Last Visit
2639
Hits

Thread 6 of 25
Thread Starter  

brationer1
Networking Paradox 9
My company has intentions of running Paradox 9 on Server 2008.  The Operating System on most of our computers  is Windows 7 Pro.  Is this a good idea? Will we require an expensive  'top notch' network person to facilitate this.  Please advise me. Thank you very much.
11 years ago
1 Reply:

mprestwood
Sure, this will work well. Just stay away from Vista which it sounds like you are. If you need help, you can post here for free or you can open a support account: Support Account - http://www.prestwood.com/services/support/
11 years ago
Nothing New Since Your Last Visit
2477
Hits

Thread 5 of 25
Thread Starter  

riskmansea
Value to a query-push button on a form
Attempting to set up a form to query customer information by entering the account number. I copied the code from the guide but it fails. The debug highlights the word enter on the  sACCOUNT =  Enter ACCOUNT.value line. Appreciate any help. ;Button :: pushButton, method pushButton(var eventInfo Event),  var ,   sACCOUNT String,   qryCustomer  Query ,   tv TableView ,  endVar  sACCOUNT =  Enter ACCOUNT.value  qryCustomer = Query Answer: :WORK:ANSWER.DB CUSTOMER MASTER.DB|ACCOUNT|NAME|ADDRESS 1|ADDRESS 2|CITY|STATE|ZIP|PHONE|, |..~sACCOUNT|CHECK|CHECK|CHECK|CHECK|CHECK|CHECK|CHECK|CHECK|CHECK| EndQuery   qryCustomer.readFromFile("\\\\Sultanmain\\acctg\\PDOX\\CUSTOMER MASTER") ,   qryCustomer.executeQBE(":WORK:answer.db") ;// run the query   ,   tv.open(":WORK:answer.db")      ;// display the results ,  , endMethod
11 years ago
1 Reply:

Dan
Try this if Account is a field on the form then this should work sACCOUNT =  ACCOUNT.value if Enter_Account is the field name then sACCOUNT =  Enter_ACCOUNT.value otherwise you could use sACCOUNT.view(" Enter the ACCOUNT value")
11 years ago
Nothing New Since Your Last Visit
2199
Hits

Thread 4 of 25
Thread Starter  

brationer1
Using the function
Can "Today()" be used in a QBE or a Report? I am trying to use it in order to find the number of years an employee or a group of employees worked. Something like: "(today() - Employee's Employment date)/ 365.25" I can copy the data to a spreadsheet and get a result, but this not what I need. 
11 years ago
3 replies. Last post:

mprestwood
You can base a report off of a QBE file. When the report opens the report, it first runs the QBE then uses the answer table. So, the first step is to create a QBE that generates an answer table with the correct data. Have you gotten that far? Do you have a QBE that shows the correct data? After that, you can use ObjectPAL to pass in values to the QBE to tailor each run of the report.
11 years ago
Nothing New Since Your Last Visit
3506
Hits

Thread 3 of 25
Thread Starter  

simd01
Using MS SQL as back end
I need to set MS SQL up as the back end to a large multi-user paradox 10 app and am having trouble finding discussions of the issues involved. Can someone either point me to the relevant documents or briefly describe the various problems one is going to confront in achieving this? Many thanks.
11 years ago
2 replies. Last post:

simd01
Robert, many thanks for that. I had been using the SQL link but that was the problem. Once I set up the ODBC DSN and then an alias as you suggested I was up and running. Thanks again - you've made my day. Simon
11 years ago
Nothing New Since Your Last Visit
3626
Hits

Thread 2 of 25
Thread Starter  

hedrickr
Paradox 11 Help Not Working
{Too Long!}
12 years ago
1 Reply:

mprestwood
{Too Long!}
11 years ago
Nothing New Since Your Last Visit
2769
Hits

Thread 1 of 25
Thread Starter  

Cfolkes
Find duplicate data in a column using query
I have a multiple column table which has a column "A" I want to query "A" and show an answer of all duplicates found.
12 years ago
Nothing New Since Your Last Visit
3239
Hits

New Thread

Follow PrestwoodBoards on: 


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