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>,

Here's a sketch for one-pass code:

-default &this='Shirt';
define file transtable
hits/i5 = item contains '&this';
end

table file transtable
write
sum.hits noprint
by transid

print
some detail columns
by transid
by itemid

where total sum.hits gt 0;
end


But, depending on the generated SQL, that may not improve performance (might even degrade it)

It may be worth investigating a couple 7.705 ehancements:

Creating a Subquery or Sequential File With HOLD FORMAT SQL_SCRIPT

and

DB_INFILE Function: Testing Values Against a File or an SQL Subquery

Just to expand on my previous comment.

MFD:

FILENAME=BASKET_CASE, SUFFIX=SQLMYSQL, $
  SEGMENT=BASKET_CASE, SEGTYPE=S0, $
    FIELDNAME=BASKET_CASE_KEY, ALIAS=basket_case_key, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=BASKET_NUMBER, ALIAS=basket_number, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=ITEM_SKU, ALIAS=item_sku, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=ITEM_DESCRIPTION, ALIAS=item_description, USAGE=A45V, ACTUAL=A45V, $

FEX:

TABLE FILE basket_case
PRINT basket_number
WHERE item_description IN ('Shirt')
ON TABLE HOLD AS select_sub FORMAT SQL_SCRIPT
END

TABLE FILE basket_case
PRINT item_sku item_description
BY basket_number
WHERE DB_INFILE(select_sub,BASKET_NUMBER,BASKET_NUMBER)
END

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:

SELECT                                                                      
T1.'basket_number',                                                          
T1.'item_sku',                                                               
T1.'item_description'                                                        
FROM                                                                        
basket_case T1                                                               
WHERE                                                                       
(T1.'basket_number' IN ( SELECT                                              
(T1.'basket_number') AS E01                                                  
FROM                                                                        
basket_case T1                                                               
WHERE                                                                       
(T1.'item_description' = 'Shirt')))                                          
ORDER BY                                                                    
T1.'basket_number'; 

This 'should' be faster than running through a HOLD file.

Sweet!

Thank for the great suggestions. They both worked well however creating the subquery worked the best.

I appreciate all the help!

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).

A self-join should work too, for example:

JOIN
 TRANSACTION_ID IN TRANSACTION TAG txItem TO UNIQUE
 TRANSACTION_ID IN TRANSACTION TAG tx AS J0
END
TABLE FILE TRANSACTION
SUM tx.QUANTITY tx.PRICE
BY tx.ITEM
BY tx.DESCRIPTION 

WHERE txItem.ITEM EQ 'Shirt';
END

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