Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Concatenate memo fields in SQL SELECT?Helpful Member! 

bfwriter (TechnicalUser) (OP)
8 Dec 09 14:28
Hello Friends,

The following attempt to concatenate two memo fields in a SQL SELECT query fails, giving the error "String too long to fit. (Both memo fields are ordinary text.)

CODE

SELECT memofield1 + memofield2 AS bigmemo FROM temptbl_2 INTO dbf temptbl

Yet this work-around is successful:

CODE

SELECT memofield1, memofield2 FROM temptbl_2 INTO DBF temptbl
SELECT temptbl
REPLACE memofield2 WITH memofield1+memofield2 ALL

Is there a way to accomplish this all in the original SELECT SQL query?

Thanks
MikeLewis (Programmer)
8 Dec 09 15:06
You need to specify the length of the target field. Try something like this:

CODE

SELECT ;
  PADR(LEFT(memofield1, 250) +LEFT( memofield2, 250), 500);
  AS bigmemo FROM temptbl_2 INTO dbf temptbl

Obviously, this will limit you to a given number of characters, which might not be want you want. But VFP has got to have some way of knowing how wide to make the field within the output table. Memos are variable length, so you can't just add two memos together and get the same width each time.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

bfwriter (TechnicalUser) (OP)
8 Dec 09 15:33
Thanks for your input, Mike.

Are you saying that VFP joins the 2 memo fields and places the result into a Character field of definite length (rather than simply making a third memo field, which I supposed would be variable in length)?

I was hoping there might be something like an SQL SELECT [MEMO] clause, akin to the GATHER [MEMO] command.

Thanks again.
GriffMG (Programmer)
8 Dec 09 15:59
Why not add them together afterwards?

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

Helpful Member!  TamarGranor (Programmer)
8 Dec 09 16:21
If you're in VFP 9, use CAST:

SELECT CAST(Memo1 + Memo2 AS Memo) ...


Tamar
bfwriter (TechnicalUser) (OP)
8 Dec 09 19:32
Many thanks, Tamar. That's the kind of clue I was hoping for. (Yes, I'm in VFP9.)

I'm not familiar with CAST, but it works fine in this application, and I'm getting into it. One limitation is that apparently it doesn't permit the programmer to name the resulting field. The resulting field turns out to be (like it or not) "Expr_[n]", where n=the column position in the resulting table-- which is a usable, though not ideal, result.

Way cool!  Thanks again.

FOR GRIFF: Thanks for your response, Griff. But, as in my original question, an extra step is what I was trying to avoid. The "CAST" function suggested by Tamar is an elegant simplification.
markros (Programmer)
8 Dec 09 21:47
Mike,

Your code is not going to work without CAST function suggested by Tamar.
MikeLewis (Programmer)
9 Dec 09 2:23
BFWriter,

I hadn't thought of using CAST(). It's a better solution than the one I suggested. (I think mine will still work, but it creates a character field rather than a memo, which is not what you want.)

You wrote: One limitation is that apparently it doesn't permit the programmer to name the resulting field. You can solve that by using an AS clause:

CODE

SELECT CAST(Memo1 + Memo2 AS Memo) AS NewMemo

Mike

 

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

bfwriter (TechnicalUser) (OP)
9 Dec 09 8:49
Thanks Mike. That works excellently. It's precisely the kind of thing I was hoping to find. (And, in addition, I came out learning a new VFP function I wasn't aware of.)

You guys are great!

Bill
OlafDoschke (Programmer)
9 Dec 09 8:50
As Mike said, it's not the job of the cast function to name the result, like no function names it's result, that's always done via "AS name".

The "AS" within CAST() is simply a special SQL syntax - as other databases have this too - to specify the result Type, but not the name.

Bye, Olaf.
TamarGranor (Programmer)
9 Dec 09 16:07
Bill - you might find my e-book "Taming Visual FoxPro's SQL" useful. It's available from www.hentzenwerke.com, and it covers all of VFP's SQL commands.

Tamar
markros (Programmer)
9 Dec 09 22:35
Mike,

You can not create a character field in VFP with the length of 500 characters. May be you meant 125 and 125 instead (the maximum length is 255 characters).

CREATE CURSOR ddd (ch C(100))
INSERT INTO ddd VALUES ('test')
SELECT PADR(REPLICATE('_',200) + REPLICATE('*',200),500) as LongField FROM ddd

--Error - String is too long to fit

SELECT CAST(PADR(REPLICATE('_',200) + REPLICATE('*',200),500) as M) as LongField FROM ddd && No error
MikeLewis (Programmer)
10 Dec 09 4:58
Markros,

You're right, of course. But padding it to 254 characters rather than 500 would work, although it still doesn't give the desired result. But, in any case, CAST() is a much better solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close