Focal Point
Sub Querry In WebFocus...
March 31, 2008, 02:21 AM
focuzsambitSub Querry In WebFocus...
Do we have any concept of subquerry in webfocus ?
If yes, any pointer regarding where the below code going wrong ??
TABLE FILE B_SQL_CONTRACTSCHEDOFVALUES
SUM
SCHEDULEDVALUE
BY B_SQL_CONTRACTSCHEDOFVALUES.PROJECTID
WHERE PROJECTID IN (
TABLE FILE HLDDTL1
PRINT PROJECTID
END
)
ON TABLE HOLD AS HLDDTL2 FORMAT FOCUS INDEX PROJECTID
END
Thanks...
March 31, 2008, 03:08 AM
GamPSam,
Webfocus does have a statement like 'where field in ...', but the individual values should be a file, not a new request. So turn the order around, first create your hold file with the values to be tested for, next create your table request that uses the just created hold file.
So, in your piece of code it would read:
TABLE FILE HLDDTL1
PRINT PROJECTID
ON TABLE HOLD
END
TABLE FILE B_SQL_CONTRACTSCHEDOFVALUES
SUM
SCHEDULEDVALUE
BY B_SQL_CONTRACTSCHEDOFVALUES.PROJECTID
WHERE PROJECTID IN (HOLD);
ON TABLE HOLD AS HLDDTL2 FORMAT FOCUS INDEX PROJECTID
END
Hope this helps ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
March 31, 2008, 03:14 AM
Tony Aquote:
Do we have any concept of subquerry in webfocus ?
This sort of question would have been anwsered in any basic training or by reading the manual(s).
Push hard for training with your management - it will benefit them and you in the long term.
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
March 31, 2008, 03:30 AM
focuzsambityou should really help yourself know that you have some 20/30 yrs of exp....
WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
March 31, 2008, 09:16 AM
iraJust an added note.
If you are going to use Where Projectid EQ (DDNAME) and the number of projectid's is large - then you should be aware that there is a 3200 byte limitation. If you believe you are going to exceed THAT NUMBER, then you should be prepared to either (A) split the records into more than one file or (better yet) use (B) a 'decode ddname' where you get
32000 bytes. Again, if you know the number of records is going to be relatively small consistently - then the technique suggested is perfectly fine as is.
Ira
wf 538 aix 533
aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
March 31, 2008, 09:19 AM
GinnyJakesIra,
If you are going against a relational db with a WHERE, the byte limit is 16000.
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
March 31, 2008, 10:02 AM
Tony A... or use a join, match or even something like an MFD join via DKU etc.
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
March 31, 2008, 11:04 AM
Alan BAlthough not sure of performance (suspect not great), I wonder how DB_LOOKUP might work for this.
Alan.
WF 7.705/8.007
March 31, 2008, 11:46 AM
GinnyJakesGam's suggestion is the way to go if the data size meets the restrictions.
New syntax is
WHERE [NOT] fieldname IN FILE filename
and it works wonderfully. The beauty of this method is that it avoids hetergeneous joins which are a definite no-no.
If the size is too big, a join would work.
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
March 31, 2008, 12:11 PM
Darin LeeThere is not a sub-query per-se. But as indicated by other posts, you can use multiple queries, in sequence, to do the same thing.
This may be overly simplistic, but since you're really not requiring any FOCUS-specific functionality, just write this query using SQL-passthru, as this seems to be what you're most familiar with. Sounds like that would be the simplest approach, require least code, least IO, no hold/ external files to reference, everything handled by the DB.
Lots of documentation and additional posts exist regarding SQL-passthru.
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat