Unltimately, my goal here is to join two databases together by the ENCNTR.ALIAS and ICU_LIST.VST_EXT_ID fields. Here are the master file definitions of the two fields:

FIELDNAME=VST_EXT_ID, ALIAS=vst_ext_id, USAGE=A20, ACTUAL=A20, $
FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200V, ACTUAL=A200V, $
(Although the fields are longer than 7 chars, for my purposes it is a 7 digit number stored as an alphanumeric field.)

I am first creating a hold file on the first database with the following:
DEFINE FILE ICU_LIST
FIN_NUM/A9 = ''''||SUBSTR(20,VST_EXT_ID,1,7,7,'A7')||'''';
END
TABLE FILE ICU_LIST
PRINT
FIN_NUM
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FIN_LIST FORMAT ALPHA
END

The records on this file look as I would expect when I table file it, ie, '1234567' '2345678' etc.

I'm then querying the second database with this file:
TABLE FILE ENCNTR_ALIAS
PRINT ENCNTR_ID
WHERE ENCNTR_ALIAS.ALIAS IN FILE FIN_LIST;
ON TABLE
END

I would expect this to parse to something like "where alias in ('1234567','2345678')" - which will work when I run this manually- but instead the trace shows the following and nothing is returned:
13.20.19 AE SELECT T1."ENCNTR_ID",T1."ALIAS" FROM V500.ENCNTR_ALIAS T1
13.20.19 AE WHERE (T1."ALIAS" IN('1796197
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1809173
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1849962
13.20.19 AE
13.20.19 AE
13.20.19 AE '

Where are all these extra spaces coming from?! Is there a better way of doing this?
Thanks in advance for any advice, my head hurts from banging it up against this wall all day...

This message has been edited. Last edited by: jjoyce,

Since that field is a Define..it must be adding extra spaces. You might need to use the TRIM function and get it down to what you need...just a thought

Tried a trim in my define and the result was the same.
DEFINE FILE ICU_LIST
FIN_NUM/A9 = ''''||TRIM('B',VST_EXT_ID,20,' ',1,'A7')||'''';

Execute this command after holding the file to see the format of the fields in the hold file:

? FF FIN_LIST

You only require those single quotes around your filter values if they contain blanks.

You could trying using a conditional JOIN: Using a Conditional Join

I replaced my define with
FIN_NUM/A7 = TRIM('B',VST_EXT_ID,20,' ',1,'A7');

but the trace remains the same and nothing is returned.

Did you run this

quote:

? FF FIN_LIST

what did it tell you?

Example program:

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN
TABLE FILE BASEL_TIME_D
SUM
COMPUTE PERIOD_TYPE2/A5 = SUBSTR(10, PERIOD_TYPE, 1, 5, 5, 'A5');
BY PERIOD_TYPE NOPRINT
WHERE PERIOD_TYPE IN ('MONTH', 'DAY');
ON TABLE HOLD AS H001
END
?FF H001
-RUN
TABLE FILE BASEL_TIME_D
SUM
DAY_OF_WEEK
BY PERIOD_TYPE
BY DAY_OF_WEEK NOPRINT
WHERE PERIOD_TYPE IN FILE H001;
WHERE READLIMIT EQ 1000;
END

Result of ?FF H001:

FILENAME=  H001
PERIOD_TYPE2  E01           A5

Generated SQL:

AGGREGATION DONE ...
SELECT T1."PERIOD_TYPE",T1."DAY_OF_WEEK",
MAX(T1."DAY_OF_WEEK") FROM BASEL.TIME_D T1 WHERE
(T1."PERIOD_TYPE" IN('DAY', 'MONTH')) GROUP BY T1."PERIOD_TYPE",
T1."DAY_OF_WEEK" ORDER BY T1."PERIOD_TYPE",T1."DAY_OF_WEEK"
FETCH FIRST 1000 ROWS ONLY FOR FETCH ONLY;

At first it wouldn't work as I added a space "? FF" but now I got it...

This was the result using the trim function in my define.
FILENAME= FIN_LIST
FIN_NUM E01 A7
15.31.19 AE SELECT T1."ENCNTR_ID",T1."ALIAS" FROM V500.ENCNTR_ALIAS T1
15.31.19 AE WHERE (T1."ALIAS" IN('1796197
15.31.19 AE
15.31.19 AE
15.31.19 AE ', '1809173
15.31.19 AE
15.31.19 AE
15.31.19 AE ',

Also, I did attempt the conditional join and got only syntax errors.. I have never used one before so I don't fully understand how its supposed to work.

My concern with that it appears to me that the join will return all rows from the database and then apply a filter. This table is very large and I want only a very small subset so returning all the rows in the table would be a very inefficient way of getting at the data vs passing the values that I want in a where clause...

What kind of database is this? Temporarily change the Master to

FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200, ACTUAL=A200, $

and test again. Also, in my DB2 environment, I added this to EDASPROF to eliminate the annoying A200V:

SQL DB2 SET VARCHAR OFF

(Sorry about the blank between ? and FF - my mistake)

One database is sql server... I am querying it to create the ICU_LIST hold file containing VST_EXT_ID in the earlier code- I left that part out for simplicity- and the hold file is a FOCUS format.

The other database containing encntr_alias.alias is an oracle 10g.

YES!!!!!!!!!!!
modifying the masterfile to remove the V did it. Thank you so much! These are our two major databases and I've had a growing need for integrated reports between them and this field is the only common link. Solving this helps me oodles and oodles!

If you regularly regenerate the masters (we do once a month), you may wish to add the varchar command to EDASPROF and then regenerate the masters:

ENGINE [SQLMSS] SET VARCHAR {ON|OFF}

quote:

where:
SQLMSS - Indicates the Adapter for Microsoft SQL Server. You can omit this value if you
previously issued the SET SQLENGINE command.
ON - Maps the Microsoft SQL Server data type VARCHAR as variable-length alphanumeric
(AnV). This is required for Unicode environments. ON is the default value.
OFF - Maps the Microsoft SQL Server data type VARCHAR as alphanumeric (A).

(See "Managing Microsoft SQL Server Metadata" in the "Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS" doc).

I am afraid of the "This is required for Unicode environments" statement - we're moving to a Unicode environment and I hope this statement is not true!