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 firstname.lastname@example.org for further assistance. Reference the community FAQ to learn more about the community.
I've done some research on here to try and find the answer to this question. I'm sure its out here somewhere and I'm probably just not searching on the right Keywords.
I'm working with transaction table. As you can imagine most transaction contains more than one record associated with a transaction#. Example Transaction 1 Item =Shirt Item=Shoe Item =Pant
Transaction 2 Item=Shirt Item=Pant
Transaction 3 item=Pant item-Shoe
What I would like to do is get the details of the entire transaction if any of the items within transaction contains "shirt". So in the above example I want the results of Transaction 1 and Transaction 2 with all the detail. Is there a Where condition that would allow for this. Right now I'm doing two passes one that gives me all the transaction# that sold a 'Shirt" I'm holding the file of Transactions# and joining it again to the transaction table to get the total details, but I feel like there would be a way to do this in one pass - and make my query much quicker.
Any insight would be appreciated.This message has been edited. Last edited by: <Kathryn Henning>,
TABLE FILE basket_case
WHERE item_description IN ('Shirt')
ON TABLE HOLD AS select_sub FORMAT SQL_SCRIPT
TABLE FILE basket_case
PRINT item_sku item_description
In the first TABLE request, the SQL SCRIPT is saved as:
SELECT (T1.'basket_number') AS E01 FROM basket_case T1 WHERE (T1.'item_description' = 'Shirt')
The second TABLE request produces the following SQL:
(T1.'basket_number' IN ( SELECT
(T1.'basket_number') AS E01
(T1.'item_description' = 'Shirt')))
This 'should' be faster than running through a HOLD file.
Alan. WF 7.705/8.007
Posts: 1451 | Location: Portugal | Registered: February 07, 2007
Note on using the FORMAT SQL_SCRIPT feature as illustrated.
If the first query involved SUM ... BY key , the generated SQL query will have GROUP BY key and ORDER BY key.
The ORDER BY is unavoidable (because of the way the product works) and unneeded, and undesirable: When you use DB_INFILE in the second request, the inclusion of GROUP BY key in the inner query of the generated SQL code may degrade performance (relative to the same query less that phrase).
- Jack Gross WF through 8.1.05
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005
TRANSACTION_ID IN TRANSACTION TAG txItem TO UNIQUE
TRANSACTION_ID IN TRANSACTION TAG tx AS J0
TABLE FILE TRANSACTION
SUM tx.QUANTITY tx.PRICE
WHERE txItem.ITEM EQ 'Shirt';
Make sure you use those tags to reference the correct table from the join, namely the one with all the items in the transaction and not just those that are about the shirt.
Oh, and don't use tags longer than 8 characters on anything before 8.something; it won't throw an error, but it will fail to find your fields... although in this case it might instead use the fields in the other half of the self-join, which is possibly even worse.
Self-joins like these can get you into trouble if you use conditional joins instead of "straight" joins, as IBI/WebFOCUS mistakes the self-join for a recursive join and they refuse to perform those on anything but FOCUS-files. If you don't know what a conditional join is, you probably have nothing to worry about
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :