I thought this would be easy. I wanted to use an existing SQL table to select order numbers for a calculated date range. Then use that list to select records from one of our Universe databases to extract information from it. I used the following code (sorry if I paste anything wrong - still fairly new on this):

 
-SET &TODAY = &YYMD;
-RUN
SET HOLDLIST = PRINTONLY
DEFINE FILE SALES_CURR_PREV_FY_US
CURR_DATE/YYMD=&TODAY.EVAL;
START_MONTH/YYMD=DATEADD(CURR_DATE, 'M', -3);
START_DATE/YYMD=DATEMOV(START_MONTH, 'BOM');
END
TABLE FILE SALES_CURR_PREV_FY_US
SUM
     'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.ORDER_NUM/A10V'
BY 'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.ORDER_NUM' NOPRINT
WHERE ( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE_MDYY GE START_DATE ) AND ( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE_MDYY LE CURR_DATE );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS LEDHOLD FORMAT ALPHA
END
JOIN
 LEFT_OUTER LEDHOLD.LEDHOLD.ORDER_NUM IN LEDHOLD TO UNIQUE
 LEDGER_LOG_US.LEDGER_LOG_US.@ID IN LEDGER_LOG_US TAG J0 AS J0
 END
TABLE FILE LEDHOLD
PRINT 
     'LEDHOLD.LEDHOLD.ORDER_NUM'
     'J0.LEDGER_LOG_US.@ID'
     'J0.LEDGER_LOG_US.ORDER_CMTS'
HEADING
""
FOOTING
""
WHERE RECORDLIMIT EQ 10
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
.
.
. 

This resulted in the following output:

ORDER_NUM @ID ORDER_CMTS
S4488178 . .
S4515175 . .
S4856343 . .
S4895379 . .
S5365942 . .
S5791190 . .
S5832861 . .
S5840952 . .
S5976381 . .
S5989810 . .

If I reverse the JOIN of the files (and make it an INNER join) I get the following error:

0 NUMBER OF RECORDS IN TABLE= 314295 LINES= 182493
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED

My first question is can anyone explain why I get an error one way but not the other?
I think it has more to do with the Universe which uses PICK databases (which are non-relational). Can anyone provide me with some wisdom on when or how to join databases? I have had training on JOIN's but it all works great when it is in SQL format. It seems to me that it should be straight forward to join a value from a hold file to the key of another database. What am I missing?

This message has been edited. Last edited by: Glenn C.,

What is the format of the LEDGER_LOG_US.LEDGER_LOG_US.@ID in the LEDGER_LOG_US table ?

Also Swapping the join around requires either the hold file to be sorted in the right order or changes to a FOCUS with the ORDER_NUM field indexed.

The format of LEDGER_LOG_US.LEDGER_LOG_US.@ID is A10V which is the same ORDER_NUM in the hold file.
I'm wondering does LEDGER_LOG_US need to be indexed for the join to work?
I just find it very strange that I don't get an error at all. I checked the source to make sure.

Typically you don't have to have the join field in SQL indexed, but with Universe, I am nut sure.

I also would have thought that at lease one record would match as well.

The other option is to extract the table into a hold file and index the field there, and join to it.

Are you able to verify that the contents are exactly the same. e.g. one has a leading space, etc

Add

?FF LEDHOLD
-EXIT

after the JOIN, and post the output.

Hold alpha is not the best way to join

Here is the output from the FF LEDHOLD:

FILENAME= LEDHOLD
ORDER_NUM E01 A10V
@ID @ID A10V
@ID1 @ID A10V
PRINT_LOG PRINT_LOG A10V
ORDER_CMTS ORDER_CMTS A100V
BID_FOLLOWUP_DATE BID_FOLLOWUP_DATE YYMD
BID_FOLLOWUP_USER BID_FOLLOWUP_USER A10V
BID_FOLLOWUP_CMTS BID_FOLLOWUP_CMTS A40V
BID_FOLLOWUP_ACK BID_FOLLOWUP_ACK I11
MANIFEST_ID MANIFEST_ID A16V
COMPLETE_DATE COMPLETE_DATE YYMD
PICKERS PICKERS A15V
STAGING STAGING A15V
SHIPVIAS SHIPVIAS A8V
TRACKNOS TRACKNOS A15V
WEIGHTS WEIGHTS A7V
FREIGHTS FREIGHTS I11
ACTION_DATE ACTION_DATE YYMD
HANDLING HANDLING I8
CHARGES CHARGES I11
ACTION_CODE ACTION_CODE A10V
TRIG_NOTIFY TRIG_NOTIFY A10V
NEXT_CALL_DATE NEXT_CALL_DATE YYMD
LOCKED_UPDATE LOCKED_UPDATE A13V
PREV_PRINT_STAT PREV_PRINT_STAT A2V
CURRENT_PRINT_STAT CURRENT_PRINT_STAT A2V

Frank,
I did try holding it to a FOCUS database and received the same results however. I did not index however. Any better suugestions?

I would try to put the other database also in a focus temperary table with an index and sorted in a logic way

I found out in the end that I could not extract directly from this file due to row size issues.
Thanks to everyone who replied.