Limit of 256 Fields in Select SQL Cursor

[mstrcmtr]

Select SQL Cursor is the best option of Joining Multiple tables in One Cursor especially for Creating Reports

There is a Limit of 256 Fields in Curosr or Table

IF joining tables for making report on( Group By ) Exceed 256 Fields Limit

What is the solution ?

 

[GriffMG]

One way is to combine text fields into concatenated fixed length strings.

select (field1+field2+field3) as LongField from...

But bear in mind that there are limits to the length of a query string too

Another way is to get SOME of the data into the cursor, but get the rest using lookup functions in the report itself.

So you combine your tables, then use keys from them to generate the content in the report

So a control on the report might have the contents

MyLookUp(MyCursor.KeyField,"MyField")

Function MyLookUp
Parameters m.KeyField,m.RetVal
Private m.KeyField,m.RetVal, m.OldArea
m.OldArea = Select()
Select MyTable
Set order to MyTag
Seek (m.KeyField)
m.RetVal= EVALUATE(m.RetVal)
Select (m.OldArea)
Return(m.RatVal)

Regards

Griff
Keep [Smile]ing

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

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[tomk3]

Hello,


as you wrote a cursor is a (temp) table so it cannot have >255 fields.

https://fox.wikis.com/wc.dll?Wiki~VisualFoxProLimits

You have to change the view / sqlexec / CA to include only the fields you need.
So no "select * from ..." but "select adr.adress,adr.pk_adr ,...."

You may create a Remote view graphically as needed and then switch to SQL-View to get the statement.


Regards
tom

 

[Mike Lewis]

256 is a heck of a lot of fields for one report. Are you sure you need that many?

Concatenating fields, as per Griff's suggestion, is one solution. Or consider Griff's MyLookup() function, or alternatively VFP's native LOOKUP().

Another solution would be to use two or more cursors and to use SET RELATION to link them.

But I think the best approach would be to take a hard look at your design, and ask yourself why have you need so many fields.

By the way, the limit is 255, not 256. But that's not really relevant.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Hadn't thought about LOOKUP(), not used that - probably ever. Does it leave you in the original place (so to speak, original workarea, record and index order)?

Regards

Griff
Keep [Smile]ing

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

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Griff, LOOKUP() doesn't change the work area, but it does move the record pointer (just like a SEEK or LOCATE), so for that reason it wouldn't be suitable for finding data in the main cursor (the one that controls the report). But I think the same issue would apply to your version - unless you saved the record number and index tag before doing your SEEK().

I was thinking more along the liens of using LOOKUP() to search a subsidiary cursor, if that's possible in this case.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

In that case it could be quicker than my approach, if you could be sure it was called 1st on any given detail, you could perhaps rely on it to have located the record of interest
and then you could call off the fields you want.

Regards

Griff
Keep [Smile]ing

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

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​