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     Things that make me go hmmmm......

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Things that make me go hmmmm......
 Login/Join
 
Silver Member
posted
I know you can do this.....

SQL DB2
SELECT CAR FROM SOME_TABLE
WHERE VEH_MAKE IN ('ROADSTER', 'COUPE', 'CONVERTIBLE')
;
TABLE
ON TABLE HOLD AS A_FILTER
END

TABLE FILE CAR
PRINT *
WHERE CAR EQ (A_FILTER)
END


but you cannot do this (that i am aware of)......

TABLE FILE CAR
PRINT CAR
WHERE SEATS EQ 2
ON TABLE HOLD AS A_FILTER
END

SQL DB2
SELECT * FROM SOME_TABLE
WHERE VEH_MAKE IN (A_FILTER)
;
TABLE
ON TABLE HOLD AS SQLOUT
END



I know I could join A_FILTER to SOME_TABLE but just for fun let's say I DON'T have a master for SOME_TABLE, so I cannot join in FOCUS, but I can read SOME_TABLE with a sql pass through. I am lazy and do not want to create a master for SOME_TABLE and IF I could get away with the second example that would be soooo easy..... Anyone have any idea how I could get my data out of SOME_TABLE using straight up sql and the data from A_FILTER as a filter without writing a master for SOME_TABLE or writing the data from A_FILTER to a file or bringing back unnecessary records to FOCUS from SOME_TABLE? Is it possible/easy to create a master on the fly???? I know I am kind'of out in the weeds on this but what the heck I'll ask anyway........

I know how to solve this problem in couple different (more difficult) ways, but I thought it would be fun to see what ingenious solutions Focal Pointers' would come up with.


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Report This Post
Expert
posted Hide Post
Here's one way to do this: create a hold file that contains the values you need, with commas. Include this file in the SQl request, addind an extra non-existent value to compensate for the last comma.

TABLE FILE BASEL_TIME_D
PRINT 
TIME_DIM_KEY
COMPUTE SEP/A1 = ',';
WHERE READLIMIT EQ 10
WHERE TIME_DIM_KEY GT 20000
ON TABLE HOLD AS A_FILTER FORMAT ALPHA
END
-RUN

SQL DB2
SELECT *
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY IN (
-INCLUDE A_FILTER
999999999
)
;
END

Generated SQL:
SQL DB2
SELECT *
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY IN (
20001,
20002,
20003,
20004,
20005,
20006,
20007,
20008,
20009,
20010,
999999999
)
;


OOPS! I was missing some code!

This message has been edited. Last edited by: Francis Mariani,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
  
APP APPENDPATH IBISAMP
-RUN
TABLE FILE CAR
PRINT CAR
WHERE SEATS EQ 2
ON TABLE SAVE AS A_FILTER
END
-RUN
-SET &XLINES    = &LINES;
-SET &QT        = '''';
-SET &CNTR      = 0;
-SET &GOT_VALS  = '';
-REPEAT GET_VALS &XLINES TIMES
-SET &CNTR      = &CNTR + 1;
-READ A_FILTER NOCLOSE &CAR_VAL.A16.
-SET &GOT_VALS  = IF &CNTR EQ &XLINES THEN &GOT_VALS || &QT || &CAR_VAL || &QT ELSE
-                                          &GOT_VALS || &QT || &CAR_VAL || &QT || ',';
-GET_VALS
-TYPE &GOT_VALS
-**********************
SQL DB2
SELECT * FROM SOME_TABLE
WHERE VEH_MAKE IN (&GOT_VALS)
;
TABLE
ON TABLE HOLD AS SQLOUT
END


EDIT: My &CNTR disappeared...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY

TABLE FILE CAR
PRINT 
COMPUTE FVALUE/A20 = '''' || BODYTYPE || ''',';
WHERE BODYTYPE NE 'SEDAN'
ON TABLE HOLD AS A_FILTER
END
-RUN

SQL
SELECT *
FROM CAR
WHERE BODYTYPE IN (
-INCLUDE A_FILTER
'999999999'
)
;
END

SQL:
SQL
SELECT *
FROM CAR
WHERE BODYTYPE IN (
-INCLUDE A_FILTER
'CONVERTIBLE',
'COUPE',
'HARDTOP',
'ROADSTER',
'COUPE',
'999999999'
)
;


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Are the two tables on separate servers? Why not use a sub-select in a single SQL statement.

SQL DB2
SELECT * FROM SOME_TABLE
WHERE VEH_MAKE IN
 (SELECT CAR FROM SOME_TABLE WHERE VEH_MAKE IN ('ROADSTER', 'COUPE', 'CONVERTIBLE'))
;
TABLE
ON TABLE HOLD AS SQLOUT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
quote:
Are the two tables on separate servers?
If not then use a cross query using the OPENQUERY syntax.

I successfully use this to insert data into MS SQL from Oracle - both on different servers.

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
Silver Member
posted Hide Post
First off I would like to thank each one of you for taking the time to reply.

Tom,
Your example while not the most 'facile' is great because it is a wonderful example of how to roll through data, lumping it into on big string variable using dialogue manager (Something that is frankly one of my weaker points). Because I am weak at DM, I usually write data to a text file, whip it (ie. loop though) with something like VB or Jscript and then toss it back to FOCUS as an 'amper variable'. That makes for less than elegant solution and what I WAS going to do had you guys not showed me a better way.


Francis,
That is elegant, simple and exactly the type of solution I had in mind. I had no clue you could use an 'include' in this manner. You are a true asset to the FOCUS community IMO.


Dan,
Yes they are on different servers........ in fact in my 'real world problem' the file I am using as my CAR example is flat and strictly known only to FOCUS. The SOME_TABLE table is on a DB2 sever and FOCUS has reading privileges but knows nothing about the data. (ie. how it is laid out, number of fields, etc). In other words I cannot 'TABLE FILE' SOME_TABLE but I can 'SELECT' on it using DB2 syntax only.

Here is an example of a real world shot your suggestion.....

DYNAM ALLOC FILE PEG25 DA JCUSRNO.FCCC.PEGS SHR REUSE
SQL DB2
Select item_no_LP
from proddb2.SINVLINE_dpims
where ITEM_NO_LP NOT IN
(
SELECT PARTNO FROM PEG25 GROUP BY PARTNO
)
AND LOC_LP = '034'
ORDER BY ITEM_NO_LP;
END


and the results = DSNT408I SQLCODE = -204, ERROR: IWAYPINT.PEG25 IS AN UNDEFINED NAME


Tony,
OPENQUERY eh???? I am searching around on this one. So far the only real documentation I have found is on Microsoft's site. Other than that I am getting vague reference to this searching on IB.com.

Again Thanks!!!!! If our paths ever cross the first beverage of your choice is on me. BTW anyone know of a good book on DM (I prefer reading large amouts of text from paper in my hands)?



Jay


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Report This Post
Expert
posted Hide Post
Jay,

The IBI ECL has a bunch of stuff in PDF format here.

Some may be of interest...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Jay,

The openquery on MSDN is what you need to look at. The basic syntax is OPENQUERY ( linked_server ,'query' ). I use an IP address for the "linked server" and the query is exactly that, the query that you would normally execute on the linked server. If I remember correctly you might not be able to use parameters in the SQL but that is where WebFOCUS DM scores as the DM should be evaluated before the query is sent.

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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Things that make me go hmmmm......

Copyright © 1996-2020 Information Builders