December 18, 2007, 08:51 PM
Francis MarianiWeird problem in SQL translation of selection criteria based on a DEFINE
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
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 19, 2007, 08:50 AM
jgelonaFrancis,
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
December 19, 2007, 10:07 AM
TexasStingrayIf you want WebFOCUS to process the define the turn it into a compute and use WHERE TOTAL.
Scott
December 19, 2007, 10:11 AM
Francis MarianiTexasStingray,
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,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 26, 2007, 12:26 PM
N.SelphIf 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
(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
January 23, 2008, 01:17 PM
BethanyI'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
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
January 23, 2008, 04:53 PM
GinnyJakesIndex 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.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
January 23, 2008, 04:56 PM
GinnyJakesKerry,
This is very strange but the last two posts in this topic belong to another topic. What happened?
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
January 23, 2008, 05:29 PM
KerryHi 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
Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
January 23, 2008, 05:41 PM
GinnyJakesThe reply was to Bethany's post. Starting from there, this all belongs to a different topic.
She'll never get her answer this way.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
