Hello,
i think i have strange problem. I need to collect values from a SAP BW which has 700000 records, i need details for 240 entries. i thought i can solve this by using IN FILE, but i got a message that the memory of the SAP BW is not large enough. Now is my idea to call every single record in a loop and add this record to a focus file. So i get my 240 records step by step. I think i can run this tasks scheduled by the report caster at night.

Regards

Christian

This message has been edited. Last edited by: ChristianP,

Christian,

Normally, with relational databases, SQL selection criteria (WHERE statements) can have up to 1000 test values, so I'd be surprised that SAP BW does not allow even 240. Perhaps it's how the program is coded. Did you turn SQL traces on?

YUCK to:

---

"call every single record in a loop and add this record to a focus file. So i get my 240 records step by step." .

---

Can you HOLD those 240 records and work with that? That way it's on the WebeFOCUS side of things, not the SAP BW side (just athought)?

If you are doing the loop, I would join to the records in the IN FILE, instead. It will effectively do the same thing.

In fact it would be better to join from the records in the IN FILE.

How many values can you use with IN FILE before you get a memory issue?

If it's a manageable amount, say 50-ish, you could split your list of values across multiple files; to follow the example, you'd end up with 5 files (let's say HVAL1, HVAL2, ..., HVAL5).

You could then loop through them and query your data referencing them with IN FILE. Something like:

APP FILE HRESULTS DISK hresults.ftm (APPEND
-RUN

-REPEAT :GETDATA FOR &I FROM 1 TO 5
TABLE FILE MYSAPTABLE
SUM ....
WHERE MYFIELD IN FILE HVAL&I ;
ON TABLE HOLD AS HRESULTS
END
-RUN
-:GETDATA

-* Process HRESULTS ...
TABLE FILE HRESULTS
blah
END

You'll be hitting the DB 5 times ... not optimal but better than hitting it 240 times with the row-by-row approach, I think.

This message has been edited. Last edited by: njsden,

I would still follow Francis' suggestion of enabling traces to determine what's going on at the SAP level.

I can't imagine an enterprise platform such as SAP blowing up when trying to handle a query statement that has an IN clause with 240 values in it ... there must be something else ...

Hello,
thanks for the help so far. I think it is even an internal political problem, because the SAP BW Team provides in our campany BO. And i think they limit the memory for our WEBFocus requests.

Regards

Christian

Oh brother! they should revisit their policies then. SAP BW is a business intelligence platform and as such it should allow BI tools to have as much resource availability as possible because they are supposed to be dealing with significant volumes of data upon reporting/analysis.

Unless as you say, "political reasons" may be motivating them to push WebFOCUS away in favour of SAP's own BI technology ... hmmm, sounds like a conspiracy ...

This message has been edited. Last edited by: njsden,

From experience, databases and other BI/DW platforms run so much more efficiently when there's no users on the system locking tables and hogging resources.

Of course, one will not be able to tell that for sure if one cannot log on....

-ABT

Hello,
in the meantime, I build a loop but i can't put the records together!!
I get the message that the table with x lines have been build, but they are not build
together. When i look at the result, i can only see the last result.
I also tried it with the Append, same result.

 
-RUN
-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;



TABLE FILE xxx
SUM
     vvv
     vvv
BY  ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA 
END
-RUN
-ENDLOOP
-RUN
 

Look at the first line of the sample code I gave you originally ... just adjust it to your specific HOLD file name.

The must be a -RUN after that APP FILE or it will not work.

quote:

I also tried it with the Append, same result.

Please post the code exactly as you had it using (APPEND. I've used that feature multiple times in the past and it's never failed me.

Hello njsden,

please take a look at the code

APP FILE RESULT DISK result.ftm (APPEND
-RUN

-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;



TABLE FILE xxx
SUM
     vvv
     vvv
BY  ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA 
END
-RUN
-ENDLOOP
  

I got a FOC 224 Syntax Error message. Thanks for you help !

Regards

Christian

quote:

I got a FOC 224 Syntax Error message. Thanks for you help !

Where exactly?

Could you please work an example using the CAR table (or GGSALES) so we can replicate and perhaps be better equipped to help?

quote:

-SET &LIMIT=&LINES;

What is &LINES set to in this line? zero? or are you getting results from a previous run not shown here?

Got it!

Try using instead:

FILEDEF RESULTS DISK result.ftm (APPEND

That ought to do it.

Yep. That works.

TABLE FILE CAR
PRINT COUNTRY AS KEY
ON TABLE HOLD AS HKEYS FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN

-SET &LIMIT=&LINES;

FILEDEF HRESULTS DISK result.ftm (APPEND
-RUN

-REPEAT :ENDLOOP &LIMIT TIMES
-READFILE HKEYS

TABLE FILE CAR
PRINT COUNTRY AND CAR AND MODEL
WHERE COUNTRY EQ '&KEY'
ON TABLE HOLD AS HRESULTS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-:ENDLOOP

TABLE FILE HRESULTS
PRINT COUNTRY AND CAR AND MODEL
END

quote:

APP FI[LEDEF]
The APP FI[LEDEF] command has been deprecated and aliased to FILEDEF. For information, see the Stored Procedure Reference.

Old-timers curse!

The reason why you see your last result only is very simple. When you issue the -RUN, the stack is emptied and the input file is closed. Try it with the NOCLOSE option and, if needed, close it with the command -CLOSE filename

Hello njsden, H Lidholm

thanks for your help!!! I got it!!! At least i thougt the table has to have the same name as the .ftm file. But now it works great for me!!!

Regards

Christian