Getting a Failed to retrieve data from database error

[vsimpkins]

Hi there,
I'm using Crystal Reports XI with MOMs 5.4 & 6.0. The report has a parameter for selecting items for the report. The are a ton of items is the list for which to choose. there's also a parameter for selecting an Association code. When the report is run, depending on the # of items selected an error occurs:
Failed to retrieve data from the database...Details:S1000:[Microsoft][ODBC Visual FoxPro Driver]Compiled code for this line is too long. [Database Vendor Code: 252]. Screenshot shown below. Any help would be great.

Valerie Simpkins

http://www.pacetraining.com

 

[Turkbear]

According to this:

http://msdn.microsoft.com/en-us/library/aa976029(VS.71).aspx

you have exceed some limit in FoxPro's string handling ability - Can you post your selection formula - I would guess that the selecting of too many values for the parameter causes this problem...Not sure there is a workaround without knowing your data or report needs.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vsimpkins]

The values for the filter are passed via parameters at runtime. Here's the selection formula:

(If {?GetItem}="ALL ITEMS" then true else {items.item} = {?GetItem}) and
(If {?GetAssociation} = "ALL ASSOCIATIONS" then true else {stock.assoc} = {?GetAssociation}) and
not ({items.advert} like ["IH*", "DESTROY", "DESK COPY"]) and
{stock.units} <> 0 and
not {stock.discont}

Since any number of items (item #'s) can be selected, I imagine each individual value makes the selection string too long. I thought about dividing into multiple statements but I imagine that statements would be combined when the report is run...making the selection string too long again

Valerie Simpkins

http://www.pacetraining.com

 

[Turkbear]

Hi,
Copy the SQL created by the report [ after selecting multiple values for the params] and run it directly against your database ( not sure what tool FoxPro provides but I am sure there is one)..This may isolate exactly which selection criteria is the failing one and may aid in modifying the formula.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vsimpkins]

SELECT `items`.`item`, `items`.`item_state`, `items`.`r_code`, `items`.`quants`, `items`.`it_unlist`, `items`.`discount`, `items`.`it_uncost`, `stock`.`assoc`, `stock`.`desc`, `stock`.`desc2`, `stock`.`uncost`, `stock`.`units`, `stock`.`commited`, `stock`.`bounits`, `stock`.`onorder`, `cms`.`odr_date`, `cms`.`custnum`, `items`.`advert`, `stock`.`discont`, `cms`.`ship_date`, `stock`.`price1`
FROM (`items` `items` INNER JOIN `stock` `stock` ON `items`.`item`=`stock`.`number`) INNER JOIN `cms` `cms` ON `items`.`order`=`cms`.`order`
WHERE (`items`.`item`='006840317' OR `items`.`item`='01CONFBOOK' OR `items`.`item`='02CONFBOOK' OR `items`.`item`='02CONFSTUD' OR `items`.`item`='0323017452' OR `items`.`item`='0323026664' OR `items`.`item`='0380728524' OR `items`.`item`='0385493029' OR `items`.`item`='0385497458' OR `items`.`item`='0399527885' OR `items`.`item`='03CONFBOOK' OR `items`.`item`='03CONFSTUD' OR `items`.`item`='0473094592' OR `items`.`item`='04CONFBOOK' OR `items`.`item`='0553381156' OR `items`.`item`='05CONFBOOK' OR `items`.`item`='05CONFCD' OR `items`.`item`='05CONFSTDY' OR `items`.`item`='05MEMAPP' OR `items`.`item`='0609807870' OR `items`.`item`='0671027441' OR `items`.`item`='06EXAMBKAPP' OR `items`.`item`='06EXAMBKLT' OR `items`.`item`='06MEMAPP' OR `items`.`item`='0721647162' OR `items`.`item`='0738206091' OR `items`.`item`='0738210730' OR `items`.`item`='0743219333' OR `items`.`item`='0743256115' OR `items`.`item`='0743439635' OR `items`.`item`='0763716499' OR `items`.`item`='076371819X' OR `items`.`item`='076372260X' OR `items`.`item`='0763724815' OR `items`.`item`='0787952273' OR `items`.`item`='0789484404' OR `items`.`item`='07EXAMBKAPP' OR `items`.`item`='0814797792' OR `items`.`item`='086573433X' OR `items`.`item`='08BENAPP' OR `items`.`item`='08EXAMBKAPP' OR `items`.`item`='0912500921' OR `items`.`item`='0962745006' OR `items`.`item`='0962745073' OR `items`.`item`='0966287592' OR `items`.`item`='0969731922' OR `items`.`item`='0972961704' OR `items`.`item`='0976975815' OR `items`.`item`='0976975823' OR `items`.`item`='0976975831' OR `items`.`item`='0976975858' OR `items`.`item`='10GIFTCARD' OR `items`.`item`='10X13ENV' OR `items`.`item`='13080102X' OR `items`.`item`='14011789X' OR `items`.`item`='140512217X' OR `items`.`item`='1434377415' OR `items`.`item`='152016392' OR `items`.`item`='1555612822' OR `items`.`item`='1566563119' OR `items`.`item`='1570671044' OR `items`.`item`='1571741526' OR `items`.`item`='1577311876' OR `items`.`item`='1580050514' OR `items`.`item`='1580620418' OR `items`.`item`='1580620418P' OR `items`.`item`='1580628737' OR `items`.`item`='1589230183' OR `items`.`item`='1592131034' OR `items`.`item`='1593370342' OR `items`.`item`='1593374259' OR `items`.`item`='1886039593' OR `items`.`item`='1891768026' OR `items`.`item`='1891768107' OR `items`.`item`='1892123959' OR `items`.`item`='192623249' OR `items`.`item`='19263173X' OR `items`.`item`='2000CONF' OR `items`.`item`='2002CONFNB' OR `items`.`item`='2002CONFR' OR `items`.`item`='2002CONFT' OR `items`.`item`='2003CONFT1' OR `items`.`item`='2003CONFT2' OR `items`.`item`='20162673X' OR `items`.`item`='201632721' OR `items`.`item`='202011925' OR `items`.`item`='300040873' OR `items`.`item`='316779032' OR `items`.`item`='316779059' OR `items`.`item`='316779059P' OR `items`.`item`='323000851' OR `items`.`item`='323009158' OR `items`.`item`='374522979' OR `items`.`item`='375701958' OR `items`.`item`='385318448' OR `items`.`item`='385496273' OR `items`.`item`='394580117' OR `items`.`item`='399525173' OR `items`.`item`='415923387' OR `items`.`item`='440508002' OR `items`.`item`='443050260' OR `items`.`item`='517880717' OR `items`.`item`='520084314' OR `items`.`item`='520207858' OR `items`.`item`='553373692' OR `items`.`item`='553580744' OR `items`.`item`='555611206' OR `items`.`item`='557043140' OR `items`.`item`='557043175' OR `items`.`item`='558320105' OR `items`.`item`='558320385' OR `items`.`item`='558320415' OR `items`.`item`='558320431' OR `items`.`item`='558320547' OR `items`.`item`='558320652' OR `items`.`item`='558321055' OR `items`.`item`='558321179' OR `items`.`item`='558321179P' OR `items`.`item`='558321292' OR `items`.`item`='558321527' OR `items`.`item`='558321950' OR `items`.`item`='568530323' OR `items`.`item`='632041455' OR `items`.`item`='646168371' OR `items`.`item`='671792180' OR `items`.`item`='674217039' OR `items`.`item`='679450289' OR `items`.`item`='682452546' OR `items`.`item`='711210489' OR `items`.`item`='721620523' OR `items`.`item`='721667775' OR `items`.`item`='721680097' OR `items`.`item`='738200131' OR `items`.`item`='73820188X' OR `items`.`item`='74321241X' OR `items`.`item`='743412737' OR `items`.`item`='761102426' OR `items`.`item`='761109021' OR `items`.`item`='761529969' OR `items`.`item`='763705454' OR `items`.`item`='763709204' OR `items`.`item`='763710377' OR `items`.`item`='763710385' OR `items`.`item`='805041575' OR `items`.`item`='809226154' OR `items`.`item`='809298422' OR `items`.`item`='814207030' OR `items`.`item`='814797679' OR `items`.`item`='815103735' OR `items`.`item`='815123795' OR `items`.`item`='815124538' OR `items`.`item`='852303212' OR `items`.`item`='875964273' OR `items`.`item`='879237806' OR `items`.`item`='880924005' OR `items`.`item`='880924250' OR `items`.`item`='88133717X' OR `items`.`item`='881661775' OR `items`.`item`='890874239' OR `items`.`item`='890875715' OR `items`.`item`='890876339' OR `items`.`item`='890878382' OR `items`.`item`='890879346' OR `items`.`item`='890879559' OR `items`.`item`='891836129' OR `items`.`item`='892814802' OR `items`.`item`='895293579' OR `items`.`item`='895293730' OR `items`.`item`='895294818' OR `items`.`item`='895294907' OR `items`.`item`='895295458' OR `items`.`item`='895947862' OR `items`.`item`='895948559' OR `items`.`item`='897893042' OR `items`.`item`='897894073' OR `items`.`item`='897894278' OR `items`.`item`='897895886' OR `items`.`item`='912500123' OR `items`.`item`='912500247' OR `items`.`item`='912500484' OR `items`.`item`='912500522' OR `items`.`item`='916291960' OR `items`.`item`='920668372' OR `items`.`item`='929240685' OR `items`.`item`='933794088' OR `items`.`item`='934252459' OR `items`.`item`='937604070' OR `items`.`item`='960945687' OR `items`.`item`='961519711' OR `items`.`item`='962352950' OR `items`.`item`='962745073' OR `items`.`item`='962745081' OR `items`.`item`='96274509X' OR `items`.`item`='964113980' OR `items`.`item`='964115964' OR `items`.`item`='964115980' OR `items`.`item`='964115999' OR `items`.`item`='964118319' OR `items`.`item`='964237024' OR `items`.`item`='965987302' OR `items`.`item`='966287509' OR `items`.`item`='966287541' OR `items`.`item`='966287568' OR `items`.`item`='966287576' OR `items`.`item`='966287584' OR `items`.`item`='966799100' OR `items`.`item`='966799119' OR `items`.`item`='96CONFHS' OR `items`.`item`='97037710X') AND `stock`.`assoc`='UN' AND NOT (`items`.`advert` LIKE 'DESK COPY' OR `items`.`advert` LIKE 'DESTROY' OR `items`.`advert` LIKE 'IH%') AND `stock`.`units`<>0 AND `stock`.`discont`=.F.
ORDER BY `stock`.`assoc`, `items`.`item`

When I try to run it directly using the Add Command feature, it states that the statement is too long and won't OK the statement.

Valerie Simpkins

http://www.pacetraining.com

 

[Turkbear]

Hi,
How many items are there?

Just as a attempt to get around that limit, try using the IN operator not the = one and use, as that part of your selection criteria:

(
If {?GetItem}="ALL ITEMS" then true else {items.item} IN Join({?GetItem},",")
 ) 
...rest of your formula

Using the IN operator with a comma-separated list MAY reduce the length of the command by eliminating all the extra items`.`item`= strings - It depends on how the SQL gets created with that formula...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
I forgot the parens around the IN value list, so try:

(
If {?GetItem}="ALL ITEMS" then true else {items.item} IN [COLOR=red]([/color]Join({?GetItem},",")[COLOR=red])[/color]
 ) 
...rest of your formula

If using the Join() function directly in the IN value does not work ( may get a syntax type error) the predefine the value string:

stringVar ItemVals := Join({?GetItem},",")
(
If {?GetItem}="ALL ITEMS" then true else {items.item} IN 
ItemVals ) 
 and
(If {?GetAssociation} = "ALL ASSOCIATIONS" then true else {stock.assoc} = {?GetAssociation}) and
not ({items.advert} like ["IH*", "DESTROY", "DESK COPY"]) and
{stock.units} <> 0 and
not {stock.discont}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vsimpkins]

I used the suggestion you mentioned in the previous post and adjusted the syntax. It appears to be working. I'll test with more values. Thanx a bunch

Valerie Simpkins

http://www.pacetraining.com