SQL/VFP 5.0 problem

[PBREP]

Hi All:
I have been trying to use a PADL function. My problem is applying this to the ‘Prompt’ for the ODBCLink script I written. I cannot get it to work within my Select statement – I’m not sure if my syntax is incorrect, or if I am limited because of the field I am working with. Please see my Notes and code below:

Thanks,
~PM

NOTES:

1) In my first Select statement, I am having a problem matching the *Prompted Text* field to their order number. In the Host database (Progress), the Data Type and size of the order# field is CHAR 7. Most of their order #’s are 6 characters long with a leading space. Some of the order #’s are 7 characters and do not have a leading space. I need a solution to “PAD” a leading space onto the *Prompted Text* field.
The ‘WHERE’ clause in my script looks like this - WHERE co.co-num = "<>"
I have already tried the following:

a. Using a Progress TRIM function on the left side of the operation - WHERE TRIM(co.co-num) = "<>". This returned the proper results, but by using the TRIM function on the Primary Key field in their database, I essentially removed the index and it took almost 5 min. to return the results of my query. This is not acceptable.

b. I’ve tried using a PADL function on the right side of the operation, but I cannot get the syntax correct. I have tried: PADL("<>") & “PADL(<>)”. I don’t believe my syntax is correct – can you recommend a better way to format this??
c. I’ve tried forcing a space into the script - WHERE co.co-num = "<>". This works for a 6 character order #, but not for a 7 character value.

Can you help with this issue???
2) The second issue I am having is reading fields from the Host database with a Data Type – Logical or Bit. There are not any logical fields in the ODBCLink script (3rd party provided) to which I can map these fields. There are 4 different fields in question. The values are ignored. I do not get any errors from my script, but I also do not have any values returned. I have done a lot of work on this issue. I used the POST SQL EXEC PowerAct to open a VFP Browse window to view the cursor which holds the results of my Select statement before saving to the Batch table. In the cursor I can see the values for these logical fields as either an ‘T’ or an ‘F’, but nothing is saved to Batch.
I believe the issue is only present in VFP5. I re-created a Select statement in VFP5 and VFP6 – outside of third party software – and I see the same result. I have also tried using the Post SQL EXEC to call a custom function where I tried to capture the value I see in the cursor and force it to a specific data type before saving it to Batch. I tried forcing it to a logical type, also tried forcing it to a Character type.

&&Code
select taxcode.tax-rate, co-ss-info.contact-phone,
co-ss-info.contact-name, co-ss-info.spec-del-inst[6],
co-ss-info.spec-del-inst[7], co-ss-info.spec-del-inst[8],
co-ss-info.spec-del-inst[9], co-ss-info.spec-del-inst[10],
co-ss-info.tax-id, co-ss-info.freight-allowed, co-ss-info.acct-num,
co.price, co-ss-info.cod-tag-adj, co.terms-code, custaddr.country,
co.co-num, co.contact, co.cust-po, custaddr.name, custaddr.addr__1,
custaddr.addr__2, custaddr.addr__3, co.contact, custaddr.city,
custaddr.state, custaddr.zip, custaddr.credit-hold, co.ship-code,
custaddr.cust-num, co.decifld2, customer.logifld FROM drmnt.CO CO,
drmnt.CUSTADDR CUSTADDR, invdorm.co-ss-info co-ss-info, drmnt.taxcode
taxcode, drmnt.CUSTOMER CUSTOMER where co.co-num = "<>" AND && This line in question...
(drmnt.custaddr.CUST-NUM = drmnt.co.CUST-NUM) and
(drmnt.custaddr.cust-seq = drmnt.co.cust-seq) and
(invdorm.co-ss-info.co-num = drmnt.co.co-num) AND
(Drmnt.taxcode.tax-code = drmnt.co.tax-code1) AND
(drmnt.CUSTOMER.CUST-NUM = drmnt.co.CUST-NUM) and
(drmnt.CUSTOMER.CUST-SEQ = drmnt.co.CUST-SEQ)

 

[rgbean]

I'm not really sure what 'WHERE co.co-num = "<>"' means.

In VFP, if you have xx="234567" or xx="1234567" to do comparisons on and you want to pad on the left for 7 characters, then you'd simply use:

PADL(xx,7)

Since what you've presented is clearly NOT VFP syntax, I don't understand why you think this is a VFP 5.0 issue.

Perhaps I've totally misunderstood, and you may need to simplfy the question.

Rick

 

[PBREP]

Hi Rick:

'WHERE co.co-num = "<>"', "<>" means PROMPT (i.e. an input box prompts the user). It is intergrated in the third-party software/tools (i.e. ODBC Link setup).

Thanks,
~PM

 

[rgbean]

~PM,
OK, then have you tried 'WHERE co.co-num = PADL("<>",7)'?

Or may be the more aggressive 'WHERE co.co-num = PADL(LEFT(ALLTRIM("<>"),7),7)'? (This one trims any leading or trailing spaces, gets at most the first 7 characters, and then pads on the left enough spaces to make the string exactly 7 characters long!)

Rick

 

[PBREP]

Thank you Rick for the suggestions, I'll give it a whirl.

~PM