QBE: combining a numeric field to an alpha field?

#1

Greetings -

At long last have moved over to (or "up to"??) pdox 11 (in winXP) from
a long-established DOS-based pdox DB application and so must rewrite a
good number of PAL scripts into OPAL-based forms and/or stand-alone
scripts; or perhaps, hopefully, use some of the expanded features of
pdox.

Would like to ask this as my starting point; the CALC QBE feature
doesn't appear to have any way to combine a numeric field (in this
case, Short) with an Alpha-defined field to create a new third field;
is there a method outside of writing some OPAL code using STRVAL to
combine the two aforementioned types? In PAL that's the approach I had
to use.

If there isn't some neat undocumented trick, I'll follow up w/ my
specific example, as it involves doing QBEs on two different (but
closely-related in structure) tables and combining the selected records
into a third table in order to write a number of reports. The 3rd
table structure is based on the table which doesn't require the
combining of the previously mentioned fields.

Thanks for any feedback, general or otherwise, on this.

Mike

#2

Quote:

Would like to ask this as my starting point; the CALC QBE feature
doesn't appear to have any way to combine a numeric field (in this
case, Short) with an Alpha-defined field to create a new third field;
is there a method outside of writing some OPAL code using STRVAL to
combine the two aforementioned types? In PAL that's the approach I had
to use.

I think you might be able to do this with local SQL and the CAST function,
but I haven't tried it myself. See the helpfiles for local SQL.

Anders

#3

Mike - I just did something similar to this to update a db that someone else
created. Code is posted below. Where I have the "2911", you can just
change that to the field reference.

Hope this helps...

method run(var eventInfo Event)
var
tcProvInfo tCursor
stAccu1 String
f form
st string
endvar

st="Please wait while the New Field Number script is processing.

This may take several minutes, depending upon
the size of the database.

Please be patient."

f.open(":WORK:waitmssg.fsl")
f.waitmessage.value=st
setMouseShape(MouseWait, True)

tcProvInfo.open(":WORK:Lithics.db")
tcProvInfo.edit()
scan tcProvInfo :
stAccu1 = "" ;initialise it
if tcProvInfo."SiteNum" = "13WD130" then
stAccu1 = "2911" + tcProvInfo."FldNum"
endif

tcProvInfo."NewFldNum" = stAccu1
tcProvInfo.postRecord()
endScan
tcProvInfo.endEdit()
tcProvInfo.close()

st="The script has completed processing.

Thank you for waiting."
f.waitmessage.value=st
sleep(2000)

f.close()
setMouseShape(MouseArrow, False)

endMethod

"Mike" <micoma (AT) aol (DOT) com> wrote

Quote:

Greetings -

At long last have moved over to (or "up to"??) pdox 11 (in winXP) from
a long-established DOS-based pdox DB application and so must rewrite a
good number of PAL scripts into OPAL-based forms and/or stand-alone
scripts; or perhaps, hopefully, use some of the expanded features of
pdox.

Would like to ask this as my starting point; the CALC QBE feature
doesn't appear to have any way to combine a numeric field (in this
case, Short) with an Alpha-defined field to create a new third field;
is there a method outside of writing some OPAL code using STRVAL to
combine the two aforementioned types? In PAL that's the approach I had
to use.

If there isn't some neat undocumented trick, I'll follow up w/ my
specific example, as it involves doing QBEs on two different (but
closely-related in structure) tables and combining the selected records
into a third table in order to write a number of reports. The 3rd
table structure is based on the table which doesn't require the
combining of the previously mentioned fields.

Thanks for any feedback, general or otherwise, on this.

Mike

#4

Mike wrote:

Quote:

Would like to ask this as my starting point; the CALC QBE feature
doesn't appear to have any way to combine a numeric field (in
this case, Short) with an Alpha-defined field to create a new
third field;

Not in QBE, but in Local SQL:

SELECT YourShort, YourAlpha, CAST(YourShort AS CHAR(5)) || YourAlpha
FROM MikesTable

Works with any short because the final length of any short is 5 or less (<
32768)

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

#5

Larry DiGiovanni wrote:

Quote:

Mike wrote:

Would like to ask this as my starting point; the CALC QBE feature
doesn't appear to have any way to combine a numeric field (in
this case, Short) with an Alpha-defined field to create a new
third field;

Not in QBE, but in Local SQL:

SELECT YourShort, YourAlpha, CAST(YourShort AS CHAR(5)) || YourAlpha
FROM MikesTable

Works with any short because the final length of any short is 5 or less (
32768)

Thanks Larry; this approach would certainly be a "move up" as it were
from rewriting a script, but when I start looking over what's available
via pdox help I notice the words "the SQL driver" being installed. Is
that required in order to do local SQL commands?

BTW, my use is stricly standalone, on one PC.

As a followup can you briefly explain or point to a source, on how one,
after creating via the SQL editor the code as mentioned, "call" or
execute that piece of SQL logic?

Thanks again for the input.

Mike

#6

Thanks Susan. What you had to do between the scan/endscan statements
mirrors closely how my PAL script was written as I moved the various
fields from my answer table to the new, 3rd table. My main challenge
now is to understand the difference between how PAL interacted w/ the
data and how OPAL does -- especially the logic once a table has been
opened and in edit mode.

Mike

#7

Mike,

You can save SQL queries just like you can save QBE queries (as a
file) and then just run the file manually. If you wanted to put the
query in code, then it's rather like you do with QBE queries - check
the ObjectPAL help for SQL, especially executeSQL().

To run SQL against Paradox tables, you do not need anything
special/extra installed. I'm sure what you were reading is in
relation to non-Paradox tables accessed via ODBC, etc.

Liz

Mike wrote:

Quote:

SELECT YourShort, YourAlpha, CAST(YourShort AS CHAR(5)) || YourAlpha
FROM MikesTable

Thanks Larry; this approach would certainly be a "move up" as it were
from rewriting a script, but when I start looking over what's available
via pdox help I notice the words "the SQL driver" being installed. Is
that required in order to do local SQL commands?

BTW, my use is stricly standalone, on one PC.

As a followup can you briefly explain or point to a source, on how one,
after creating via the SQL editor the code as mentioned, "call" or
execute that piece of SQL logic?

Thanks again for the input.

Mike

#8

Liz -

Ah, the light starts to get brighter; thanks.

One of my drawbacks is a lack of "current" pdox OPAL book documentation
to read, so I'm using more of the pdox built-in Help for these "later"
features which are mentioned.

Found the SQL query "expert", so now have a better understanding of
what Larry meant in his code example.

I do hope to make the old PAL procedures "hands-off" via OPAL, so will
followup on the many suggestions that have been put forth. You know, a
few using method "a", a few using "b", etc.etc

Mike

Liz McGuire wrote:

Quote:

Mike,

You can save SQL queries just like you can save QBE queries (as a
file) and then just run the file manually. If you wanted to put the
query in code, then it's rather like you do with QBE queries - check
the ObjectPAL help for SQL, especially executeSQL().

To run SQL against Paradox tables, you do not need anything
special/extra installed. I'm sure what you were reading is in
relation to non-Paradox tables accessed via ODBC, etc.

Liz

Mike wrote:

SELECT YourShort, YourAlpha, CAST(YourShort AS CHAR(5)) || YourAlpha
FROM MikesTable

Thanks Larry; this approach would certainly be a "move up" as it were
from rewriting a script, but when I start looking over what's available
via pdox help I notice the words "the SQL driver" being installed. Is
that required in order to do local SQL commands?

BTW, my use is stricly standalone, on one PC.

As a followup can you briefly explain or point to a source, on how one,
after creating via the SQL editor the code as mentioned, "call" or
execute that piece of SQL logic?

Thanks again for the input.

Mike

#9

Quote:

One of my drawbacks is a lack of "current" pdox OPAL book documentation to
read, so I'm using more of the pdox built-in Help for these "later"
features which are mentioned.

so far, the suggestions have been:

scan
restructure
ascii export/import
SQL query

all of these things are part of PdoxDOS, too.. the syntax is a bit
different, but the basic concepts are the same..

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards
"Mike" <micoma (AT) aol (DOT) com> wrote

Quote:

Liz -

Ah, the light starts to get brighter; thanks.

One of my drawbacks is a lack of "current" pdox OPAL book documentation
to read, so I'm using more of the pdox built-in Help for these "later"
features which are mentioned.

Found the SQL query "expert", so now have a better understanding of
what Larry meant in his code example.

I do hope to make the old PAL procedures "hands-off" via OPAL, so will
followup on the many suggestions that have been put forth. You know, a
few using method "a", a few using "b", etc.etc

Mike

Liz McGuire wrote:
Mike,

You can save SQL queries just like you can save QBE queries (as a
file) and then just run the file manually. If you wanted to put the
query in code, then it's rather like you do with QBE queries - check
the ObjectPAL help for SQL, especially executeSQL().

To run SQL against Paradox tables, you do not need anything
special/extra installed. I'm sure what you were reading is in
relation to non-Paradox tables accessed via ODBC, etc.

Liz

Mike wrote:

SELECT YourShort, YourAlpha, CAST(YourShort AS CHAR(5)) || YourAlpha
FROM MikesTable

Thanks Larry; this approach would certainly be a "move up" as it were
from rewriting a script, but when I start looking over what's available
via pdox help I notice the words "the SQL driver" being installed. Is
that required in order to do local SQL commands?

BTW, my use is stricly standalone, on one PC.

As a followup can you briefly explain or point to a source, on how one,
after creating via the SQL editor the code as mentioned, "call" or
execute that piece of SQL logic?

Thanks again for the input.

Mike

#10

While you are getting started with Pdox Win I suggest you check out my Database
basics paper on our paradox resources page for some best practices and common
pitfalls. It will help you with some of the major differences. Link in my
signature

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982