Select statement field length Last name

[DiGi2]

I have a table that has Last name and first name in 1 field seperated with a comma.
Example : GUTIERREZ, MERCEDES
If I issue the command line ?left(customer,At(",",customer)-1) I get
GUTIERREZ

however when I do a select statement and try to do all the records, they are all truncated. No last name has any more than 5 letters
eg:
SELECT job as wordnumb, assigned as inspector, contractor as cmpynumb,;
electricut as woh_ele,;
gasutility as woh_gas,
left(customer,At(",",customer)-1) as woh_last,;
ALLTRIM(SUBSTR(customer, AT(',', customer)+1)) as woh_firs,;
address as woh_sad1, city as woh_scit, zip as woh_szp5, ;
CTOD('03/18/2013') as reportdate, CTOD('03/18/2013') as statusdate, "O" as status,SPACE(1) AS insptype;
FROM mar18report WHERE job NOT IN (sele wordnumb FROM cpmast)

All of the records have only 5 characters.

Can anyone offer any insight into this?

 

[mm0000]

Could you change 'left(customer,At(",",customer)-1) as woh_last' to 'substr(customer,1,At(",",customer)-1) as woh_last' and check the results.

MM

 

[mm0000]

I've just checked my suggestion will not work.

An option would be take the data from mar18report into a cursor, create 2 additional fields for first and last name. Populate the 2 fields with the first and last names using

replace all woh_last with left(customer,At(",",customer)-1)
replace all woh_firs with ALLTRIM(SUBSTR(customer, AT(',', customer)+1))

and then running your SQL query.

MM

 

[Olaf Doschke]

The result field length is deterrmined by the first result. So most probably all last names are truncated, because the firs last name is 5 chars long only. VFP doesn't automatically determine the field length needed for any expression result. How should it do that? Where should it store all the values temporary before being able to create the result fields?

CAST or PAD every char expression you select to get the field length you need for all names. Besides that, use getwordnum:

select cast(getwwordnum(customer,1) as c(50)) as lastname, ;
cast(getwordnum(customer,2) as c(50)) as firstname;
from mar18report

Better you have the seperate names in the first place, especially if you combine them in that temporary report table or cursor in the first place, which the name mar18report suggests. If that is created by alltrim(lastname)+", "+alltrim(firstname) then this would really be just very overcomplicated, wouldn't it? you can have lastname and firstname in such a table or cursor for a report and have an expression in a report field putting together that "lastaname, firstname".

Bye, Olaf.

 

[Mike Lewis]

Just to add a note to Olaf's advice:

The CAST() function was new in VFP 9.0. In earlier versions, you can use PADL():

SELECT ;
  PADL(GETWORDNUM(Customer, 1, ", "), 50) AS LastName, ;
  PADL(GETWORDNUM(Customer, 2, ", "), 50) AS FirstName, ;
  FROM ....

The aim is to pad the strings out to the required number of characters.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

PADL would be fine, but you would rather pad the names to the right in a table field, so just use PADR instead.

Bye, Olaf.

 

[Olaf Doschke]

And Mike has made a more important change: As you don't want the comma in either of the single names, make it a seperator of getwordnum as Mike did.

Bye, Olaf.

 

[Mike Lewis]

Of course I meant PADR(), not PADL(). Thanks, Olaf. I got up too early this morning.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy