Concatenate memo fields in SQL SELECT? 1

[bfwriter]

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.)

SELECT memofield1 + memofield2 AS bigmemo FROM temptbl_2 INTO dbf temptbl

Yet this work-around is successful:

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

 

[Mike Lewis]

You need to specify the length of the target field. Try something like this:

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]

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]

Why not add them together afterwards?

Regards

Griff
Keep [Smile]ing

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

 

[TamarGranor]

If you're in VFP 9, use CAST:

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


Tamar

 

[bfwriter]

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]

Mike,

Your code is not going to work without CAST function suggested by Tamar.

 

[Mike Lewis]

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:

SELECT CAST(Memo1 + Memo2 AS Memo) [b]AS NewMemo[/b]

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[bfwriter]

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

 

[Olaf Doschke]

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]

Bill - you might find my e-book "Taming Visual FoxPro's SQL" useful. It's available from

http://www.hentzenwerke.com,

and it covers all of VFP's SQL commands.

Tamar

 

[markros]

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

 

[Mike Lewis]

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