Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Sub Querry In WebFocus...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Sub Querry In WebFocus...
 Login/Join
 
focuzsambit
Platinum Member
posted
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...
 
Posts: 133 | Registered: May 12, 2007Report This Post
GamP
Virtuoso
posted Hide Post
Sam,
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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Tony A
Expert
posted Hide Post
quote:
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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
focuzsambit
Platinum Member
posted Hide Post
you should really help yourself know that you have some 20/30 yrs of exp.... Smiler


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
 
Posts: 133 | Registered: May 12, 2007Report This Post
ira
Platinum Member
posted Hide Post
Just 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
 
Posts: 195 | Registered: October 27, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
Ira,

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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Tony A
Expert
posted Hide Post
... 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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Alan B
Virtuoso
posted Hide Post
Although not sure of performance (suspect not great), I wonder how DB_LOOKUP might work for this.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Gam'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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Darin Lee
Virtuoso
posted Hide Post
There 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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Sub Querry In WebFocus...

Copyright © 1996-2020 Information Builders