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.

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,

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

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'
)
;

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

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

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

Jay,

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

Some may be of interest...

Tom

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