Snippet of my code:

DEFINE FILE MERCHANDISEREQUEST
...
CATEGORYID/A38 WITH MERCHANDISEREQUEST.REQUESTID = '{00000000-0000-0000-0000-000000000000}';
...
END
TABLE FILE MERCHANDISEREQUEST
...
WHERE CATEGORYID IN FILE CECAT
...
END

File CECAT contains one row with this in it:

'{00000000-0000-0000-0000-000000000000}'

The SQL that gets generated is this:

SELECT ...
WHERE ...
AND
 ('{00000000-0000-0000-0000-000000000000}' =
 '{00000000-0000-0000-0000-000000000000}')
...

I thought since the field is a DEFINEd field, the WHERE statement would not be put in the SQL statement, but be done after the data is retrieved.

I get the following in the execution of the request, but no WebFOCUS error:

 Invalid root page
 Invalid root page
 Invalid root page
 Invalid root page
...
0 NUMBER OF RECORDS IN TABLE= 0  LINES= 0

The reason why I'm selecting on a constant is that this is a complicated program with many data extraction steps, some are done or not done depending on options selected on the launch form. Sometimes File CECAT will contain other rows, including the 0000 one or not. I'm going to have to avoid doing it like this I guess.

Francis,

Some defines can/will get translated. This happens when:

quote:

For RDBMS-managed record selection, the DEFINE expression must be an arithmetic valued expression, a character string expression or a logical expression.

quote:

FOR RDBMS-managed aggregation, the DEFINE expression must be arithmetic or character string valued expression.

Arithmetic valued expressions are scalar and return a single number. DEFINE fields are considered to be of this class if the expression on the right includes one or more of the following:

Real field operands of a numeric datatype;
Numeric constants;
Arithmetic operators (+ , - , * , /);
Other DEFINES that include the above.

Character string valued expressions return a character string. DEFINE fiels are are considered to be of this class if the expression on the right include one or more of the following:

Real fields of alphanumeric datatype;
String Constants;
String Concatenation operators ('|' or '||')
other DEFINE with the above.

I got this from my book, "Interface to Oracle Users Manual", chapter 7. There are examples and a more detail discussion. As I've stated in other posts, this manual was for FOCUS for UNIX 6.5. I don't know if there is a more up to date manual.

Your example would be considered a string constant

If you want WebFOCUS to process the define the turn it into a compute and use WHERE TOTAL.

TexasStingray,

Thanks for the documentation quote. Would you happen to know what the "Invalid root page" error might be? Could it be caused by my joins? I was joining two or three MS SQL Server tables AND a FOCUS DB! Not very efficient I realize.

I've rewritten the code.

Thanks,

If you want to force your define NOT to Optimize (wierd, but sometimes you have to), I do something like this:

DEFINE FILE MERCHANDISEREQUEST
...
CATEGORYID/A38 WITH MERCHANDISEREQUEST.REQUESTID = IF NE 1 THEN '' ELSE '{00000000-0000-0000-0000-000000000000}';
...
END

I'm also getting a strange Invalid root page message, but it's happening before I have any defines.

Here's code up to where I get the error.

 -DEFAULT &TERM = '1082';
-DEFAULT &RPT = ALL;
-DEFAULT &AMT = 0;
SET ASNAME = ON
TABLE FILE REC_STDNT_CAR_TERM_DIM
PRINT
CTERM_ACAD_CAREER
CTERM_TERM_CD
CTERM_STDNT_CAR_NBR AS CAR_NBR
BY CTERM_EMPLID
WHERE CTERM_ACAD_CAREER EQ 'UGRD'
AND CTERM_TERM_CD EQ '&TERM'
AND UNT_TAKEN_PRGRSS GE 12;
ON TABLE HOLD AS HSTDNT FORMAT FOCUS INDEX CTERM_EMPLID CTERM_STDNT_CAR_NBR
END
SQL 
SELECT APROG_EMPLID, APROG_ACAD_CAREER, APROG_STDNT_CAR_NBR, APROG_EFFDT, APROG_ACAD_PROG, APROG_ACAD_PROG_LDESC, APROG_PROG_STATUS, APROG_PROG_STATUS_SDESC, APROG_PROG_ADMIT_TERM, APROG_PROG_ADMIT_TERM_SDESC FROM REC_ACAD_PROGRAM_S1
WHERE APROG_EMPLID IN (SELECT CTERM_EMPLID FROM HSTDNT)
AND APROG_PROG_STATUS = 'AC'
AND APROG_ACAD_CAREER = 'UGRD';
TABLE ON TABLE HOLD AS HPROGS FORMAT FOCUS INDEX APROG_EMPLID
END 

Here is the error I'm getting...
0 NUMBER OF RECORDS IN TABLE= 8616 LINES= 8616
1
0 NUMBER OF RECORDS IN TABLE= 8616 LINES= 8616
0
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
etc, etc, etc, etc......

I have no idea why, but I had this problem before and moving the report from one domain to another somehow resolved the error. I tried doing that again and did work the first time I ran it, but after making a couple changes to the code (at a later point in the report) and rerunning it, I get the invalid root page again.

Any ideas????

Bethany

Server Environment:
Win2K3 Server
WebFOCUS 7.13
Apache Tomcat standalone application server

Index names for FOCUS files can't be any longer than 12 characters.

CTERM_STDNT_CAR_NBR is too long a field name.

Create a DEFINE to make the field name shorter and your problem should go away.

Kerry,

This is very strange but the last two posts in this topic belong to another topic. What happened?

Hi Ginny,

I am not exactly sure why, but please let me know if you would like me to move your post to wherever it should belong to.

Cheers,

Kerry

The reply was to Bethany's post. Starting from there, this all belongs to a different topic.

She'll never get her answer this way.